Its the start of a new year and I’m getting my class timetable from our school system. I like working with colour coding so I can see what years I’m teaching. I used to do it manually but today I looked into how to do it using conditional formatting in Excel. It’s a bit odd but it seems to work. You have to add it based on a formula and the formula I used is this:

=ISNUMBER(SEARCH($A$1,"9"))

..to look for a cell that contains “9”. Note: $A$1 is the first cell of the block I have selected. And I make the background colour change to yellow.

I’ve added 4 other rows depending on what Year groups I’m teaching (eg 10, 11, 12 and 13)

It should also make it easier to change the colours if I think they’re a bit naff!

Another useful conditional formatting rule I discovered to highlight those students who missed marks in each question is shown below:

See how the max mark is shown at the top of the column (row 8) and the student marks are from G12 across and down. It just makes it easy to spot patterns where students haven’t got full marks.