How to avoid a conditional formatting rule in Excel that doesn't work as expected

Formulaic conditional formatting rules in Microsoft Excel can be tricky, so learn how to avoid a common mistake.

spreadsheet screen laptop on conference room mockup

Image: milindri, Getty Images/iStock Photo

Microsoft Excel's conditional formatting is a flexible tool that applies formatting on the fly; as you update the data, the rule updates the formatting accordingly. It's easy to apply but doesn't always work the way you expect. Sometimes you don't even notice that it isn't working as expected. 

In this article, we'll look at a formulaic rule in Excel that works in one instance, but not another. We'll begin with a simple expression that highlights specific values in a four-column data set. Then, we'll try to apply the same rule to a set of labels, where it will fail.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

I'm using (desktop) Office 365 but you can work with earlier versions. To follow along, download the demonstration .xlsx file. The browser will support existing conditional formatting rules, but you can't implement a formulaic rule in the browser.

LEARN MORE: Office 365 Consumer pricing and features

The conditional formatting rule that works

You've probably worked with formulaic rules in Excel before—they're simple to implement but can be tricky. By applying a rule to a data set (basically an array), you can use one rule to format the selected values. To illustrate, we'll highlight values less than or equal to the value 21 in the simple data set shown in Figure A. (Number 21 and the equality operator are arbitrary to the example.)

Figure A

exceldf-a.jpg

  We'll use a conditional formatting rule to highlight values equal to or less than 21.

To get started, select the data you want to format in Excel, in this case, that's C3:F6 and do the following: 

  1. On the Home tab, click Conditional Formatting (in the Styles group) and choose New Rule.
  2. Select the last rule in the top pane (Use a Formula).
  3. In the second pane, enter the following expression:
    =C3:F6<=21
  4. Click the Format tab and choose any format to apply. I chose red from the Color dropdown (on the Font tab).
  5. Click OK and view the formula and the format (Figure A) before clicking OK to see the results shown in Figure B.

SEE: Cheat sheet: Windows 10 (free PDF) (TechRepublic)

Figure B

exceldf-b.jpg

  All values in the selected range (C3:F6) equal to or less than the value 21 are red.

This is a simple rule in Excel, and you've probably applied something similar to your own work. It works great as is, but what if you want to highlight only the corresponding names and not the values? You might assume that the rule would work the same for the names, but it doesn't because the conditional reference contains more than one column. Let's see what happens.

SEE: 50 Excel tips every user should master (TechRepublic) 

Adding a conditional formatting monkey wrench

When applying a conditional format, you select the range you want formatted. Let's suppose instead of the values, we want to apply the format to the corresponding names in column B. If we apply the same rule to B3:B6, we see the results shown in Figure C. A you can see, it doesn't work as you might expect. (I entered the same expression, C3:F6<=21.)

Figure C

exceldf-c.jpg

  The expression doesn't return the results you expected.

The biggest problem with this rule is that it is working—but not the way you expected. If you hadn't already seen the results in the last section, would you have noticed the problem? You might in such a small data set, but in a large one, you might miss it.

This is one of those times when a screenshot is more effective than an explanation. Figure D shows the same expression entered into G3 and copied to G6. As you can see, you get an "array" result—four TRUE or FALSE values for each row. The TRUE values in column G, match the formatted values in column B. The rule can't crunch all those results into one value for a single-column—it's a column-to-column rule. Excel applies the TRUE/FALSE results of the first column in the rule range, column C, to the values in column B.

Figure D

exceldf-d.jpg

  The expression returns a four-column array result.

You might have noticed that another problem has been exposed: Why does the blank cell, G3, return TRUE? Look no further than the equality operator, <=. When comparing values using an equality operator, Excel evaluates a blank cell as if it were 0; therefore, a blank cell is less than or equal to 21.

At this point, you're probably wondering what will work in Excel.

Use a logical OR operator in conditional formatting

The equality operator we used in the last section was easy to implement, but it doesn't work as expected when applied to a single-column range. The answer in this case is an OR operator. The rule is harder to write and manage if you have lots of columns, but it will work as expected.

If you're not familiar with the OR operator, here are a few basics:

  • The OR operator allows you to compare multiple conditions.
  • If any condition evaluates to TRUE, the OR operator returns TRUE.

Our expression follows:

=OR(C3<=21,D3<=21,E3<=21,F3<=21)

Instead of expressing the data set as an array, the OR operator allows us to explicitly compare each value in the data set. If any condition returns TRUE, the OR expression returns TRUE and consequently, Excel applies the conditional format, as shown in Figure E.

Figure E

exceldf-e.jpg

  By evaluating the values individually, instead of an array, we get the results we want.

How to avoid mistakes with formatting in Excel

You might think viewing what doesn't work isn't helpful, but in this case I think it is. It's a good exercise for understanding how Excel evaluates ranges. Once you have a better understanding of how things work "under the hood," you can avoid rules that don't work as expected, and perhaps worse, go undetected.

Send me your Microsoft Office questions 

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 see