Why Your Google Sheet Is Slow: Cleaning Up Conditional Formatting
When a Google Sheet gets sluggish, people blame formulas, file size, or the browser. Conditional formatting rarely makes the suspect list — it's invisible, so nobody counts it. But every conditional formatting rule is a little program: it has to be evaluated against the cells in its range to decide what to color, and re-evaluated as the sheet changes. A workbook that has quietly accumulated hundreds of rules is doing a lot of extra work you never asked for.
How rules multiply without anyone noticing
Nobody sits down and writes 400 conditional formatting rules. They accumulate:
- Copy-paste multiplies rules. Paint format and Paste special > Format only create a new rule entry per paste instead of extending the original rule. Months of normal work can leave dozens of identical copies of the same rule (our copying guide explains why).
- Inserted rows and reorganisations fragment ranges. What was one rule over one block becomes several rules over slivers.
- Old rules never die. Rules whose ranges no longer contain data, or whose formatting is set to nothing visible, keep being evaluated anyway.
Which rules cost the most
- Whole-column ranges. A rule on
A:Acovers every row the sheet has — including tens of thousands of empty ones. Bounding the range to the data (A2:A2000) does the same visible job for a fraction of the work. - Custom formulas with volatile functions. A formula rule using
TODAY(),NOW(), orRAND()has to be re-evaluated far more often than a static condition — and it runs once per cell in its range. - Color scales over huge ranges. A gradient computes its minimum and maximum over the whole range to place each cell's color, so a full-column color scale re-scans the column to color anything.
- Duplicates. Ten identical rules do the identical evaluation ten times. This is the purest waste, and the most common.
The cleanup, step by step
1. Get the full inventory first
You can't clean up what you can't see. Go tab by tab through Format > Conditional formatting (including hidden tabs), or use a script or add-on to list every rule — our inventory guide covers all three routes. Note the duplicates and the whole-column ranges; they're your targets.
2. Back up before touching anything
Cleanup means deleting things, and the rules panel has no “restore.” At minimum, duplicate the file (File > Make a copy). Better: export the rules themselves so you can restore formatting logic without rolling back data.
3. Merge duplicates into multi-range rules
One rule can apply to several ranges — that's the clean form. For each group of identical rules, keep one, add the others' ranges to it (Apply to range accepts a comma-separated list), and delete the copies. One caution: if another rule sits between two identical rules in the priority order and overlaps their ranges, collapsing them can change which rule wins on the overlap — check the order before merging.
4. Delete the rules that do nothing
Rules with no visible formatting effect, and rules whose ranges hold no data anymore, are pure overhead. Delete them.
5. Bound the ranges
Replace whole-column ranges with bounded ones sized to your real data (leave headroom — A2:A5000 for a list that grows). This is often the single biggest win on large sheets.
6. Tame the custom formulas
Where a built-in condition can express the same test (“greater than,” “text contains,” “date is before”), prefer it over a custom formula. Where the custom formula must stay, make sure it isn't volatile without need.
Or let the cleanup find itself
All of the above works manually. On a big workbook, the finding is the hard part — which is what Format Rules Manager, a free Google Sheets add-on, automates: it inventories every rule on every tab, flags duplicate groups and do-nothing rules automatically, and offers one-click merge — but only when merging provably can't change how the sheet looks (gradient rules and groups with conflicting overlaps are flagged for manual review instead). Everything is undoable, and you can export all rules as JSON before you start as a backup. The result is the same sheet, the same colors, and a fraction of the rules.
Frequently asked questions
Will cleaning up conditional formatting change how my sheet looks?
Done right, no — merging identical rules and bounding empty ranges preserves the visible result. The one genuine risk is priority order: when rules overlap, the higher rule wins, so removing or reordering rules in an overlap needs care. That's why anything ambiguous should be reviewed by hand rather than bulk-merged.
How many rules does it take to slow a sheet down?
There's no magic number — cost is rules × range size × evaluation frequency. A handful of full-column volatile-formula rules can outweigh a hundred small static ones. Duplicates and whole-column ranges are the high-yield cleanup targets regardless of the total count.
My sheet is still slow after the cleanup — what else should I check?
The usual suspects, in order: volatile and chained formulas (especially IMPORTRANGE chains), very large used ranges full of empty formatted cells, and too much data on one tab. Conditional formatting is one layer of spreadsheet health — a broader review like our sibling add-on Sheet Audit Pack performs covers the rest.