• 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:

    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:

    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:

    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:

    Figure 4.

    That returns the effects shown in Figure 5:

    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

    • 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:
    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
    1 =$G4=”Monday”
    TRUE
    Blue
    N/A
    N/A
    2 Value > 5000
    FALSE
    N/A
    N/A
    N/A
    3 Value > 1500
    TRUE
    Rule 1 has
    precedence
    Red
    Italics

    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
    1 =$G4=”Monday”
    TRUE
    Blue
    N/A
    N/A
    2 Value > 5000
    TRUE
    Rule 1 has
    precedence
    White
    N/A
    3 Value > 1500
    TRUE
    Rule 1 has
    precedence
    Rule 2 has
    precedence
    Italics
    And the end result is shown in Figure 6:

    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
    1 Value > 5000 FALSE
    N/A
    N/A
    N/A
    2 Value > 1500 FALSE
    N/A
    N/A
    N/A
    1 =$G4=”Monday” TRUE
    Blue
    N/A
    N/A

    Now D6:
    Cell D6
    Formatting Elements
    Cumulative
    # Rule Apply? Background Fill Font Color Font Style
    Result
    Base 2,344.84
    None
    Automatic
    (black)
    Normal
    1 Value > 5000
    FALSE
    N/A
    N/A
    N/A
    2 Value > 1500
    TRUE
    Yellow
    Red
    Italics
    1 =$G4=”Monday”
    TRUE
    Rule 2 has
    precedence
    Rule 2 has
    precedence
    Rule 2 has
    precedence

    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
    1 Value > 5000
    TRUE
    Green
    White
    N/A
    2 Value > 1500
    TRUE
    Rule 1 has
    precedence
    Rule 1 has
    precedence
    Italics
    3 =$G4=”Monday”
    TRUE
    Rule 1 has
    precedence
    Rule 1 has
    precedence
    Rule 2 has
    precedence

    And the end result is shown in Figure 7:

    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:

    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
    1 Value > 5000
    TRUE
    Green
    White
    N/A
    2 Not processed
    N/A
    N/A
    N/A
    N/A
    3 Not processed
    N/A
    N/A
    N/A
    N/A
    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.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 4 Comments
    1. R Cubed's Avatar
      R Cubed -
      I am trying to compare information contained on 2 seperate worksheets and if the information does not match I want the cell to turn red. I have read evertying I could find and tried several different "Fixes" but none of them work.

      Finer details
      Worksheet #1 containes a "Master Matrix"
      Worksheet #2 containes the most recent report query based on the same start date range to current date.

      A daily or weekly report is generated and exported to an excel file.
      The information from this report is copied and pasted into my file on worksheet #2 where worksheet #1 compares itself to worksheet #2 and anything that is different the cell is highlighted red.
      Both worksheets contain exactly the same amout of columns. The number of rows can and will change as new issues or concerns are generated from the report. Also the information contained within each cell of the report can change (ie, quantities, cost, BIN code, etc) so we want an easy way for these changes to be identified on worksheet #1 by changing the color which prompts us to look at that particular cell to see what was changed. No automatic updates required, just a flag to review the modified data.

      I hope that I have explained this ok.
    1. Pecoflyer's Avatar
      Pecoflyer -
      Nice work Ken.

      I would also like to add that all CF formulas are considered as array formulas. So, if you need an array formula to define your CF, don't bother about Ctrl+Shift+Enter
    1. canischintsomani's Avatar
      canischintsomani -
      i have a query on conditional formatting

      i am working on a data and i need to put conditional formatting for the same.. data is as under

      Detail 1 Detail 2 Comments
      500 650 Green
      400r 300 Red
      200 250 Green
      200 200 Yellow









      Now i want to put conditional formatting in this if detail 2 is grater than detail 1 color should be green, if smaller than it should be red. otherwise yellow.. yes we can do it with formula as u explained on the top.. but this i need to do for around 200 line items.. and i cant apply the formula on one cell and copy and paste .. awaiting for the response thanks in advnace
    1. Ken Puls's Avatar
      Ken Puls -
      Apologies, folks. I don't get notified of responses to these posts, so for anyone else looking for help on this article, please post in the forum at http://www.excelguru.ca/forums/forum.php. That way an expert will see it.

      Thanks!