How to Highlight a Row Based on a Cell in Google Sheets
=$B2="Done". The whole row changes color when column B says Done.
The steps
- Select the cells you want to format.
- Click Format, then Conditional formatting. A toolbar opens on the right.
- Under "Format cells if", choose Custom formula is.
- Enter your formula in the Value or formula box and choose the formatting.
- Click Done.
The example
Here is a task list in cells A1:C5. You want a whole row colored when its Status is Done.
| A | B | C | |
|---|---|---|---|
| 1 | Task | Status | Hours |
| 2 | Report | Done | 6 |
| 3 | Slides | Open | 3 |
| 4 | Audit | Done | 8 |
| 5 | Invoice | Open | 2 |
Color the whole row
Select A2:C5, which covers every column of the rows. Write the formula for the first row of the selection:
=$B2="Done"
This colors the Report and Audit rows. Here is why: the dollar sign before B locks the check to column B, so cells in columns A, B and C all look at the Status in their own row. The row number has no dollar sign, so it moves down with each row. Google's help page gives the same idea with =$B1="Yes" over columns A to E.
Use a number test
The condition can be any comparison. To color rows where Hours is above 5:
=$C2>5
This colors the Report (6) and Audit (8) rows.
Rules run in order
Per Google's page, rules run in the order listed and the first one that is true sets the format. Drag the rules to reorder them if two rules could apply to the same row.
Common mistakes
- Forgetting the dollar sign before the column. Without it,
B2shifts to C2 and D2 in the next columns, and the colors come out wrong. - Locking the row too.
$B$2checks only one cell, so every row looks at the same value. - Selecting only column B. To color the whole row, select all of its columns.
- Missing quotation marks around text such as
"Done".
Try it yourself
Type your formula into the box, starting with =, then press Check. This is the formula you would enter in the conditional formatting panel.
Exercise 1: Rows marked Done
Write the custom formula that colors the whole row when the Status in column B is Done. The rule is written for row 2.
Exercise 2: More than 5 hours
Write the custom formula that colors the whole row when the Hours in column C are greater than 5. The rule is written for row 2.
What to learn next
- Highlight duplicates: color repeated values automatically.
- IF: run a test and return one of two results.
- COUNTIF: count the rows that match a condition.
Syntax and rules in this guide were checked against Google's Highlight a row based on a cell help page. See all guides.