Conditional formatting – Hierarchy

In 2003, the hierarchy is very important in Conditional Formatting: if you have that wrong some conditions may never be tested. Supposing you want the cell to go red if less than 50% in an exam, amber between 51 and 75%, and green for above 75%.
Conditional formatting is a very simple test, which stops testing after a true condition.
In the exam example, if you tested for a score over 50 before over 75, then the second test would never be performed, because the first was true.

From 2007, the default is to test all conditions.

If you don’t want the tests to continue after a condition has been satisfied (ie it’s TRUE), then you should tick the “Stop if True” box in Home, Conditional Formatting, Manage Rules. This has to be applied individually to each Rule.

Even so, you need to check that rules don’t conflict, or you might not get the results you are expecting.

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. 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