Skip to content
ArumSoft Learning
KO
    FormattingExcel

    Why conditional formatting isn't working

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerOpen Home > Conditional Formatting > Manage Rules, choose This Worksheet, and check each rule's formula and Applies to range. The formula must be written for the first cell of that range.

    Causes and fixes

    1. The $ signs are in the wrong place
    2. The formula is written for a different row
    3. The Applies to range is split or too short
    4. Numbers are stored as text
    5. 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>100 filled 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.