Formula guide · Excel and Google Sheets · Updated May 13, 2026
Highlight Entire Row with Conditional Formatting
Highlight an entire spreadsheet row when a status, date, or value meets a condition.
Quick Answer
Use a mixed reference like $C2 so the rule checks one column while formatting the whole row.
Copyable Formula
=AND($C2="Open",$D2<TODAY())Syntax
=$C2="Open"Excel and Google Sheets
Worked Example
| C | D | Rule |
|---|---|---|
| Status | Due date | Highlight row |
| Open | 2026-05-01 | TRUE when overdue |
Result: Rows are highlighted when the status is Open and the due date is before today.
Steps
- Select the full row range you want to format.
- Write the rule from the perspective of the first selected row.
- Lock the condition column with a dollar sign.
- Keep the row number relative so the rule changes per row.
Common Mistakes
- Locking both the column and row when the rule should move down.
- Writing the formula for the wrong first row of the selected range.
- Applying the rule to only one column instead of the full row range.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.