Conditional Formatting With Formulas in Excel
Use formulas in conditional formatting to highlight whole rows, overdue dates, duplicates, top values and targets, with the $ reference rule that makes it work.
By Ayoub Farid · · 2 min read
Built-in rules (greater than, duplicates, colour scales) cover simple cases. With a formula, you can highlight anything: a whole row when the status is "Late", dates within a week, sales below their own target.
How to add a formula rule
- Select the range to format, starting from the top-left cell (e.g. A2:F100).
- Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Type a formula that returns TRUE or FALSE for the top-left cell.
- Choose a format. OK.
Excel applies the formula to every cell in the range, adjusting references exactly as if you'd filled it.
The $ rule
This is where most rules go wrong.
Highlight the whole row when column F says "Late":
=$F2="Late"$F: always look at column F, even when formatting column A, B, C…2(no $): move down with each row.
Without the $, column B would check G2, column C would check H2, and so on. See mixed references.
Useful formula rules
| Highlight | Formula (range starts in row 2) |
|---|---|
| Overdue dates (C) | =AND($C2<TODAY(),$C2<>"") |
| Due within 7 days | =AND($C2>=TODAY(),$C2<=TODAY()+7) |
| Below target in F1 | =$B2<$F$1 |
| Below own target (C) | =$B2<$C2 |
| Duplicates in A | =COUNTIF($A$2:$A$500,$A2)>1 |
| Top 3 sales | =$B2>=LARGE($B$2:$B$100,3) |
| Every other row | =MOD(ROW(),2)=0 |
| Blank required field | =$D2="" |
Test the formula first
Type the formula into a spare cell next to row 2 and fill it down. If it returns TRUE in the right rows, it will work as a rule.
Manage and fix rules
Home > Conditional Formatting > Manage Rules shows every rule, its formula and the range it applies to. Most "it highlights the wrong rows" problems are a wrong $ or a range starting at a different row than the formula.
Frequently asked questions
How do I highlight a row based on a cell value?
Select the rows, add a formula rule like =$F2="Late", with $ before the column only.
Why is my conditional formatting highlighting the wrong cells?
The formula's references don't match the top-left cell of the range, or the $ signs are wrong.
How do I highlight dates before today?
Use =$C2<TODAY() as the rule formula.
Does Google Sheets support formula-based conditional formatting?
Yes: Format > Conditional formatting > Custom formula is.