Checkbox
=$C2=TRUEFormats the row when the checkbox in C is checked.
PASTE-READY RECIPE
Conditional Formatting Based on Another Cell in Google Sheets
Apply a format to one cell by testing the value in another column.
QUICK ANSWER
The formula and where it fits.
Apply a format to one cell by testing the value in another column. Use =$C2="Overdue", then replace the sample ranges with the columns in your sheet.
The dollar sign locks the tested column while each row changes.
- 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 | |
|---|---|---|---|
| 1 | Task | Owner | Status |
| 2 | |||
| 3 | |||
| 4 |
FORMULA OUTPUTReview budgetRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Past due date
=AND($C2<>"Done",$B2<TODAY())Combines status and date.
Contains text
=REGEXMATCH(LOWER($C2),"overdue|blocked")Matches either status word.
COMMON ERRORS
When the result looks wrong.
Only the first row reacts
Apply the rule to the full target range and use the first data-row number in the formula.
The wrong column moves
Lock the tested column with a dollar sign.
QUESTIONS
Answers before you paste.
Only the first row reacts
Apply the rule to the full target range and use the first data-row number in the formula.
The wrong column moves
Lock the tested column with a dollar sign.
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.