How to Use Conditional Formatting in Google Sheets
Conditional formatting colors a cell automatically based on its value — to highlight, rank, or flag data. Select your cells, open Format > Conditional formatting, and set a rule. Here is how to use single-color rules, color scales, and custom formulas.
Conditional formatting makes Google Sheets color your cells automatically based on what they contain, turning a plain table into something you can scan in seconds. Use it to highlight values above a target, flag overdue dates, or spot duplicates — and the colors update themselves as the data changes.
How to apply conditional formatting
- Select the cells you want to format.
- Open the Format menu and click Conditional formatting.
- In the panel on the right, choose a condition under "Format cells if…".
- Pick the fill color (or text style), then click Done.
Single color vs color scale
The panel has two tabs. Single color applies one format when a condition is true — for example, turn a cell green if it is greater than 1000. Color scale shades every cell on a gradient (such as red-to-green), which is perfect for seeing highs and lows across a whole range at a glance.
| Condition | Use it to |
|---|---|
| Greater than / Less than | Highlight numbers above or below a target |
| Text contains | Flag cells that include a word, like "Overdue" |
| Date is before / after | Mark upcoming or past dates |
| Is empty / is not empty | Spot missing entries |
| Custom formula is | Anything the built-in options do not cover |
Custom rules with a formula
For full control, choose "Custom formula is" and enter a test that returns TRUE or FALSE. To shade an entire row when column C says "Overdue", select the whole range and use:
=$C1="Overdue"The dollar sign locks the column so the whole row is tested — the same TRUE/FALSE logic behind the IF function. Doing this in Excel instead? See conditional formatting in Excel.
Edit or remove a rule
- To change a rule: open Format > Conditional formatting, click the rule, and edit it.
- To delete a rule: hover over it in the panel and click the trash icon.
- Rules apply top to bottom — the first matching rule wins, so drag them to reorder if two overlap.
Conditional formatting pairs well with other tools: highlight repeats before you remove duplicates in Google Sheets.
Frequently asked questions
How do I highlight an entire row in Google Sheets?
Select the whole range, choose Format > Conditional formatting > Custom formula is, and lock the column with a dollar sign — for example =$C1="Overdue". Because the column is fixed, the full row is colored whenever the test is true.
Why is my conditional formatting not working in Google Sheets?
Usually the range is wrong, the custom formula is missing the dollar sign, or a higher rule is overriding it. Open the Conditional formatting panel to check the range, the formula, and the order of the rules.
Can I copy conditional formatting to other cells?
Yes. Copy a formatted cell, select the target cells, then use Edit > Paste special > Conditional formatting only. The rules copy across and adjust to the new cells.