Software

Understanding Excel's conditional formatting rules can help prevent unintended results

Avoid frustration and unintended formatting by learning how Excel applies conditional formatting rules.

scanrailistock-469667229.jpg

Image: iStockphoto.com/scanrail

Excel's conditional formatting is a powerful and versatile tool that lets you dynamically control formatting. For instance, you might implement a rule that displays a value in red if it reaches a threshold of 500 or greater. Or you might display an entire record in yellow when an order date is older than two weeks. But sometimes the rules you apply don't display the results you intended. This can happen when ambiguity exists between rules. In this article, I'll explain how Excel applies rules. With a better understanding of these rules, you'll avoid unintended results.

I'm using Excel 2016 on a Windows 10 64-bit system. For your convenience, you can download the demonstration .xlsx or .xls file. Conditional formatting is available in version 2000 and later, but the feature is more accommodating in the Ribbon versions. You can view conditional formats in the browser version, and they update appropriately when you change values. However, you can't build a rule in the browser; you'll need Excel's desktop version to build conditional formatting rules.

SEE: 10 cool ways to use Excel's conditional formatting feature

What's a conditional formatting rule?

A conditional formatting rule is a dynamic application of formatting. Each rule has two components that you specify:

  • A conditional expression that returns true or false.
  • The format(s) that Excel applies when the conditional expression returns true.

When the conditional expression returns true, Excel applies the conditional format. If you change the result of the conditional expression to false by changing a value, Excel removes the conditional format. The format updates automatically.

You'll run into situations where this feature returns unexpected results. First, the logic expressed by multiple rules can introduce ambiguity. In other words, two rules can satisfy the same condition. Second, when applying the same format, precedence matters. You might even encounter both situations in the same set of rules.

You can add multiple rules, but they're not autonomous. Excel applies your rules by order of precedence. Initially, you determine precedence: The rule you enter first has the least precedence, and the rule you enter last has the highest precedence. You can adjust this order at any time, and you can apply the Stop option to any rule to prevent further application when a specific condition is met. In addition, conditional formats take precedence over direct (manual) formats. If you remove a conditional formatting rule, direct formats will remain.

Getting a single rule to work is often easy. Getting multiple rules to work together as you intend requires the correct application of these rules of precedence.

Stating your conditions

It's up to you to manipulate Excel's precedence rules to get the desired results. Let's look at a simple set of conditions you intend to apply to the simple data set shown in Figure A:

  • If there's no date in column C, display a blue fill color for the entire row.
  • If the amount value in column E is greater than 500, display a red fill color to the cell.
  • If the amount value in column E is greater than 100, display a green fill color to the cell.

Each rule seems exclusive, but we'll expose their conflicts as we try to apply these intentions to the data.

Figure A

excelconditionalformata.jpg
We'll apply three simple conditional formatting rules to this data.

Rule 1

Let's start by applying the first rule: If there's no date in column C, display a blue fill color for the entire row. You can do so as follows:

  1. Select C5:E9. The range you initially select is critical to your expectations. In this case, the expectation is to apply the format to the entire row. Therefore, select all columns (and rows) in your data set.
  2. Click the Home tab.
  3. In the Styles group, click Conditional Formatting and then choose New Rule.
  4. In the top pane of the resulting dialog, select Use A Formula To Determine Which Cells To Format.
  5. In the Edit The Rule Description section, enter the following formula:
    =IsBlank($C5)
  6. Click the Format button.
  7. Click the Fill tab, choose blue, and click OK. Figure B shows the conditional expression and the format.
  8. Click OK to return to the sheet and apply the conditional format. So far, so good. The resulting format, also shown in Figure B, is probably what you expected; rows 6 and 7 are blue because C6 and C7 are blank.

Figure B

excelconditionalformatb.jpg
The dialog shows the expression you want to evaluate and the format you want to apply when that expression equals True.

Rule 2

