Why does conditional formatting highlight blank cells?
Summary: conditional formatting highlights blank cells because it makes no difference between blanks and zeros. More details follow below.
In the internal Excel system, a blank cell equals a zero value. So, when you create a conditional format for cells less than a certain number, say 20, blank cells get highlighted too (as 0 is less than 20, for empty cells the condition is TRUE).
Another example is highlighting dates less than today. In terms of Excel, any date is an integer greater than zero, meaning an empty cell is always less than today's day, so the condition is satisfied for blanks again.
Solution: Make a separate rule to stop conditional formatting if cell is blank or use a formula to ignore blank cells.
Why aren't blank cells highlighted with conditional formatting?
There may be different reasons for blanks not being formatted such as:
- There is the first-in priority rule that stops conditional formatting for empty cells.
- Your formula is not correct.
- Your cells are not absolutely empty.
If your conditional formatting formula uses the ISBLANK function, please be aware that it identifies only truly empty cells, i.e. cells that contain absolutely nothing: no spaces, no tabs, no carriage returns, no empty strings, etc.
For example, if a cell contains a zero-length string ("") returned by some other formula, that cell is not considered as blank:
Solution: If you want to highlight visually empty cells that contain zero-length strings, apply the preset conditional formatting for blanks or create a rule with one of these formulas.