Open a report with five hundred rows and your eye has nowhere to land. Conditional formatting decides where it should — red on the three overdue invoices, a heat map that makes the outliers obvious, a green flag on the rows that cleared. The data doesn’t change; what changes is how fast a reader finds the part that matters. This walks the three kinds of rule, the custom formulas that reach past the menu, and the absolute-reference detail that trips up almost everyone the first time.
The three kinds of rule
Every conditional formatting rule in Google Sheets is one of three types, and knowing which one fits saves you from fighting the wrong tool. They all live under Format → Conditional formatting; the difference is how they decide what to color.
| Type | What it does | Best for |
|---|---|---|
| Single color | One format when a condition is true | Thresholds: overdue, low stock, flags |
| Color scale | A gradient across a range of values | Heat maps: spotting highs and lows |
| Custom formula | Any rule you can write as TRUE/FALSE | Whole-row highlights, cross-cell logic |
Most people only ever use single color, which covers the bulk of report formatting. Color scales are for when you care about the shape of a whole column at a glance, and custom formulas are the escape hatch for anything the built-in conditions can’t express — which, as you’ll see, is where the real power sits.
Single-color rules for thresholds
The everyday rule: color a cell when it crosses a line. Overdue dates, low inventory, numbers below target — each is a single-color rule with a condition and a format.
- Select the range — say the due-date column.
- Open Format → Conditional formatting.
- Under “Format cells if,” pick a condition like “Date is before” and choose today.
- Set the fill color and click Done.
Overdue dates now turn red on their own, and they keep doing it as dates pass — a row that was fine yesterday flags itself the moment it slips. The built-in conditions cover most thresholds without a formula: greater than, less than, between, text contains, is empty, date before or after. Reach for these first; they’re faster to set up and easier for the next person to understand than a custom formula doing the same job.
A couple of everyday examples make the pattern concrete. On an inventory sheet, a “less than 10” rule on the quantity column turns low stock red before it becomes a stockout you find out about from a customer. On a project tracker, “text is exactly Blocked” on the status column makes blocked tasks impossible to miss in a long list. The recipe never changes — pick the column, pick the condition, pick a color whose meaning you’ll keep consistent across the whole report. The discipline is in that last part: a color only helps if it means the same thing everywhere it appears.
Color scales turn a column into a heat map
When you care less about a single threshold and more about how every value compares to the rest, a color scale paints the whole range as a gradient. Pick the type, set a minimum and maximum color — and optionally a midpoint — and the column becomes a heat map.
| A | B (heat) | |
|---|---|---|
| 1 | North | 420 |
| 2 | South | 1180 |
| 3 | West | 2050 |
At a glance, the lowest values lean one color and the highest another, with everything in between shaded by where it falls. This is the fastest way to find the laggards and the standouts in a long column of numbers — sales by region, response times, scores — without sorting or scanning. Color scales work best on a single metric column; applied across columns with different scales, the gradient compares numbers that shouldn’t be compared and the picture turns to noise.
Set a midpoint color when zero or a target matters — green above target, white at it, red below — so the scale reads as good-to-bad rather than merely low-to-high. Without a midpoint, a column of mostly-fine numbers with one dramatic value washes everything else to one end of the gradient, hiding the smaller differences you might actually care about. The midpoint is what turns a raw heat map into one that answers “are we on track?” at a glance.
Custom formulas for what the menu can’t express
The built-in conditions check one cell against one value. The moment your rule depends on another cell — “color this row if its status is Done” — you need a custom formula, which colors based on any expression that returns TRUE or FALSE.
Highlight an entire row
=$B1="Done"Apply this to the whole data range (not just column B) and every row whose column B reads “Done” fills with your chosen color — the entire row, not just the one cell. The dollar sign locks the formula to column B while letting the row number move, so each row checks its own B value. This single pattern — whole-row highlight driven by a status column — is the most useful conditional format most reports never use.
Flag duplicates
=COUNTIF($A$1:$A$100, A1) > 1This colors any value that appears more than once in the range, which catches double-entered invoices, repeated emails, or accidental copy-paste before they cause a problem. Pair conditional formatting with data validation and you cover both ends: validation stops bad input going in, formatting surfaces anything questionable that’s already there.
Highlight rows by date, and combine conditions
=$E1 < TODAY()Applied to the whole range with due dates in column E, this flags every row already past due — the report’s most urgent rows color themselves every morning without anyone touching the sheet. Custom formulas also combine conditions, which is where they pull far ahead of the menu:
=AND($E1 < TODAY(), $C1 <> "Closed")This highlights only the rows that are overdue and not yet closed — usually the exact list you act on, rather than every past date including the ones already handled. For the deeper formula patterns, the advanced conditional formatting techniques guide goes further into multi-condition rules.
The absolute-reference trap
This is the detail that turns a five-minute task into a confusing twenty. Custom formula rules use the same relative-and-absolute reference rules as ordinary formulas, and getting the dollar signs wrong makes the formatting land on the wrong cells.
=$B1="Done" locks the column (B) and lets the row float — correct for a whole-row highlight. =B1="Done" with no lock drifts column by column; =$B$1="Done" locks everything and checks only one cell against every row. The single dollar sign placement is the whole game.The rule of thumb: lock what should stay fixed, leave free what should move. For a whole-row rule keyed on column B, you want $B1 — column fixed, row free. For a rule comparing each cell to a single target cell, lock the target fully with $B$1. When formatting lands on the wrong cells, the dollar signs are almost always why, and the official conditional formatting documentation shows the menu side of each rule type.
Keep report formatting readable
Formatting earns attention, and attention is a budget. Color everything and you’ve colored nothing — the reader’s eye gives up when the whole sheet is a rainbow.
- ✓ Three formats per report is plenty — one for trouble, one for success, maybe one heat map
- ✓ Use color consistently: if red means “overdue” in one place, it can’t mean “high value” in another
- ✓ Rules are ordered — the first matching rule wins, so put the most specific at the top
- ✓ Bound your ranges so rules don’t quietly evaluate thousands of empty rows
Conditional formatting that’s used sparingly is read instantly; used everywhere, it becomes wallpaper. A tracker like a sales pipeline sheet benefits from exactly two or three rules — deals closing soon, deals gone stale — not a dozen competing colors.
Where it pays off
The goal isn’t a colorful sheet; it’s a report that answers its own most-asked question before anyone scrolls. Single-color rules flag the thresholds that need action, color scales reveal the shape of a column at a glance, and custom formulas express the logic the menu can’t. Add the few rules that point at what matters, keep the dollar signs right, and resist coloring the rest — and a wall of rows turns into a report someone can read in seconds. That speed is the entire return on a few minutes of setup. Every time the report opens, the formatting does the first pass of reading for whoever’s looking, pointing them at the three rows that need a decision instead of leaving them to scan the four hundred that don’t. Build the rules once, keep them few, and the report does that work on every open from then on.