Conditional formatting can do far more than the built-in presets once you drive it with a formula. The key technique: highlight an entire row based on one cell’s value, using a rule with a cleverly locked reference.
$C) and leave the row relative (2) so the rule slides down each row but always reads column C.
The example
An invoice list; overdue rows are shaded end-to-end.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Amount | Status |
| 2 | INV-01 | $120 | Paid |
| 3 | INV-02 | $80 | Overdue |
| 4 | INV-03 | $200 | Paid |
| 5 | INV-04 | $60 | Overdue |
The formula
The conditional-formatting rule (not a cell formula) applied to the whole data range A2:C5:
How it works
Set it up in four clicks, and the mixed reference does the magic:
- Select the full data range first —
A2:C5— before opening the rule, so it applies to every cell. - Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Enter
=$C2="Overdue". The$Clocks the column so every cell in a row checks column C; the relative2lets the rule move down row by row. - Pick a fill color. Excel evaluates the rule for each cell; whenever its row’s C value is "Overdue", the cell — and thus the whole row — is shaded.
The single most common mistake: getting the dollar signs wrong. $C2 highlights whole rows. C2 (no lock) highlights only single cells and drifts. $C$2 (both locked) makes every cell test one fixed cell. For whole-row highlighting you want column-locked, row-relative: $C2.
Try it: interactive demo
Choose what to highlight; see the rule and the result.
Variations
Highlight on a number threshold
Shade rows where the amount is over $150:
Two conditions (AND)
Overdue and over $100:
Highlight duplicate rows
Drive the rule with COUNTIF:
Pitfalls & errors
Only one cell highlights, not the row. You used C2 instead of $C2. Lock the column with a single dollar sign.
The rule applies to the wrong cells. Conditional formatting applies to whatever was selected when you created the rule. Check (and fix) the range in Manage Rules → Applies to.
The formula must return TRUE/FALSE. A conditional-formatting formula is a logical test, not a value — it should evaluate to TRUE or FALSE for each cell.
Practice workbook
Frequently asked questions
How do I highlight an entire row based on one cell in Excel?
Why does only one cell get highlighted instead of the whole row?
Can a conditional-formatting formula use AND or COUNTIF?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class