How to Color Cells Based on Value in Google Sheets (Conditional Formatting)
Conditional formatting colors cells automatically based on what's in them: red for negative numbers, green for "Done", a heatmap for a column of scores. The colors update the moment the data changes, so your sheet always highlights what matters.
Color cells with a single-color rule
- Select the cells to format, for example a column of amounts.
- Go to Format → Conditional formatting. A panel opens on the right.
- Under Format cells if…, pick a condition such as Greater than, Text is exactly or Is between, and type the value.
- Choose a fill color under Formatting style.
- Click Done.
Common conditions
| Format cells if… | Use it for |
|---|---|
| Greater than / Less than | Large or small numbers (budgets, scores) |
| Text is exactly | Status words like "Done" or "Overdue" |
| Text contains | Any cell mentioning a keyword |
| Date is before | Overdue deadlines |
| Is empty / Is not empty | Missing or filled-in data |
Make a heatmap with a color scale
For a column of numbers, a color scale shades each cell by how big it is:
- Select the numbers and open Format → Conditional formatting.
- Click the Color scale tab at the top of the panel.
- Pick a preset, or set the min, midpoint and max colors yourself.
Color a whole row based on one cell
To color the entire row when a status column says "Overdue", select the whole table and use a custom formula. With the status in column D:
=$D2="Overdue"The dollar sign before D locks the column, so every cell in the row checks column D. This is the same technique used to highlight duplicates.
Custom formulas for common situations
Choose Custom formula is under Format cells if… and use one of these. Each formula is written for the first row of the selected range (row 2 here); Sheets adjusts it for the rest.
| Color the cell or row when… | Custom formula |
|---|---|
| Another cell is above a target (B2 vs. a target in F1) | =B2>$F$1 |
| The cell contains a word (case doesn't matter) | =ISNUMBER(SEARCH("urgent", A2)) |
| Two conditions are both true | =AND($C2>1000, $D2="North") |
| Either condition is true | =OR($C2>1000, $C2<100) |
| A date is in the past (overdue) | =AND($D2<>"", $D2<TODAY()) |
| A date is in the next 7 days | =AND($D2>=TODAY(), $D2<=TODAY()+7) |
| A checkbox in column A is ticked | =$A2=TRUE |
| Two columns don't match | =$B2<>$C2 |
The dates use TODAY, so the colors update by themselves each day. The checkbox rule is how the to-do list template greys out finished tasks; see also checkboxes in Google Sheets.
Tip: a custom formula must return TRUE or FALSE for the first cell of the range. If the colors appear one row too low or too high, the row number in your formula doesn't match the first row you selected.
Edit or remove a rule
Select the range, open Format → Conditional formatting, and the existing rules appear. Click one to edit it, or hover and click the trash icon to delete it. Rules are checked top to bottom, and the first one that matches wins, so drag them to reorder if two overlap.
Rule not working? If only one column changes color, the wrong rows change, or nothing happens, see conditional formatting not working: three silent mistakes, tested.
Frequently asked questions
Why isn't my rule working?
Check the Apply to range box at the top of the rule, and make sure numbers are stored as numbers, not text. A rule set for "greater than 50" won't fire on the text "60": in our test, a 60 typed as '60 stayed uncolored. See how to convert text to numbers.
Can I copy conditional formatting to other cells?
Yes. Copy a formatted cell, select the target cells, and use Edit → Paste special → Conditional formatting only.
How many rules can one range have?
As many as you like. They stack, and the first matching rule decides the color.
Can a rule refer to another sheet?
Not directly. Wrap the reference in INDIRECT, for example =B2>INDIRECT("Targets!B2"), or copy the values you need onto the same sheet.
Why don't my manual fill colors show?
Conditional formatting is drawn on top of normal formatting. While a rule matches, its color wins; remove or edit the rule to see your own fill again. Alternating colors can also cover a range.