Conditional Formatting That Actually Helps (And When It Hurts)
By Mark Fulton · 2026-08-09 · 8 min read
Conditional formatting is the only formatting in a spreadsheet that carries information. Bold text is a style choice; a red cell is a claim. That's what makes it powerful and also what makes it easy to abuse — every rule you add is another thing the reader has to decode before they can trust the sheet.
The question I ask before adding any rule: what would a reader do differently because of this color? If the answer is "nothing, it just looks analytical," the rule doesn't go in.
Each rule type answers a different question
| Rule type | Question it answers | Works well when | Fails when |
|---|---|---|---|
| Data bars | "How big is this relative to the rest?" | Values are positive, same unit, similar order of magnitude | One outlier flattens every other bar to a stub |
| Color scales | "Where is this in the range?" | You're scanning a dense grid for hot and cold zones | The reader needs to compare two specific cells |
| Icon sets | "Which bucket is this in?" | There are real, defensible thresholds | Thresholds are just the default percentiles |
| Formula rules | "Does this row meet a condition?" | The condition is a business rule someone can state | The condition is "make it look nice" |
Data bars deserve more use than they get. They're the only one of the three that encodes magnitude with length, which is the visual channel people read most accurately. A column of data bars plus the numbers still visible is often a better chart than an actual chart, because it lives in the table and doesn't need a legend.
The catch is scaling. Excel draws bars relative to the minimum and maximum in the applied range, so one enormous value squashes everything else into slivers. When that happens, set the maximum manually in Manage Rules → Edit Rule rather than accepting Automatic, or split the outlier out.
The three-color scale readability trap
Red-yellow-green across an entire numeric block is the default reach, and it's usually the wrong one.
Two problems. First, the middle of a three-color scale is a low-saturation yellow that sits behind black text at poor contrast, so the rows in the middle of your distribution — usually most of them — become the hardest rows to read. You've made the typical case worse to highlight the extremes.
Second, a continuous gradient asks the eye to compare shades, and people are bad at that. A reader can tell dark red from pale green. They cannot reliably tell the 62nd percentile from the 71st by hue, which is exactly the comparison a smooth scale invites them to make.
When a scale is genuinely the right tool — a big grid where you want to spot regions, like months across the top and regions down the side — a two-color scale from white to one saturated color reads better than three. Intensity maps to magnitude in one direction, text stays legible at the light end, and there's no ambiguous middle.
Use it for exceptions, not decoration
The version of conditional formatting that earns its place is narrow: a rule that fires on a small minority of cells.
- Inventory below reorder point
- Invoices past due
- Variance beyond a tolerance you can name
- Blank cells in a column that shouldn't have any
- Duplicate values in an ID column
Each of those is a threshold someone can defend in a meeting. And because they fire rarely, a colored cell keeps its alarm value. Once more than roughly a fifth of your cells are colored, color has become the background rather than the signal.
For row-level rules, use a formula rule rather than coloring one cell. Select the whole table, choose Use a formula to determine which cells to format, and write the condition against the top-left cell of your selection with the column anchored and the row left relative — something like =$G2="Overdue" for a table starting at row 2. The $ pins the test to column G while the relative row lets it walk down the table.
A worked example: overdue invoices
That's abstract until you see it against real cells. Same idea, on an invoice table starting in row 2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Due date | Status | Amount |
| 2 | INV-1041 | 2026-07-14 | Overdue | 2,400.00 |
| 3 | INV-1042 | 2026-08-30 | Open | 1,150.00 |
| 4 | INV-1043 | 2026-06-02 | Paid | 890.00 |
| 5 | INV-1044 | 2026-07-28 | Overdue | 3,275.00 |
Select A2:D5 — the data, not the header, not the whole columns. Then Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format:
=$C2="Overdue"
Format: a pale fill, nothing more. Rows 2 and 5 light up across all four columns; rows 3 and 4 stay plain.
The part worth pausing on is what the selection does to the formula. It's written as if the active cell were A2, the top-left of what you selected, and every other cell gets its own version built by offset from there. Select A1:D5 by accident and the same formula tests C1, C2, C3, C4 instead — every row colored by the status of the row above it, which looks plausible enough that it can survive a review.
To drive it off the date instead of a status someone has to maintain, =AND($B2<TODAY(), $C2<>"Paid") does the same job. Both are conditions you can state out loud, which is the bar.
When conditional formatting goes wrong
The failures repeat:
- Blank cells matching a "less than" rule. A "less than 100" rule treats an empty cell as zero and colors it, so trailing empty rows become a block of alarm color under real data. Restrict the applied range to the rows that exist, or add a blank test to the condition.
- Whole-column ranges. Applying a rule to
D:Drather thanD2:D500is the main cause of that blank-cell problem, and it hands the workbook far more cells to evaluate on every recalculation. - The wrong kind of anchoring.
=$G$2="Overdue"with both parts locked evaluates one cell for the entire range, so the whole table colors or none of it does. If a rule is behaving all-or-nothing, this is almost always why. - Rules quietly overlapping. Two rules can both be true for one cell. In Excel, order in Manage Rules decides which wins, and Stop If True halts evaluation for cells that matched. If a rule in the list appears to do nothing, check what sits above it.
- Format Painter carrying rules with it. Painting a format from a conditionally formatted cell copies the rule too, with a range fragment nobody intended.
Excel and Google Sheets differ more here than anywhere else
Worth knowing before you promise someone a design:
- Google Sheets has no data bars and no icon sets in conditional formatting. It offers single-color rules and color scales, and that's the set. For in-cell bars, Sheets users reach for
SPARKLINE(A2, {"charttype","bar"})in a helper column — a formula rather than a format, so it occupies a cell of its own. - The menu path differs. Excel: Home → Conditional Formatting. Sheets: Format → Conditional formatting, which opens a side panel rather than a dialog.
- Formula rules are the common ground. Sheets calls it Custom formula is, and
=$C2="Overdue"works there as written above. If a design has to survive in both, build it from formula rules and fills only. - There's no Stop If True in Sheets. You reorder rules by dragging them in the panel, and position is the whole mechanism.
Don't encode meaning in color alone
This is the rule I see broken most often, and it's not a style preference — it's WCAG 1.4.1 Use of Color, a Level A success criterion. Color can't be the only visual means of conveying information.
Practical consequences for a spreadsheet:
- Red-green pairs are the worst possible choice for pass/fail, because red-green color vision deficiency is the most common form. Red and blue survive far better, and so does red versus a light neutral.
- Add a second channel. A status word in an adjacent column, an icon set alongside the fill, bold text, or a border. If the reader can get the answer with the color stripped out, you're fine.
- Check text contrast on fills. WCAG asks for 4.5:1 for normal-size text. Dark text on a saturated mid-tone fill routinely misses that, and the darkest cells in a color scale are the usual offenders.
- Assume it will be printed in grayscale or pasted into a deck as an image. Every color scale collapses to mush under that transformation; a data bar and an icon set both survive.
Housekeeping that keeps files sane
Copying and pasting rows propagates conditional formatting rules, and Excel doesn't merge them. A file that's been edited for a year can accumulate hundreds of near-identical rules with fragmented "Applies to" ranges, which slows the workbook and makes rules impossible to reason about.
Open Home → Conditional Formatting → Manage Rules, switch the scope to This Worksheet, and look at the list. If you see the same rule twenty times over ranges like $D$14:$D$14, delete them all and reapply one rule to the full column. Do this before you send the file, not after someone reports it's slow.
The short version
Reach for data bars when you want magnitude, a two-color scale when you want regions, an icon set only when the buckets are real, and a formula rule when there's a stated business condition. Skip it entirely when the honest answer to "what would change?" is nothing.
When SuperSheet proposes formatting for a file, conditional formatting is deliberately the most conservative part of the plan — it suggests rules for thresholds it can actually infer from your columns, and leaves the rest alone. You can always add more; nobody has ever removed the fourth color scale from a sheet and regretted it.
For the rest of the visual pass, see the seven rules for a professional-looking sheet. And if the underlying data is still messy, formatting is premature — start with the messy spreadsheet checklist.