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.

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

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »