Conditional Formatting in Google Sheets: A Practical Guide
Conditional formatting turns a grid of numbers into something your eyes can scan: overdue tasks glow red, top performers glow green, duplicates reveal themselves. Here is how to use it well in Google Sheets — including the custom-formula trick that unlocks most real-world use cases.
The basics
Select a range, then go to Format > Conditional formatting. A panel opens on the right where you pick a condition ("Greater than", "Text contains", "Date is before") and a formatting style. Rules live with the range, and you can stack multiple rules — the first matching rule wins.
Color scales for instant heatmaps
Switch to the Color scale tab to paint a range from low to high. This is the one-click heatmap: apply it to a pivot table of sales by region and month, and the strong and weak spots surface immediately.
Custom formulas: the real power
The built-in conditions only look at each cell in isolation. Choose Custom formula is and you can format cells based on other cells. The rule applies your formula to the top-left cell of the range, then fills it down and across like a normal formula — so lock columns and rows with $ exactly as you would when copying.
Highlight an entire row
Select A2:F100 and use:
=$E2="Overdue"
The $E pins the check to column E while the missing $ before 2 lets the row float — every cell in a row lights up when that row’s status is Overdue.
Flag duplicates
=COUNTIF($A$2:$A$100, A2)>1
Highlight weekends in a date column
=WEEKDAY(A2, 2)>5
Rows due in the next 7 days
=AND($D2>=TODAY(), $D2<=TODAY()+7)
Keep it fast and maintainable
- Apply rules to bounded ranges (
A2:F1000) rather than whole columns when sheets get large — volatile rules on a million cells slow everything down. - Fewer, smarter rules beat many overlapping ones. Remember: first match wins, so order matters.
- Use a legend. A tiny key in a corner cell ("red = overdue, yellow = due this week") saves every future reader a guess.
If you take one thing from this guide, make it the row-highlight pattern — =$E2="something" with a locked column letter. It covers ninety percent of what people actually want conditional formatting to do.