Highlight an Entire Row Based on One Cell

Excel Formulas › Conditional Formatting

All versionsMixed refs

Make whole rows light up when a status, priority, or flag matches. The secret is a mixed reference — lock the column, let the row float — applied across the full table.

Quick formula: select the whole table (A2:D20), then add a formula rule:
=$D2 = "Overdue"
The $ before the column ($D) but not the row means every cell in a row checks that row’s column D. Matching rows are fully shaded.

Functions used (tap for the full reference guide):

AND

The example

Tasks where Status = “Overdue” get the whole row highlighted.

ABC
1TaskOwnerStatus
2InvoiceAnnOverdue
3ReportBoOn track
4AuditCyOverdue

The formula

The rule, applied to the whole table:

=$D2 = "Overdue" // shades every column in matching rows

How it works

Mixed references make the whole row respond to one column:

  1. Select the entire data range (all columns), e.g. A2:D20 — not just the status column.
  2. Add a formula rule: =$D2 = "Overdue". The $D locks the column so every cell looks at column D; the bare 2 lets the row move.
  3. Excel evaluates the rule per cell. In row 2 every cell checks $D2; in row 3 every cell checks $D3 — so the entire matching row formats together.
  4. Choose a fill and click OK. Change a status and the whole row updates instantly.

Multiple conditions? Combine with AND/OR: =AND($D2="Overdue", $C2>1000) highlights only overdue rows above $1,000. Point the comparison at a dropdown cell (=$D2=$F$1) to make the highlight interactive.

Try it: interactive demo

Live demo

Pick the status to highlight.

Variations

Two conditions (AND)

Overdue AND high value:

=AND($D2="Overdue", $C2>1000)

Match a dropdown

Interactive highlight from F1:

=$D2 = $F$1

Blank-row check

Shade rows missing an owner:

=$B2 = ""

Pitfalls & errors

Mixed reference is everything. Use $D2 — column locked, row free. D2 (relative) shifts per column; $D$2 (absolute) checks only one cell for the whole table.

Select all columns first. The rule only formats cells inside the selected range. Select the full table so the whole row can light up.

Text must match exactly. Trailing spaces or different casing can stop a match — though CF text comparisons are case-insensitive, stray spaces still break them.

Practice workbook

📊
Download the free Highlight an Entire Row Based on One Cell practice workbook
A task table with whole-row highlighting, the AND, dropdown, and blank-row rules applied, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight an entire row based on one cell in Excel?
Select the whole table and add a formula rule with a mixed reference, e.g. =$D2="Overdue". Locking the column ($D) but not the row makes every cell in a row check column D.
Why does only one cell highlight instead of the whole row?
You likely used an absolute reference ($D$2) or selected only one column. Use $D2 and select all the columns you want shaded.
How do I highlight rows based on a dropdown?
Compare to the dropdown cell: =$D2=$F$1. Changing F1 re-highlights the matching rows instantly.

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

Related formulas: Highlight with a formula · Highlight blank rows · IF with AND/OR

Function references: AND