When we’re working with conditional formatting with formulas, how does Excel manage conditional formatting rules that conflict with each other?
And how does the “Stop If True” checkbox affect our results?
I thought of these questions when I was working on How to Set Up a Square-Format U.S. Map in Excel, and on How to Set Up Multiple Conditional Formats with Formulas—both of which rely on conditional formatting.
Because the answers to these questions aren’t obvious, let’s go through several tests to see what we can learn. (You can download the workbook with my conditional formatting tests here.)
Here’s the general setup: Depending on the value in cell A2, we want cell B2 to display some combination of the formats shown in column D. As you can see from these conditions, if cell A2 has a value of 31, all three conditions will be TRUE. So how will Excel format cell B2 in this case?
To examine these issues, I’ll assume that you know how to set up conditional formatting with formulas. If you need help, you can find it here: Conditional Formatting with Formulas.
Conflicting Conditional Formats, Test 1
To begin, let’s set up our conditional formatting rules like this:
And then, let’s apply four tests to our setup, like this:
Here, for example D, the rules were applied in B-to-D order in the Conditional Formatting Rules Manager. That is, Excel processes the >10 rule, then the >20 rule, and then the >30 rule.
These results are not what we’re looking for. Excel’s note above the rules tells us that they’re applied in the order shown in the dialog. So if the >30 rule is last, why isn’t cell B2 blue for example D?
Conflicting Conditional Formats, Test 2
Now let’s reverse the order of our rules. To do so, we select one of the rules and then use the arrows I’ve circled in red to shift the rule’s position.
And now, let’s apply our tests again:
The B and C results are the same as for Test 1. But example D gives us a blue fill and a red border when cell A2>30. Why is that?
When we look at the rules manager dialog, we see that Excel ran the >30 rule first; it applied the >10 rule last, which added the red border; but it ignored the >20 setting between them. Why was that?
Before we draw any conclusions, let’s try one more test.
Conflicting Conditional Formats, Test 3
Here, we’ll leave the same order as we had in Test 2, but we’ll add a checkmark to the Stop If True checkbox:
And we get these results:
In this final test for example D, we no longer have a red border. But we do have a red border for the other two tests.
Why is that?
Conflicting Conditional Formats, Conclusion
These three sets of tests give us two useful guidelines about the process that Excel follows when it applies conditional formats that conflict:
Guideline 1: The first rule to set a specific format wins that format.
In all three sets of tests, when a rule’s formula evaluated as TRUE, the first rule to apply any specific format won that format. So, in test 2, rule D was the first rule to apply a fill format. And therefore, rule D won the fill-format contest. But because both the C and D rules ignored the border format, rule B won that contest.
Therefore, if you want some rule X to override rule Y under certain conditions, you’d always position rule X above rule Y in the Conditional Formatting Rules Manager.
Guideline 2. If an earlier rule ignores a format setting, later rules can apply the setting—if they’re not blocked by Stop If True.
To illustrate, in all three sets of tests, the >20 and >30 rules ignored the border setting. And therefore, when the >10 rule applied that setting, the final result showed that setting—with one exception:
In Test 3, we checked the Stop If True checkbox for the >30 rule. And therefore, the >10 rule never had the chance to apply its red border, which left example D without that border.
Now would be a good time to open a new workbook and test some conditional formatting rules yourself.
You also can download my workbook here.
