6Microsoft Office Excel 2010Conditional formatting for grades v targets

So, in my oth­er life I’m actu­ally a sci­ence teach­er, and as it usu­ally gets round that I’m “good with com­puters” I usu­ally get asked to have a look at a few things.  Twice in the last few months I’ve been asked if I’d set up con­di­tion­al format­ting in excel to col­our code stu­dent test res­ults com­pared to their tar­get grades.  Whilst not exactly related to the core con­tent of this site, it is still DIY tech­nic­al help so I decided to pub­lish a full set of instruc­tions here.  Enjoy…

Microsoft Excel Conditional Formatting: Lookup TablesFirst you need a lookup sheet

  • Cre­ate a new sheet in the work­book.
  • Fill in 2 columns – the left with grades (1c, 1b, 1a, 2c etc) and the right with a “score” value (can just be 1, 2, 3, 4, 5 etc)
  • Fill in 2 addi­tion­al columns with a “reverse look­up” which is the same as before but with the 2 columns reversed
  • High­light the full left hand table that you’ve cre­ated, right click on it, and select “define name”.  Give it a name, e.g. “look­up”
  • High­light the second table you cre­ated, right click and define anoth­er name.  Give it a name, e.g. “reverse_lookup”

Microsoft Excel Conditional Formatting: Define NameCreating the worksheets

  • Now cre­ate a work­sheet for each class, group or year (as suits).  Include a column for tar­get grades and vari­ous columns for test or assess­ment res­ults (e.g. as below)
  • Note in this example that tar­get data is in C3 – C5.  Res­ults are in D3 – F5

Microsoft Excel Conditional Formatting: Example Markbook

Building the basic conditional formatting

  • Select cell D3 – the top left of the res­ults data cells.  Make sure you are on the “home” tab and click on “con­di­tion­al format­ting” and select “new rule”
  • Select “use a for­mula to determ­ine which cells to format”
  • What we want to do is com­pare the cur­rent cell to the tar­get, but it isn’t quite as simple as that, because of the way excel com­pares val­ues – so we need to use the look­up tables.
  • We also need 5 rules – 2 sub­levels or more below, 1 below, on tar­get, 1 above and 2 or more above.  You can cre­ate more rules if you want more detail or less if you just want below, on and above tar­get.
  • The even­tu­al for­mula for 2 sub­levels or more below will be =VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE) 

    Microsoft Excel Conditional Formatting: New Rule Formula

  • The C3 in the first part refers to the top tar­get cell, and the D3 refers to the top actu­al res­ult cell.  The “Look­up” refers to the look­up table range that we named earli­er.
  • To break this down – we’re look­ing up numer­ic­al val­ues for both the tar­get and the actu­al score in the cur­rent cell.  Because we want 2 sub­levels or more below we take 1 off the tar­get and say it must still be great­er than the score.  The $ before the C of C3 ensures that when we use the same for­mula for oth­er columns they will still be com­pared to the tar­gets column.
  • Now click on format and apply the format­ting you want.  I use a back­ground col­our of light red
  • Now we have cre­ated 1 of 5 rules for a single cell.

Microsoft Excel Conditional Formatting: Format CellsExpanding the conditional formatting for multiple rules

  • Next repeat the pro­cess to cre­ate 4 more rules.
  • When all are fin­ished my 5 examples would look as below.  Only the com­par­is­on and pos­sible addi­tion or sub­trac­tion change between them.  Also note the order (which we come to next)

  • I use medi­um red, light red, yel­low, medi­um green and light green (in that order) for back­ground col­ours

Sorting the order of the rules

  • Finally, we need to make sure the rules are in the right order, and apply them to ALL the res­ults cells, not just the single res­ult cell D3.
  • Click on the con­di­tion­al format­ting but­ton again and choose “man­age rules…”
  • Make sure the rules are in the right order – as above (medi­um red, light red, yel­low, medi­um green, light green)Microsoft Excel Conditional Formatting: Rules Manager
  • Click in the “Applies to” box and enter the full range of cells which will con­tain res­ults.  In my example this is =$D$3:$F$5.  Put this in all 5 “applies to” boxes
  • This sheet should now fully work with con­di­tion­al format­ting

Expanding easily to additional sheets

  • To apply to oth­er sheets simple copy and paste a single cell which already has con­di­tion­al format­ting applied to it, into the new sheet.
  • Then click on the con­di­tion­al format­ting but­ton, select man­age rules, and change the 5 “applies to” boxes to point to the cells of the new sheet which need con­di­tion­al format­ting

What do you think? Drop us a comment below! If you would like to subscribe please use the subscribe link on the menu at the top right. You can also share this with your friends by using the social links below. Cheers.

Leave a Reply

6 Comments

gravatarNikki

I would love to re-post this art­icle on my own web­site, would that be okay?

Reply
gravatarGary Zawadzki

Hi,
Great tutori­al — thanks. I’ve got 3 dif­fer­ent col­ours work­ing per­fectly but the 2 above and 2 below aren’t format­ting to their cor­rect col­ours. They are chan­ging to red and green but not the shades that they need to change to. Any sug­ges­tions please?

Thanks,

Gary

Reply
gravatarJon Scaife

Sorry for the long delay in reply­ing Gary — I’ve been busy migrat­ing the site to our new VPS. Did you man­age to get this sor­ted? If you did­n’t then by all means zap me over your excel file to have a look at. I’ll recheck this guide in the next day or 2 to make sure there aren’t any typo­’s or mis­lead­ing parts, it’s been known to hap­pen 😉

Reply
gravatarEmma

I have fol­lowed all your instruc­tions above but none of my cells have gone col­oured?? Any sug­ges­tions where I’m going wrong??

Reply
gravatarJon Scaife

I assume you have pop­u­lated them with data, and the tar­get column is also pop­u­lated with data?
By all means zap me the file over and I’ll take a quick look — I’ve e‑mailed you

Reply