Conditional Formatting: highlight items automatically

Conditional Formatting changes the font, border or colour of a cell depending on the criteria you have chosen. For example you might set a cell to go red if it went over 32 or green if a certain date is only three weeks away. This is very useful for highlighting records, eg, a temperature threshold exceeded or forthcoming subscription renewal.

Conditional formatting is a very useful tool in 2003, which has become even more powerful in 2007, because it’s not restricted to three criteria per cell.
However, the extra flexibility of 2007 also means it is easier to set up conflicting rules.
Worse than that, Excel also sets up duplicate rules in the background because they refer to a different range. This is a particular problem when one is developing rules, a process which is best done step by step (conditional formatting doesn’t always behave in the way one expects). Unfortunately having developed it for one cell, copying it forward over a range of cells, retains the original test, so there are now two rules.

I think two main rules apply with conditional formatting:
1/ Don’t have too many – it just gets confusing
2/ Define your own rules and don’t use Excel presets, because then you know what is going on

Leave a Reply

Your email address will not be published. Required fields are marked *