Conditional formatting formula not working: how to fix it

Step 1: test the rule in a cell

A formula rule colors a cell when the formula returns TRUE for that cell. You cannot see that result inside the dialog, so put it in the sheet. The invoice list has a threshold of 1,000 in G2, and the goal is to highlight invoices above it. The rule, written for row 2, is =$C2>$G$2.

Paste the formula into a spare cell in row 2 and fill it down to row 9. You now see TRUE or FALSE for every row, exactly as the rule sees them. Any row where the answer is wrong points straight at one of the causes below. Delete the test cells when you are done.

1. Home > Conditional Formatting > Manage Rules.
2. Set "Show formatting rules for" to This Worksheet, so rules for other ranges show too.
3. Select the rule, click Edit Rule, and copy the formula text.
4. Press Esc, click a spare cell in the first row of the Applies to range (row 2 here), paste and press Enter, then fill down.

The rule is written for the wrong row

Excel writes the rule for the top-left cell of Applies to, then shifts it for every other cell, like copying a formula. If Applies to is A2:F9 but the formula says row 3, every row checks the row below it. This happens when you select the range from the bottom up or start on the header row.

With =$C3>$G$2 on A2:F9, row 6 (Wren & Co, a draft with no amount) checks C7, Fernhill's 2,300, and lights up. Rewrite the formula for the first row of the range, row 2.

$ signs: $C2 vs C2 vs $G$2

Lock the column you test with $ and leave the row free: $C2. Without the $, the cells in columns B to F test the columns to their right, so only part of the row colors. With $C$2, every row tests C2, so all rows or none change.

A threshold or input cell needs both $: $G$2. If you write =$C2>G2, row 5 compares Copper Kettle's 640 with G5, an empty cell that counts as 0, and every row with an amount lights up. The highlight-row guide explains the $ patterns in detail.

Numbers stored as text

Pike & Sons and Lumen Labs have amounts pasted in as text. In a comparison like >, Excel ranks any text above every number. So the rule says TRUE for Lumen's 800 as well as Pike's 1,500, while an equals test such as =$C3=1500 says FALSE. COUNTA(C2:C9)-COUNT(C2:C9) returns 2, the number of text cells hiding in the column.

The same text also splits your counts: COUNTIF(C2:C9,">1000") skips text and gives 2, while a direct comparison counted with SUMPRODUCT gives 4. Convert the column to real numbers (select it, click the warning icon that appears next to the selection, Convert to Number). If you cannot change the data, coerce inside the rule: =--$C4>$G$2 returns FALSE for the 800.

Blanks, stray spaces and case

An empty cell counts as 0 in a comparison, so a rule like =$C2<500 for small invoices also colors Wren & Co, which has no amount. The same thing happens with dates: =$E2<TODAY() marks rows with no due date as overdue. Add a not-blank test with AND.

Text must match exactly apart from case. Copper Kettle's status is "Paid " with a trailing space, so =$D5="Paid" is FALSE; =TRIM($D5)="Paid" is TRUE. Case is ignored, so =$D9="Open" matches Halden's "open". If case should matter, use EXACT($D9,"Open"), which returns FALSE.

The formula returns an error

If the rule formula returns an error for a cell, the format is not applied there, and Excel gives no warning. A rule for poorly paid invoices, =$F2/$C2<0.5, works for Fernhill (500 of 2,300, TRUE) but divides by zero on Ashby Motors' void invoice with an amount of 0.

Wrap the test in IFERROR with FALSE as the fallback, or check the amount first with AND. =IFERROR($F8/$C8<0.5,FALSE) returns FALSE for that row instead of an error.

Rule order, Stop If True and Applies to

When the formula is right in the test cells but the color still does not show, look at the rules themselves. Rules are checked from the top of the Manage Rules list. If two rules set the same format, such as fill, for the same cell, the higher one wins. Rules that set different formats, such as a fill and bold text, both apply. A rule with Stop If True ticked stops the rules below it for cells where it is TRUE.

Applies to also changes over time. Copying, pasting and inserting rows can split one rule into several with odd ranges such as =$A$2:$F$5,$A$7:$F$9, and rows added below the range may not be covered. Reset it to one range, and turn the data into a table (Ctrl+T) so new rows pick up the formatting.

1. Home > Conditional Formatting > Manage Rules, with This Worksheet selected.
2. Use the up and down arrows to put the most important rule on top.
3. Clear Stop If True on any rule that should not block the ones below.
4. Delete duplicate rules and set Applies to to one range, such as =$A$2:$F$500.
5. Click Apply and check the colors against your test cells.

Typing traps in the rule box

Three habits of the rule box catch everyone. Start the formula with =; if you leave it out, Excel stores your rule as a text string in quotes and it never fires. Clicking a cell while writing the rule inserts a fully locked reference like $C$2, so remove the $ before the row number. And the arrow keys insert cell references instead of moving the cursor; press F2 to switch to Edit mode first.

Combined tests work like any formula. The overdue rule for this sheet is =AND($D2<>"Paid",$E2<TODAY()). Tested in a cell with 4 Nov 2026 in place of TODAY, it is TRUE for Fernhill Clinic, due 1 Oct.