Using Stop If True when Conditional Formatting

September 12th, 2013 by datapig Leave a reply »

When applying Conditional Formatting, you may have situations where you don’t want to apply your rule to certain cells in a range.

.

For example, in this range, I applied the Above Average Conditional formatting rule. You’ll notice that the check icons apply to values above the average for the range, while all below average values are formatted with the X icon.

.

This is nice but in the real world you often only need to bring attention to the below average values. In fact, showing too many icons at one time may only serve to inundate you clients with graphics, obstructing the message you’re trying to highlight.

.

Excel provides a clever Conditional Formatting mechanism called “Stop If True”. This allows you to specify when Excel should stop evaluating/formatting values if a condition is true.

.

In this example, I want to remove the Check icons. The cells that contain those icons all have values above the average for the range. Therefore I’ll first need to add a condition for all cells whose values are above average.

.

I first open the Conditional Formatting Rules Manager and press the New Rule button to start a new rule.

.

I then configure the rule so that the format applies to cells values greater than the average. Note that I leave the format untouched, leaving it to “No Format Set”.

 

Back in the Conditional Formatting Rules Manager, I place a check in the Stop If True check box next to my new rule.

.

Once I confirm my changes, my range shows only check boxes. This is because any value that is greater than the average gets set to No Format, then stops evaluating.

.

Now my audience can focus on the exceptions rather than determining which icons are good and bad.

Advertisement

3 comments

  1. Dave says:

    I never realised you could choose ‘no format set’ and I think I can see some areas I might use it. On a side note does the order of the rules for ‘Stop If True’ matter? I have a sheet with two conditional SIT formats that start at the top of my ordering and have many others below and because I use a macro to bring data in and have to reapply formulas and formats the SIT rules move down the order of formats, as my macro skills only seem to add formats and not clear the,properly first. However, it doesn’t seem to affect the presentation of the formats or the application of subsequent conditions, so I wonder as to the need to order conditional formats. Does it make a difference? Virtually all of the formats I am using apply to the whole sheet/table of data (its not an ‘Excel defined table’, if that makes a difference) so I can’t see the need to use the positioning buttons.

  2. datapig says:

    Dave: The order of the formatting rules absolutely matter. You generally want to come up with a basic if then else scenario in your mind and establish an order of operations for your situation.

Leave a Reply