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.
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.
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.
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
DownloadMore on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel