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

The formulaic conditional formatting rules in Microsoft Excel can be tricky, so learn how to avoid the common mistake.

Laptop with spreadsheet screen in conference room model

Photo: Melindry, Getty Images/iStockPhoto

Microsoft Excel conditional formatting is a flexible tool that applies formatting quickly; As the data is updated, the rule updates the format accordingly. It’s easy to apply but doesn’t always work the way you’d expect. Sometimes you don’t even notice that it’s not working as expected.

In this article, we’ll look at the formula rule in Excel that works in one instance, but not in another. We’ll start with a simple expression that highlights certain values ​​in a four-column data set. Next, we’ll try to apply the same rule to a bunch of labels, as they’ll fail.

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

I’m using Office 365 (for desktop) but you can work with earlier versions. To continue, download the .xlsx demo file. The browser will support existing conditional formatting rules, but you cannot implement a syntax rule in the browser.

Learn more: Office 365 Consumer pricing and features

Conditional formatting rule that works

You may have worked with formula rules in Excel before – they are easy to implement but can be challenging. By applying a rule to a data set (essentially an array), you can use a single rule to format the given values. For clarity, we will highlight values ​​less than or equal to 21 in the simple data set shown in Figure A. (The number 21 and the equality operator are arbitrary in the example).

Figure A

exceldf-a.jpg

We will use a conditional formatting rule to mark values ​​that are equal to or less than 21.

To get started, select the data you want to format in Excel, in this case, it’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 at the top (use a formula).
  3. In the second part, enter the following expression:
    = C3: F6
  4. Click the Format tab and choose which format to apply. I chose red from the “Color” drop-down list (on the “Font” tab).
  5. Click OK and view the formula and formula (Figure A) before clicking OK to see the results displayed in Figure B.

We see: Cheat Sheet: Windows 10 (Free PDF) (TechRepublic)

Figure B

exceldf-b.jpg

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

This is a simple rule of thumb in Excel, and you may have applied something similar to your work. It works great as is, but what if you want to highlight only the corresponding names and not the values? You might assume the rule will work the same for names, but it doesn’t work because the conditional reference has more than one column. Let’s see what happens.

We see: 50 Excel Tips Every User Should Master (TechRepublic)

Add conditional formatting monkey wrench

When you apply conditional formatting, you specify the range you want to format. Suppose that instead of values, we want to apply the format to the corresponding names in column B. If we apply the same rule to B3: B6, we will see the results displayed in Figure C. You can see that it is not working as you might expect. (I entered the same expression, C3: F6

Figure C

exceldf-c.jpg

The expression does not return the results you expected.

The biggest problem with this rule is that it is being Working – but not in the way I expected. If you haven’t already seen the results in the last section, did you notice the problem? You might be in such a small set of data, but in a large set, you might miss it.

This is one of the times when a screenshot is more effective than an explanation. figure d The same expression is entered into G3 and transcribed into G6. As you can see, you get an “array” result – four TRUE or FALSE values ​​per row. The TRUE values ​​in column G match the formatted values ​​in column B. The rule cannot aggregate all these results into a single value for one column – it is a column-to-column rule. Excel applies TRUE / FALSE results for first In the base range, column C, to the values ​​in column B.

figure d

exceldf-d.jpg

The expression returns the result of an array of four columns.

You may have noticed that another issue has been detected: Why does the empty cell, G3, return TRUE? Look no further than the equality operator, is being less than or equal to 21.

At this point, you might be wondering what will Work in Excel.

Use the OR logical 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 scope. The answer in this case is the OR operator. It’s hard to write and manage the rule if you have a lot of columns, but it is will Work as expected.

If you’re not familiar with the OR operator, here are some basics:

  • The OR operator allows you to compare multiple conditions.
  • if Which The condition evaluates to TRUE, and the OR operator returns TRUE.

Our expression is:

=OR (C3

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

Figure E

exceldf-e.jpg

By evaluating the values ​​individually, rather than an array, we get the results we want.

How to avoid formatting errors in Excel

You might think that showing what is not working is not useful, but in this case I think it is. It is a good exercise to understand 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 unnoticed.

Send me your questions about Microsoft Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

see also