“Conditional formatting not working” is not one problem. It is at least six, and each one has a different fix. Find your symptom in the table below, go to that check number, and skip the rest.
Verification note: These steps describe standard Google Sheets behaviour. Sheets is a rolling service, so menu labels and behaviour can change, and these checks have not yet been verified against a live Google Sheet for this site. No sample Sheet is published yet. Test each fix on your own sheet and check at least three rows (first, middle, last) before trusting it across a range.
| Symptom | Most likely check |
|---|---|
| Nothing is formatted at all, even though the rule looks right | Check 1 (range) or Check 3 ($ anchors) |
| The wrong cells are formatted, or the formatting shifts down the column | Check 3 ($ anchors) |
| Two rules conflict, or the wrong rule wins | Check 2 (rule order) |
| The rule works on one tab but not when it references another tab | Check 4 (INDIRECT for cross-sheet references) |
| Formatting appears on cells you never formatted, or old formatting reappears after pasting | Check 5 (stray pasted rules) |
| Formatting is off by one row, especially after a QUERY | Check 6 (QUERY header offset) |
| Formatting disappears or changes when you download as Excel | Edge case: Excel export (see the end) |
| You used a custom script function in the rule and nothing happens | Edge case: script functions are ignored (see the end) |
Work through the checks in order if you are not sure which symptom matches — the order is deliberate: range and order are the most common causes, and the later checks are the ones general guides miss.
Open Format → Conditional formatting. In the sidebar, look at the “Apply to range” box for your rule. That box, not your memory of what you selected, is the truth.
Common ways this goes wrong: you selected a range, then inserted or deleted rows, and the range did not follow the way you expected; you have two rules and you are editing the wrong one; or the range is a single cell (for example, A2) when you meant a column (for example, A2:A100).
Fix: correct the “Apply to range” box to the exact range you want, then check one cell that should be formatted and one cell that should not be. If both are now correct, you are done — do not change anything else.
If you have more than one rule on the same range, Sheets evaluates them in the order shown in the Conditional formatting sidebar, and the first rule whose condition is true wins for any overlapping formatting. A later rule does not “add to” an earlier rule's formatting on the same property — it is shadowed by it.
Worked example. Two rules on A2:A100: Rule 1, “Value is greater than 100” → fill green; Rule 2, “Value is greater than 50” → fill yellow. A value of 150 satisfies both rules, but it will be green, not yellow, because Rule 1 comes first and wins. Reverse the order and 150 becomes yellow, which is almost certainly not what you intended.
Fix: drag the rules in the sidebar into the order you actually mean, most specific first, and test with a value that satisfies more than one rule.
Conditional-formatting custom formulas use relative references by default, and relative references shift as Sheets applies the formula down (and across) the range. Take a rule applied to A2:A100 with the custom formula:
=G2>100When Sheets applies that formula to row 3, it becomes G3>100; to row 4, G4>100; and so on. That is correct if you want each row judged by its own G value. But if you meant “every row in A2:A100 should be judged by the value in G2” (a single threshold cell), the formula must lock both the column and the row:
=$G$2>100| Formula as written in the rule | What happens down the range | When to use it |
|---|---|---|
G2>100 | Both column and row shift | Each cell judged by its own row's G value — risky if the range is wider than one column. |
$G2>100 | Column locked to G; row shifts | Each row judged by its own row's G value — the usual case for a row-based rule across several columns. |
$G$2>100 | Both locked; every cell judged by G2 | Every cell judged by one threshold cell. |
The most common mistake is writing G2 when you meant $G2, and then watching the formatting shift or disappear as it moves down the range — or writing $G$2 when you meant $G2, and watching every row judged by the first row's value.
Fix: decide, before you write the formula, whether the reference should shift by row, by column, by neither, or by both — then add the $ signs to match that decision, and test on at least three rows (the first, a middle row, and the last row of the range), not just the first row.
A conditional-formatting custom formula cannot reference another tab directly. If your rule tries to compare a cell on the current tab with a cell on another tab by writing the other tab's name directly in the formula, it will not work as expected.
The working form uses INDIRECT to build the reference as text. In outline (adapt the tab name and cell to your sheet):
=INDIRECT("OtherTab!A2")>100That is, instead of writing the cross-sheet reference directly, you pass it to INDIRECT as a text string, and INDIRECT turns it into a reference Sheets can use inside conditional formatting.
Limitation to note: INDIRECT is volatile and text-based: if you rename the other tab, the text string does not update automatically, and the rule can break silently. If your rule does not need another tab at all, do not use INDIRECT — it adds fragility for no benefit.
Copying and pasting cells in Sheets can also copy their conditional-formatting rules. The result is formatting on cells you never formatted, old formatting reappearing after you thought you deleted it, and a sidebar full of rules you do not recognise.
Fix:
If your formatted range is fed by a QUERY, and the formatting looks shifted by exactly one row (the header row is formatted, or the first data row is not), the cause is usually the QUERY's header setting.
A QUERY with headers returns a header row followed by data rows. If your conditional-formatting range starts at the header row but your rule was written as if it starts at the first data row — or the other way round — every row's formatting is off by one.
Fix: check the QUERY's headers argument and check the formatting range's first row against the actual first data row, not the header row. Adjust the range (or the rule's anchor row) so that the first formatted row is the first data row. Test with a value you can recognise in the first data row and in the header row, so an off-by-one error is visible immediately.
If you wrote a custom function in Apps Script and used it inside a conditional-formatting custom formula, Sheets will not run it there — the rule simply does not apply, with no error message to tell you why. The fix is to move the logic into a helper column (compute the value with the script function in a normal cell, then base the formatting rule on that helper column).
Google-specific functions used inside conditional-formatting rules may not survive a download as Excel (.xlsx), and formatting driven by those functions can change or disappear in the exported file. If your sheet looks right in Sheets but wrong after export, check whether the rule uses a Google-specific function, and test the exported file rather than assuming the Sheets view is what the recipient will see.