Now let's apply the second rule: If the amount value in column E is greater than 500, that cell is red. Do so as follows:

  1. Select E5:E9.
  2. Repeats steps 2 through 4.
  3. Enter the expression
    =$E5>500
  4. Click Format.
  5. Click Fill, choose red, and click OK. Figure C shows the expression and format.
  6. Click OK to see the applied rule. The values in E5 and E8 are greater than 500, so Excel applies the red fill format (Figure C).

Figure C

excelconditionalformatccombined.jpg
So far, the results probably meet expectations.

Rule 3

Now let's see what happens when you apply the third rule: If the amount value in column E is greater than 100, that cell is green. Most likely, you expect Excel to apply a green fill color to E6. To see what really happens, do the following:

  1. Select E5:E9.
  2. Repeat steps 2 through 4 (from the rule one section).
  3. Enter the following expression
    =$E5>100
  4. Click Format.
  5. Click Fill, choose green, and click OK. Figure D shows the expression and format.
  6. Click OK to see the applied rule. Most likely, the results, also shown in Figure D, aren't what you were expecting.

Figure D

excelconditionalformatdcombined.jpg
Were you expecting this?

The third rule voids the first and second rule for some values because of the conflicts that arise between the conditions. The values in E4, E5, and E8 satisfy rules 2 and 3—any value that's greater than 500 will also be greater than 100. Here's how Excel applies multiple rules when they return true for the same value:

  • When applying different formats, Excel applies all formats because there's no conflict between the formats themselves.
  • When applying the same format, Excel applies the rule (format) with the highest precedence.

Because both rules are applying the same format, the fill color, Excel applies the rule with the highest precedence. At this point, you're probably wondering which rule has the highest precedence. To discern precedence, do the following:

  1. Select the data set: C4:E9.
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. Choose Manage Rules.

The rule at the top of the list has the highest precedence. Each subsequent rule is subordinate to those above it and has precedence over those below it. As you can see in Figure E, the rule that applies the green fill color has a higher precedence than the rule that applies the red fill color. Both of those rules have a higher precedence over the rule that applies the blue fill color to the entire row.

Figure E

excelconditionalformate.jpg
This dialog displays the current rule precedence.

Fix 1: Changing the order

One way to fix the current conflict is to change the order of precedence by moving the red fill color rule up. Simply highlight the rule and click the Up button, as shown in Figure F, and then click OK.

Figure F

excelconditionalformatf.jpg
Change the precedence of the two greater-than rules.

As you can see, changing the precedence fixes the ambiguity between the two greater-than rules. Changing the order clarifies what you mean—apply the green fill color when the value is greater than 100 and less than 500. At this point, you can change the values and Excel will apply these rules as you intended.

If you don't want Excel to change the blue fill color to red or green regardless of the value in column E, move that rule to the top of the list. Otherwise, the two greater-than rules will always take precedence over the blue row fill. In this case, there's no logical error between the rules, so it's strictly a matter of what you want to see. There's no flaw—it's your choice.

Unfortunately, a simple order change won't always solidify your intentions. You might need a more precise application.

Fix 2: A precise expression

Changing the order fixed the problems, but some problems can be avoided from the get-go by using precise expression. Expressions can be tricky, and sometimes you'll need both expression precision and order. In the case of our example, precedence between the red and green fill format no longer matters if you change the green fill color conditional expression to

=AND($E5>100,$E5<500)

This more precise expression removes the ambiguity between the two rules because they no longer overlap. Excel applies the green fill color only when the value is greater than 100 and less than 500. Precedence does still matter regarding the blue-fill rule and how you handle it will be up to you.

Precision formats

Conditional formatting is powerful, but you can avoid frustration by learning how and when Excel evaluates rules. The interface isn't particularly intuitive, but it does allow you to edit rules and change precedence. If you check the Stop If True option for any rule, Excel stops evaluating rules when that rule evaluates to True. Be careful how you implement this option!


Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read....

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox