Several statuses
=OR($D2="Complete",$D2="Cancelled")Formats two final states.
PASTE-READY RECIPE
Highlight an Entire Row Based on Status in Google Sheets
Color every cell in a row when its status column contains a chosen label.
QUICK ANSWER
The formula and where it fits.
Color every cell in a row when its status column contains a chosen label. Use =$D2="Complete", then replace the sample ranges with the columns in your sheet.
Apply this custom formula to the full row range, for example A2:D.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Owner | Due | Status |
| 2 | ||||
| 3 | ||||
| 4 |
FORMULA OUTPUTSend invoice · Call supplierRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Not complete
=$D2<>"Complete"Formats every unfinished row.
Status plus date
=AND($D2<>"Complete",$C2<TODAY())Marks unfinished overdue rows.
COMMON ERRORS
When the result looks wrong.
Only the status cell changes color
Set Apply to range to the complete table width.
Every row follows row 2
Use $D2, not $D$2.
QUESTIONS
Answers before you paste.
Only the status cell changes color
Set Apply to range to the complete table width.
Every row follows row 2
Use $D2, not $D$2.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.