Forum Discussion
Conditional formatting but ignore blanks
I am trying to use conditional formatting to highlight a cell when its value is less than the cell directly above it, but ignore the formula if the cell is blank. Right now it treating blank cells as zero, so the last entry is always highlighted.
2 Replies
Use a Conditional Formatting formula that checks whether the cell is blank before comparing values.
For example, if you're formatting column A starting at A2:
=AND(A2<>"",A2<A1)
How to apply it
Select your range (e.g. A2:A100).
Go to Home → Conditional Formatting → New Rule.
Select Use a formula to determine which cells to format.
Enter the formula above.
Choose your highlight format and click OK.
Result: Only cells containing a value smaller than the cell above will be highlighted. Blank cells will be ignored, even if they contain formulas returning
I recommend adding a rule to check for blank, make it the first rule, and check 'stop if true'.
This is the idea:
