Why conditional formatting isn't working
Causes and fixes
- The $ signs are in the wrong place
- The formula is written for a different row
- The Applies to range is split or too short
- Numbers are stored as text
- Another rule wins
1The $ signs are in the wrong place
To color whole rows based on column C, lock the column but not the row: =$C2>100. With =$C$2>100, every row checks C2, so all rows are colored or none are. With =C2>100, the column shifts across the row.
Fix
Edit the rule and use a $ only before the column letter.
2The formula is written for a different row
Excel reads a rule's formula from the top-left cell of the Applies to range. If the range starts at row 2 but the formula says =$C1>100, every row checks the row above it.
Fix
Make the formula match the first row of the range: Applies to $A$2:$F$100 with =$C2>100.
3The Applies to range is split or too short
Inserting, deleting or copying rows can split one rule into many with broken ranges, or leave new rows out.
Fix
In Manage Rules, delete the duplicates and set one Applies to range that covers all the rows. Formatting the data as an Excel table keeps the range growing with it.
4Numbers are stored as text
Rules such as "greater than 100" compare numbers. Excel treats text as larger than any number, so numbers stored as text give unexpected results.
Fix
Convert them with Convert to Number, then check the rule again.
5Another rule wins
When two rules set the same thing, such as the fill color, the rule higher in the list wins. A rule with Stop If True ticked blocks the rules below it.
Fix
In Manage Rules, use the arrow buttons to move rules up or down, and check the Stop If True boxes.
Still stuck?
- Test a formula in a spare column first:
=$C2>100filled down should show TRUE on exactly the rows you want colored. - A rule formula must return TRUE or FALSE (or a number, where 0 is FALSE).
- Rules that refer to another sheet work in current versions; in Excel 2007 and earlier, use a named range.