How to See All Conditional Formatting Rules in Google Sheets
Conditional formatting is the most invisible layer of a spreadsheet. The colors are on screen, but the rules behind them — what triggers each color, in what order, over which ranges — are tucked away in a panel most people only open by accident. On a workbook with many tabs, nobody can honestly answer the question: how many conditional formatting rules does this file have, and what do they do? This guide shows every way to find out.
What the native panel shows — and what it hides
Open the rules panel for one tab
Select any cell and choose Format > Conditional formatting. The panel that opens on the right lists the conditional formatting rules of the current tab. Two things matter here:
- Selection filters the list. If you had a range selected, the panel highlights the rules that apply to that selection. To review the whole tab, click into a single cell first, and scroll the panel — the full list for the tab is there.
- The list is per-tab. There is no built-in view that spans tabs. To review a 20-tab workbook natively, you open the panel 20 times.
Read the priority order
The order of rules in the panel is not cosmetic — it is priority order. When two rules match the same cell, the rule higher in the list wins and the lower one is ignored for the properties they conflict on. A surprising number of “my formatting stopped working” mysteries are just a broader rule sitting above a narrower one. You can drag rules up and down in the panel to change priority — per tab, one rule at a time.
Decode custom formula rules
Rules of type Custom formula is are the powerful ones — and the hardest to read. The formula is evaluated for the top-left cell of the rule's range and then shifted across the range like a fill: relative references move, $-anchored references don't. So =$C2>1000 on range A2:F500 means “highlight the whole row when column C of that row exceeds 1000.” When reviewing a sheet, always read a custom formula together with its range — the same formula means something completely different on a different range.
Inventory every tab manually
For a small file, the honest manual method works fine:
- Open Format > Conditional formatting on the first tab.
- Note each rule: its range(s), its condition, its formatting, and its position in the list.
- Repeat for every tab — including hidden tabs, which you need to unhide first (right-click any tab name > View hidden sheets... via the View menu or the tab list).
The weaknesses are obvious: it's slow, it produces no record you can search or diff later, and hidden tabs and long rule lists make it easy to miss things.
Inventory every tab with Apps Script
If you're comfortable with a little scripting, Google Apps Script can read every rule programmatically. Open Extensions > Apps Script, paste this, and run it (it only reads — it changes nothing):
function listAllConditionalFormattingRules() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getSheets().forEach(function (sheet) {
const rules = sheet.getConditionalFormatRules();
Logger.log(sheet.getName() + ': ' + rules.length + ' rule(s)');
rules.forEach(function (rule, i) {
const ranges = rule.getRanges().map(function (r) { return r.getA1Notation(); }).join(', ');
const bc = rule.getBooleanCondition();
const kind = bc ? String(bc.getCriteriaType()) : 'GRADIENT (color scale)';
Logger.log(' #' + (i + 1) + ' [' + ranges + '] ' + kind);
});
});
}The execution log then shows every tab, every rule's position, its ranges, and its condition type. It's a real overview — but it's read-only text: you still can't act on what you see, the condition values and colors take more code to decode, and non-technical teammates won't run scripts.
Or get the inventory as a sidebar
You can do everything above by hand, and on a small sheet that's perfectly reasonable. For larger or unfamiliar files, Format Rules Manager, a free Google Sheets add-on, shows every rule on every tab in one sidebar — with color swatches, plain-language summaries, the ranges as clickable chips, and duplicates flagged automatically. It also lets you act on what you find: delete, reorder, copy, merge, or export rules, with undo. Either way, the goal is the same: know what formatting logic your spreadsheet is really running.
Frequently asked questions
Why do I see colors but no rule in the panel?
Most likely the color is plain (static) cell formatting, not conditional formatting — the panel only lists conditional rules. It can also happen that the rule lives on a range that doesn't include the cell you selected before opening the panel; click a single cell inside the colored area and reopen Format > Conditional formatting.
Do hidden tabs have conditional formatting too?
Yes. Hidden tabs keep their rules, and those rules keep working. Any honest inventory has to include hidden tabs — unhide them, or use a method (script or add-on) that reads them regardless of visibility.
How many conditional formatting rules is too many?
There's no fixed limit that matters in practice — the real problems are duplicates and overlaps. Dozens of distinct, intentional rules are fine. Hundreds of near-identical rules created by copy-paste are a maintenance and performance problem; see our guide on cleaning up conditional formatting.