Conditional formatting in Excel is a powerful tool that allows you to dynamically format cells depending on the values of that or other cells’ data. In Excel 2007 the conditional formatting engine was re-written, opening things up to allow more than 3 conditional formats on any cell, as well as conditional formats that could overlap ranges. All in all, these were fantastic improvements that can lead to some very versatile and useful worksheets.

Unfortunately, the user interface to control conditional formatting is not the most intuitive. The purpose of this article is to help you understand the way Excel applies rule precedence so that you can build powerful formatting rules of your own, without getting frustrated along the way.

What you expect vs what you get

Assume that you’ve got conditional formatting rules set up on your worksheet as per Figure 1:

Given that the dialog specifies that the rules will be “applied in order shown”, I’d expect that to give me the output per Figure 2:

Based on the wording of the dialog, I’d expect rule one to be applied, then rule 2 to overwrite it, then rule 3. But that is not at all what happens. In fact, the rules as shown above, actually yield the effects shown in Figure 3:

No green cells. Blue cells with red font (which I plainly have not defined.) Obviously the rules don’t apply in the order from top to bottom. So let’s flip the order so the blue rule is last and the green rule is first, as shown in Figure 4:

That returns the effects shown in Figure 5:

Okay, so closer to what we’d expect, but where did we define the green cells to have an italic font? We didn’t! So how did that happen?

Understanding Rule Precedence

So here’s the most important things to understand about conditional formatting rules:

1. Rules are applied from top to bottom
2. Each rule is evaluated in order, unless Stop if True is checked. (More on that later.)
3. A rule can only apply a format to an element that has not already been formatted.

It’s rule number 3 that is the kicker, and the most important part to understand.

Example 1 of Rule Precedence

In the formats shown in Figure 1, the rules are set up as follows:

Let’s evaluate this in the context of D7, which has a value of $9,720.15:

• Before any rules are evaluated:
  • Background Fill: There is no background fill on the cell
  • Font Color: Set to Automatic (Black)
  • Font Style: No font style has been set
  • Beginning format
    • 
• Rule 1: G7 is Monday, so the rule is true.
  • Background Fill: A blue background fill is applied to the cell.
  • Font Color: No font color was chosen in the conditional formatting rule, so no attempt is made to apply a change to the cell. Font color can therefore assume to be “N/A” (not applicable).
  • Font Style: No font style was chosen in the conditional formatting rule, so no attempt is made to apply a change to the cell. Font style can therefore assume to be “N/A” (not applicable).
  • Cumulative result:
    • 
• Rule 2: $9,720.15 is greater than 1500, so the rule is true.
  • Background Fill: Because a background fill has previously been set, the earlier rule takes precedence and this section of the rule is ignored.
  • Font Color: Unlike the background fill, no font color has previously been set since rule 1 did not specify a font color. So this section of the rule is applied and the font turns red.
  • Font Style: No font style was applied in rule 1, this section of the rule is applied.
  • Cumulative result:
    • 
• Rule 3: $9,720.15 is greater than 5000, so the rule is true.
  • Background Fill: Because a background fill has previously been set, the earlier rule takes precedence and this section of the rule is ignored.
  • Font Color: Because a font color has previously been set, the earlier rule takes precedence and this section of the rule is ignored.
  • Font Style: No font style was chosen in the conditional formatting rule, so no attempt is made to apply a change to the cell. (N/A)
  • Final Result:
    • 

Example 2 of Rule Precedence

Let’s change the order of rules 2 and 3:

Let’s look at the evaluation of D6 first, then D7

Now, what about D7?

And the end result is shown in Figure 6:

Example 3 of Rule Precedence

Now let’s change the order one more time, pushing rule 1 down into the rule 3 position:

Let’s look at the evaluation of D5 this time…

Now D6:

And finally D7:

And the end result is shown in Figure 7:

Stopping Rule Inheritance

So what if we don’t want our green cell to inherit the italics font from the yellow format? We know that, based on the way the rules are set up in Example 3 that all cells over $5000 will ALWAYS have an italic font set since they are also over $1500.

There are actually 2 ways to do this. The first is that we could set a general font for our $5000 rule. Since it would only be applied where the amount is greater than $5000, it wouldn’t block the $1500 rule from applying its font.

Sure as anything, though, you’re going to run into a scenario where you can’t do that for whatever reason. And this is where the “Stop if True” settings comes in. By flagging this selection on our rules, the conditional rule manager will stop executing any further formatting rules once a rule is found to be true.

So this time, let’s add the Stop if True flag to rule 1, as shown in Figure 8:

This time D7 gets evaluated as follows:

And the end results look like what we expected to get, way back in the beginning:

Conclusion

Conditional formatting is a very powerful tool, and very versatile. But to make the best use out of it, we need to carefully craft rules in order to appropriately layer on the formatting that we want.

I hope that you’ve found this article helpful in understanding exactly how conditional formatting rules are applied, and how they don’t actually overrule each other, but rather add to previously applied formats where they can.