Understanding How Conditional Formatting Rules Are Applied

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:

Click image for larger version. 

Name:	cf1.png 
Views:	12269 
Size:	36.3 KB 
ID:	1053
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:

Click image for larger version. 

Name:	cf2.png 
Views:	12055 
Size:	44.9 KB 
ID:	1054
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:

Click image for larger version. 

Name:	cf3.png 
Views:	12042 
Size:	45.5 KB 
ID:	1055
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:

Click image for larger version. 

Name:	cf4.png 
Views:	12046 
Size:	36.4 KB 
ID:	1060
Figure 4.

That returns the effects shown in Figure 5:

Click image for larger version. 

Name:	cf5.png 
Views:	12036 
Size:	44.4 KB 
ID:	1056
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:

Formatting Elements
# Rule Background Fill Font Color Font Style
1 =$G4=”Monday”
Blue
{not set}
{not set}
2 Value > 1500
Yellow
Red
Italics
3 Value > 5000
Green
White
{not set}

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
      • Name:  cf-a1.png
Views: 11989
Size:  923 Bytes
  • 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:
      • Name:  cf-a2.png
Views: 11951
Size:  1.1 KB
  • 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:
      • Name:  cf-a3.png
Views: 11949
Size:  1.1 KB
  • 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:
      • Name:  cf-a3.png
Views: 11949
Size:  1.1 KB

Example 2 of Rule Precedence

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

Formatting Elements
# Rule Background Fill Font Color Font Style
1 =$G4=”Monday”
Blue
{not set}
{not set}
2 Value > 5000
Green
White
{not set}
3 Value > 1500
Yellow
Red
Italics

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

Cell D6
Formatting Elements
Cumulative
# Rule Apply? Background Fill Font Color Font Style
Result
Base 2,344.84
None
Automatic
(black)
Normal
Name:  cf-b1.png
Views: 12001
Size:  876 Bytes
1 =$G4=”Monday”
TRUE
Blue
N/A
N/A
Name:  cf-b2.png
Views: 11953
Size:  1,013 Bytes
2 Value > 5000
FALSE
N/A
N/A
N/A
Name:  cf-b2.png
Views: 11953
Size:  1,013 Bytes
3 Value > 1500
TRUE
Rule 1 has
precedence
Red
Italics
Name:  cf-b3.png
Views: 11914
Size:  1.0 KB

Now, what about D7?

Cell D7
Formatting Elements
Cumulative
# Rule Apply? Background Fill Font Color Font Style
Result
Base 9,720.15
None
Automatic
(black)
Normal
Name:  cf-a1.png
Views: 11989
Size:  923 Bytes
1 =$G4=”Monday”
TRUE
Blue
N/A
N/A
Name:  cf-a2.png
Views: 11951
Size:  1.1 KB
2 Value > 5000
TRUE
Rule 1 has
precedence
White
N/A
Name:  cf-c3.png
Views: 11913
Size:  1,023 Bytes
3 Value > 1500
TRUE
Rule 1 has
precedence
Rule 2 has
precedence
Italics
Name:  cf-c4.png
Views: 11904
Size:  1.1 KB

And the end result is shown in Figure 6:

Click image for larger version. 

Name:	cf6.png 
Views:	11982 
Size:	45.2 KB 
ID:	1057
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:

Formatting Elements
# Rule Background Fill Font Color Font Style
1 Value > 5000
Green
White
{not set}
2 Value > 1500
Yellow
Red
Italics
3 =$G4=”Monday”
Blue
{not set}
{not set}

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

Cell D5
Formatting Elements
Cumulative
# Rule Apply? Background Fill Font Color Font Style
Result
Base 2,344.84
None
Automatic
(black)
Normal
Name:  cf-b1.png
Views: 12001
Size:  876 Bytes
1 Value > 5000 FALSE
N/A
N/A
N/A
Name:  cf-b1.png
Views: 12001
Size:  876 Bytes
2 Value > 1500 FALSE
N/A
N/A
N/A
Name:  cf-b1.png
Views: 12001
Size:  876 Bytes
1 =$G4=”Monday” TRUE
Blue
N/A
N/A
Name:  cf-b2.png
Views: 11953
Size:  1,013 Bytes

Now D6:

Cell D6
Formatting Elements
Cumulative
# Rule Apply? Background Fill Font Color Font Style
Result
Base 2,344.84
None
Automatic
(black)
Normal
Name:  cf-b1.png
Views: 12001
Size:  876 Bytes
1 Value > 5000
FALSE
N/A
N/A
N/A
Name:  cf-b1.png
Views: 12001
Size:  876 Bytes
2 Value > 1500
TRUE
Yellow
Red
Italics
Name:  cf-d1.png
Views: 11914
Size:  775 Bytes
1 =$G4=”Monday”
TRUE
Rule 2 has
precedence
Rule 2 has
precedence
Rule 2 has
precedence
Name:  cf-d1.png
Views: 11914
Size:  775 Bytes

And finally D7:

Cell D7
Formatting Elements
Cumulative
# Rule Apply? Background Fill Font Color Font Style
Result
Base 9,720.15
None
Automatic
(black)
Normal
Name:  cf-a1.png
Views: 11989
Size:  923 Bytes
1 Value > 5000
TRUE
Green
White
N/A
Name:  cf-e1.png
Views: 11979
Size:  1.1 KB
2 Value > 1500
TRUE
Rule 1 has
precedence
Rule 1 has
precedence
Italics
Name:  cf-e2.png
Views: 11906
Size:  1.2 KB
3 =$G4=”Monday”
TRUE
Rule 1 has
precedence
Rule 1 has
precedence
Rule 2 has
precedence
Name:  cf-e2.png
Views: 11906
Size:  1.2 KB

And the end result is shown in Figure 7:

Click image for larger version. 

Name:	cf7.png 
Views:	11974 
Size:	45.5 KB 
ID:	1058
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:

Click image for larger version. 

Name:	cf8.png 
Views:	12348 
Size:	37.6 KB 
ID:	1059
Figure 8.

This time D7 gets evaluated as follows:

Cell D7
Formatting Elements
Cumulative
# Rule Apply? Background Fill Font Color Font Style
Result
Base 9,720.15
None
Automatic
(black)
Normal
Name:  cf-a1.png
Views: 11989
Size:  923 Bytes
1 Value > 5000
TRUE
Green
White
N/A
Name:  cf-e1.png
Views: 11979
Size:  1.1 KB
2 Not processed
N/A
N/A
N/A
N/A
Name:  cf-e1.png
Views: 11979
Size:  1.1 KB
3 Not processed
N/A
N/A
N/A
N/A
Name:  cf-e1.png
Views: 11979
Size:  1.1 KB

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

Click image for larger version. 

Name:	cf2.png 
Views:	12055 
Size:	44.9 KB 
ID:	1054

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.

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts