Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the CF rule error
Excel & Google SheetsWhat CF rule means
A conditional formatting rule using a custom formula fires based on whether the formula returns TRUE or a non-zero value for each cell in the applied range. If the formula always returns FALSE, uses an absolute reference that does not shift per row, or contains a logical error, the formatting never applies even though the rule exists.
Common causes
- The formula references the wrong 'anchor' cell — it must point to the top-left cell of the applied range (e.g., A1, not A2) or the offset will be wrong for all rows.
- Dollar signs ($) are used incorrectly: $A$1 locks both row and column so every row evaluates against the same single cell instead of shifting down.
- The formula evaluates to a number or a text string rather than TRUE/FALSE, and the application does not interpret non-zero as TRUE in CF context.
- The applied range was entered incorrectly (wrong sheet, skipped rows), so the rule applies to cells other than those intended.
- The formula uses a function not supported in CF rules (e.g., INDIRECT with a volatile dependency, or an array function in Excel CF).
Example fix
Broken
=$A$1="Overdue"Fixed
=$A1="Overdue"Removed the $ from the row number so the reference shifts from A1 to A2, A3, etc. as the rule evaluates each row in the applied range.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Open the CF rule editor and confirm the 'Applies to' range starts at the row your formula references (e.g., =$A$1:$C$100, formula starts with A1).
- 2Lock only the column, not the row, for row-relative comparisons: =$A1>100 ($ on A, no $ on row number) so the rule shifts down each row.
- 3Make sure the formula resolves to TRUE or FALSE. Replace =A1="Yes" with =EXACT(A1,"Yes") for case-sensitive checks, or =ISNUMBER(SEARCH("Yes",A1)) for partial matches.
- 4Delete and recreate the rule from scratch — editing existing CF rules sometimes carries over stale range references.
- 5In Google Sheets, ensure the custom formula starts with = and does not reference a named range defined on a different sheet without the sheet qualifier.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every CF rule and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
My CF rule worked on row 1 but not on any other row — what is wrong?
The formula almost certainly uses a fully absolute reference like $A$1. Remove the $ from the row number so it becomes $A1 and the rule evaluates the correct row for each cell.
Can I use VLOOKUP or INDEX/MATCH inside a conditional formatting formula?
Yes. Both work in CF formulas. Wrap them in ISNUMBER or ISBLANK to convert the result to TRUE/FALSE: =ISNUMBER(MATCH(A1,LookupRange,0)).
Why does my CF rule apply to the wrong cells after I copy-paste rows?
Copy-pasting rows can expand or corrupt the 'Applies to' range in CF. Open the CF manager, check the range, and correct it manually.