Why Conditional Formatting Not Working in Excel

Last Updated: September 15, 2023
puneet-gogia-excel-champs

- Written by Puneet

In Excel, when you use conditional formatting, there are some chances when you apply it, but it doesn’t work. And 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.

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.

The result of the Condition Needs to be TRUE or FALSE

When you use a formula in conditional formatting, the result of that formula needs to be TRUE or FALSE. Then only the conditional formatting will work. The result should always be TRUE or FALSE if you use LOGICAL OPERATORS.

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.

When Numbers are Formatted as Text

If you use a conditional formatting rule where the rule needs to check for a condition meant for a number, those numbers have text format. In that case, that conditional formatting rule will not work correctly.

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.

Get the Excel File

Download