Conditional Formatting Not Working in Excel? Fix It

When conditional formatting is not working in Excel, the rule itself is rarely broken. Almost always it points at the wrong cells, compares numbers that are really text, or loses to another rule. This guide walks through each cause in the order you should check it, with the fix for each, so you can get your highlights back in a few minutes.

Quick checklist: why conditional formatting is not working#

Start with the symptom you see, then jump to the matching fix:

If you're new to rules, the beginner's guide to conditional formatting explains the built-in options first.

Check every rule in Manage Rules#

The Rules Manager shows each rule, its formula, its format and the cells it applies to. Open it with Home > Conditional Formatting > Manage Rules (the same path on Windows and Mac).

1. At the top, change Show formatting rules for from Current Selection to This Worksheet. Otherwise you only see rules for the cells you selected, and a rule that covers other cells looks missing.
2. Read the Applies to column. It should cover all your data, such as =$A$2:$D$200.
3. Look for duplicates. Several near-identical rules usually mean rows were copied and pasted.

Rules run from top to bottom. When two true rules set the same thing, such as fill colour, the higher rule wins. When they set different things, such as one bold font and one red fill, both apply. Tick Stop If True on a rule to stop the rules below it once it matches. Use the up and down arrows to change the order.

Wrong $ signs in a formula rule#

A formula rule is written as if for one cell: the top-left cell of the Applies to range. Excel then shifts the references for every other cell, exactly as if you filled the formula across and down. The $ signs decide what shifts.

Say your data is in A2:D10 and you want to colour every row where the amount in column C is over 100:

The last one is a classic. It happens when your Applies to range starts in row 2 but you wrote the formula for row 3, or you selected the range from the bottom up. Always write the formula for the first row of the range.

Excel adds full $$ references when you click a cell while writing a rule. Press F4 on Windows (⌘T on Mac) to cycle through $C$2, C$2, $C2 and C2. The guide to mixed references like $C2 explains why this works, and how to highlight a whole row uses the same pattern.

Numbers or dates stored as text#

A number stored as text looks like a number but behaves like a word. It often comes from imports and copied web data, and usually sits on the left of the cell with a small green triangle.

This breaks comparisons in a surprising way. In a formula, Excel ranks any text above any number. So if B2 holds the text "80", the rule =$B2>100 returns TRUE and highlights it, while the real number 145 is highlighted correctly. Other rules fail the other way: Top 10, Above Average, data bars and color scales skip text cells, so those cells are never formatted.

You have two fixes:

• Convert the data. Select the column, then use the warning triangle's Convert to Number, or Data > Text to Columns > Finish. The guide to fixing numbers stored as text shows every method.
• Convert inside the rule. Write =VALUE($B2)>100. VALUE turns text like "80" into the number 80 and leaves real numbers unchanged. The VALUE function guide covers it.

Dates have the same problem. A rule like =$C2<DATE(2026,11,2) can't work on a date typed as text. Convert it with DATEVALUE, or Text to Columns with the right date order.

The formula is saved as text or returns an error#

A formula rule only formats a cell when the formula returns TRUE (or a non-zero number). Three things stop that:

1. No equals sign. If you type $C2>100 without =, Excel saves it as the text ="$C2>100", which never returns TRUE. Open the rule with Edit Rule and check that it starts with = and has no extra quotes.
2. The formula returns an error. A rule that gives #N/A or #VALUE! counts as FALSE, so nothing is formatted. This is common with lookups in rules. Wrap the part that can fail in IFERROR.
3. Text doesn't match exactly. =$D2="Done" fails for "Done " with a trailing space. Use =TRIM($D2)="Done" if your data has stray spaces. Text comparisons ignore case, so "done" and "DONE" both match.

One more trap: while editing the rule box, the arrow keys move around the sheet and insert cell references. Press F2 first to move the cursor inside the formula instead.

The Applies to range broke after copying or inserting rows#

Copying, pasting and dragging cells copies their rules too. Over time, one rule can turn into many small rules with ranges like =$A$2:$D$7,$A$9:$D$12. New rows that fall in a gap get no formatting.

To clean up:

1. In Manage Rules, with This Worksheet selected, delete the duplicate rules.
2. Edit the Applies to box of the one rule you keep, for example =$A$2:$D$500.
3. Click Apply, then OK.

To avoid the problem, use Paste Special > Values (Windows: Ctrl+Alt+V; Mac: ⌃⌘V) when you move data into a formatted sheet, or turn the range into an Excel table so rules grow with new rows.

Test the formula in a cell first#

The fastest way to debug a formula rule is to type the same formula into a spare cell next to the first row of data, then fill it down. Each cell shows TRUE or FALSE, which tells you exactly which rows the rule will format.

If row 2 shows TRUE when it shouldn't, the problem is the logic or the data, not the rule. Fix it in the cell, then copy the working formula back into the rule. The conditional formatting formula guide has more ready-made rules to test this way.

Practise it#

Try the exercise on this page: see why a text "80" counts as over the limit, then fix it with VALUE. Next, practise conditional formatting in a live spreadsheet, get the $ signs right with absolute and mixed references, and learn to spot fake numbers in the text and VALUE lesson. Each one checks your answer instantly.