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

About spreadsheetexpertblog

Working as a computer professional since 1983, on projects varying in size from one person to multi-million pounds. Working on Excel projects since the mid 1990s.
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s