Why Conditional Formatting is Not Working in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

You have a sales report in Excel and want to highlight cells with sales over $1000 using conditional formatting. You set up the rule, but nothing changes in your cells.

The issue is that your sales data has extra spaces or text, making Excel read them as text instead of numbers.

In Excel, when you use conditional formatting, there are some chances when you apply it, but it doesn’t work. In this situation, you need to troubleshoot the formatting you have used.

While applying pre-define conditional formatting, you might not face issues, but when using custom formulas, there’s a chance.

1. Incorrect Reference

When you apply conditional formatting using the formula, you may refer to a different cell by mistake. See the example below: The range where I want to apply the conditional formatting rule is A2:A12.

incorrect-reference

And the formula needs to refer to cell A2 instead of A1. And because I referred to A1 in the formula, the rule has been wrongly applied.

You can see that the Condition is to test if the value is greater than 1200 or not, and in the cell, it has highlighted the cell even when the value is not greater than 1200. It is all because of the wrong reference.

When working with conditional formatting, one of the major reasons for not working is the wrong reference.

2. The Result of the Condition Needs to be TRUE or FALSE

In conditional formatting, the result of the condition must be TRUE or FALSE. If your formula doesn’t return TRUE or FALSE, the rule won’t work. Excel won’t know how to apply the formatting if your formula calculates a number or text string instead of a logical value.

To fix this, write your formula to produce a TRUE or FALSE result. For instance, use a comparison operator like =A1>100 instead of =A1+100. This way, the formula checks if the condition is met (TRUE) or not (FALSE). Correcting your formula ensures Excel can apply the conditional formatting rule properly.

You first enter that formula in the worksheet to see what the result turns out to be.

3. While Copying and Pasting the Conditional Formatting

If you have the absolute reference in the formula and try to copy and paste conditional formatting to a new cell or a range of cells, the format will apply with the wrong Condition.

copy-and-paste-conditional-formatting

In the above example, our reference is absolute; you can see the dollar signs. And when you try to copy and paste this conditional formatting to a new place, it will always refer to the cell A2.

4. When Numbers are Formatted as Text

Incorrect data format is a common reason why conditional formatting might not work. It happens when the data in your cells isn’t in the format with the right type.

For example, numbers may be stored as text because of extra spaces, non-numeric characters, or being formatted as text. Excel can’t apply conditional formatting correctly if it doesn’t recognize the data type.

when-numbers-formatted-as-text

In the above example, columns A and B have the same conditional formatting rule, which tests the numbers above 1200. But in column B, the number is entered as text, and the rules highlight all the numbers. Compared to it, in column A, where numbers are stored as numbers, conditional formatting is working fine.

5. Rule Order Conflict

Rule order conflict is when you have multiple conditional formatting rules in Excel that interfere with each other. This happens because Excel applies the rules in the order they are listed. If a later rule contradicts an earlier one, it can cancel or override the first one.

For example, if you have a rule to highlight cells with sales over $1000 in green and another rule to highlight sales over $500 in yellow, the second rule might change the cells from green to yellow.

To fix this, check the order of your rules. You can rearrange them so that the most important rules are at the top. Use the “Move Up” or “Move Down” buttons in the Conditional Formatting Rules Manager to change the order.

6. IF a Worksheet is Protected

Worksheet protection is a reason why conditional formatting might not work in Excel. When a worksheet is protected, it prevents any changes to the cells, including applying or updating conditional formatting.

It means that even if you set up a rule correctly, Excel won’t use it because the sheet is locked.

Navigate to the “Review” tab and click “Unprotect Sheet”. If a password is set, you’ll need to enter it. Once the sheet is unprotected, you can apply or update your conditional formatting rules.

7. Problem with Merged Cells

Conditional formatting doesn’t work well with merged cells. Merged cells combine two or more cells into one, which can confuse Excel when applying rules.

For example, if you merge cells in a row and try to highlight those based on their values, Excel may not use the formatting correctly because it needs to learn how to handle the merged cell.

To fix this, avoid merging cells where you want to use conditional formatting. Instead, use center alignment to achieve a similar look without merging.

Keeping cells unmerged ensures Excel can apply conditional formatting rules accurately and effectively.

9. Using a Function which is Not Available

Using a new function in conditional formatting can cause errors if the function is not available in your version of Excel. It happens when you try to use a formula supported in newer versions rather than older ones. For example, a function like TEXTJOIN in Excel 2016, which doesn’t help it, will result in an error.

To fix this, check the compatibility of the functions you use with your Excel version. If the function isn’t supported, look for an alternative that works in your version. You can find compatibility information in Excel’s help resources or online.

The best way is to try that function in the worksheet to see if it’s viable.

10. Wrong Range Used in Reference

This happens when the rule is applied to the wrong range of cells. For example, if you want to highlight sales over $1000 but accidentally select the wrong column or row, the formatting won’t appear where you expect.

To fix this, double-check your selected range before applying the rule. Make sure it includes all the cells you want to format.

Bonus Tip

Using structured table references in conditional formatting formulas can cause errors. Instead, you should use regular cell references that dynamically adjust with your table.

Structured table references don’t work directly in conditional formatting formulas because conditional formatting expects simple cell references or relative references. Conditional formatting rules are designed to apply to a range of cells.

If you still face a problem while using conditional formatting, try opening Excel in the safe mode to troubleshoot, or try to clear the cache from the Excel application.

Last Updated: May 20, 2024