If the mechanics of custom-formula rules are the engine — how the dollar signs and rule order work — then this is the parts box: a set of ready-to-paste rules for the jobs that come up again and again. Pick the recipe that matches your problem, copy the formula into Format → Conditional formatting → Custom formula, point it at your range, and you’re done. Each one below is a complete setup, not a fragment.
Recipe 1: highlight duplicate entries
The single most-requested rule. To color every value that appears more than once in column A, apply this to the range A2:A:
=COUNTIF($A$2:$A, A2)>1COUNTIF tallies how many times the current row’s value shows up across the whole column; anything over one gets the format. Lock the range with both dollar signs ($A$2:$A) so every row counts against the same full list, but leave A2 relative so each cell checks its own value. Flip the comparison to =COUNTIF($A$2:$A, A2)=1 and you highlight the unique entries instead — the ones that appear exactly once, which is often what you actually want when hunting for the odd record out.
For duplicates defined across several columns — a row is a dupe only when first name and last name and email all match — COUNTIF can’t see the combination, so switch to COUNTIFS: =COUNTIFS($A$2:$A, $A2, $B$2:$B, $B2, $C$2:$C, $C2)>1. Each pair adds another column that has to match for the row to count as a repeat. This is the rule that catches the sneaky duplicates a single-column check misses, the ones where one field differs but the record is plainly the same person.
Recipe 2: a red-amber-green status light
Turn a bare number — a score, a days-remaining count, a percentage — into an at-a-glance traffic light by stacking three rules on the same range, each with its own color.
| Rule (custom formula) | Fill color | Means |
|---|---|---|
=$B2>=90 |
Green | On track |
=AND($B2>=70, $B2<90) |
Amber | Watch |
=$B2<70 |
Red | At risk |
Order matters here: because Google Sheets stops at the first rule that fires, put the green rule with its >=90 threshold and the explicit amber band AND($B2>=70, $B2<90) so the ranges don’t overlap. Apply all three to the whole row range (A2:F) instead of just column B, and the entire row lights up by status — the fastest way to make a tracker readable from across the room.
Recipe 3: a heatmap with a color scale
For a grid of numbers — monthly sales by region, survey scores, anything where magnitude matters — a color scale beats individual rules. It shades every cell on a gradient from low to high, no formula required.
- Select the numeric grid, then open Format → Conditional formatting.
- Switch the tab from “Single color” to Color scale.
- Set Minpoint to a light shade and Maxpoint to a strong one; add a Midpoint for a three-color diverging scale.
The result reads like a thermal image of your data — hot cells jump out, cold ones recede, and patterns that were invisible in a wall of numbers become obvious. Use a single-hue scale (white to dark blue) when only the high end matters, and a diverging scale (red-white-green) when both extremes are meaningful, like profit-and-loss figures where you want losses and gains to pull in opposite directions.
One gotcha decides whether the heatmap tells the truth: a color scale compares every cell in the range it’s applied to. Select the whole grid and a huge January sales figure makes every other month look uniformly cold, because they’re all measured against that one outlier. If you want to compare within each column instead — each region against its own range — apply a separate color scale to each column rather than one across the lot. Match the range to the comparison you actually mean, or the colors will quietly mislead.
Recipe 4: true zebra stripes that survive sorting
The built-in Format → Alternating colors is fine until you sort or filter, after which the bands scramble. A custom formula ties the striping to the row number instead, so it stays put:
=ISEVEN(ROW())Applied to your whole table range, this shades every even-numbered row, leaving the odd ones clear — clean stripes that never break. For a banded look that groups by a key instead of every other row (all the “North” rows one shade, “South” the next), the logic gets more involved, but for the everyday readability stripe, ISEVEN(ROW()) is the one-line answer that the menu option can’t match.
Recipe 5: flag rows by text pattern with REGEXMATCH
When “contains this word” isn’t precise enough, REGEXMATCH brings full pattern matching into a formatting rule. To highlight any row whose notes column mentions “urgent” or “asap,” case-insensitively:
=REGEXMATCH($D2, "(?i)urgent|asap")The (?i) makes it ignore case, and the | means “either word.” Because it runs on $D2 with the column locked, applying the rule to A2:F lights up the entire row whenever the pattern hits. Swap in your own pattern — "^INV-\d+" for invoice IDs, "@gmail\.com$" for a specific email domain — and you can format on rules far more specific than a plain text match allows. Google Sheets uses the RE2 engine, and the RE2 syntax reference documents every pattern token.
Recipe 6: surface blanks and missing data
Data-quality rules catch the gaps before they cause problems downstream. To flag rows where a required field is empty — say a record has a name but no date in column C:
=AND($A2<>"", $C2="")This colors only the rows that have started (column A is filled) but are missing the date, so you’re not flagging blank trailing rows that haven’t been entered yet. It’s a small distinction that turns a noisy rule into a useful one. Pair it with data validation on the entry side and you catch bad data both ways — validation blocks it going in, conditional formatting flags whatever slipped through before.
Recipe 7: highlight the top performers
To color the highest few values in a column without sorting it — the top three sales figures, say — pair a comparison with the LARGE function, applied to B2:B:
=$B2>=LARGE($B$2:$B, 3)LARGE returns the third-biggest number in the column, so any cell at or above it lands in the top three — and the rule re-evaluates the moment a value changes, so the highlight follows the leaders as the data moves. Change the 3 to whatever cutoff you want, and swap LARGE for SMALL to flag the bottom N instead. It’s the live alternative to sorting and eyeballing the top of the list, and it leaves your data in its original order while still marking the standouts.
Build a library, not one-off rules
Seven recipes cover most of what conditional formatting gets asked to do: find duplicates, signal status, map magnitude, stripe for readability, match patterns, catch gaps, and surface the leaders. Each is a starting point — change the column, adjust the threshold, swap the pattern — rather than a fixed answer. When a rule won’t fire or colors the wrong cells, the fix is almost always in the references or the rule order, which is exactly what the guide to how custom-formula rules work walks through. Start with the recipe that matches today’s problem, get it working on real data, and add the next one when you meet it. A handful of reliable rules you actually understand beats a sheet full of formatting you’re afraid to touch.