Conditional Formatting Not Working? Here's Why

Conditional formatting almost never fails silently for no reason — it's nearly always one of four causes: wrong $ anchoring in a formula rule, a higher-priority rule stopping evaluation before yours runs, the rule's "applies to" range not covering the cells you're looking at, or the cell value being text when your rule expects a number.

Why is my $ anchoring making the rule check the wrong cells?

A formula rule is written for the top-left cell of the selection and copied across the rest of the range exactly like a dragged formula — a reference without a $ shifts with every cell, which silently points the rule at the wrong data as it moves across the range.

Selection: A2:E100, formula written for A2

=$D2="Complete"   correct for whole-row logic:
                  column locked, row free to shift down

=D2="Complete"    wrong for whole-row logic:
                  as the rule copies right from column A to E,
                  it checks E2, F2, G2... not D2 anymore

Fix it by matching the anchoring to what should and shouldn't move: lock the column when the check should repeat down every row, lock the row when it should repeat across every column, and lock both only when every cell should check one fixed reference.

Why does a rule stop working when I add another one?

Rules evaluate top to bottom in priority order, and by default each rule has "Stop If True" implicitly handled by conflict resolution — a higher-priority rule that matches can prevent a lower-priority rule's formatting from displaying, even though both rules are technically "true."

Conditional Formatting → Manage Rules

Check, top to bottom:
1. Which rule is listed first (highest priority)?
2. Does any rule above yours have "Stop If True" checked?
3. Are the rules' formats actually compatible
   (e.g. both set a fill color — the higher one wins)?

Reorder with the ↑ / ↓ arrows in Manage Rules,
or drag rules to change priority

This is the classic symptom of "my rule is definitely TRUE but nothing shows" — the rule is firing correctly, but a rule above it in the list is claiming the formatting first.

Why does my rule work on some cells but not others in the same column?

Check the rule's "Applies to" range in Manage Rules — it's easy to build a rule against a selection that doesn't actually match the range you're troubleshooting, especially after inserting rows or copy-pasting cells.

Conditional Formatting → Manage Rules
→ Select "This Worksheet" from the dropdown to see every rule
→ Check the "Applies to" column for each rule

Common causes of a mismatched range:
- Rows were inserted below the original applies-to range
- Cells were copied without their conditional formatting
- The rule was originally built on a smaller selection
  than the data now covers

Fix it by editing the "Applies to" field directly in Manage Rules, or by re-copying the formatting: select a cell that already has the rule, use Format Painter, and paint it across the full current range.

Why doesn't my number-based rule match values that look like numbers?

Data pulled from a CSV, a database export, or Power Query often lands as text even though it displays as digits — comparisons like >100 silently fail against text because Excel doesn't coerce text to a number for that comparison.

Test it: put =ISNUMBER(A2) in a spare cell.
FALSE means the value is text, not a number, even if
it's right-aligned and looks numeric.

Fix options:
1. Select the range → Data tab → Text to Columns → Finish
   (forces a re-parse that converts text-numbers to numbers)
2. Multiply by 1 in a helper column: =A2*1
3. Use VALUE() inside the rule formula: =VALUE(A2)>100

Numbers stored as text usually show a small green triangle in the top-left corner of the cell and left-align by default (real numbers right-align) — that visual cue is the fastest way to confirm this is the cause before troubleshooting anything else.

What's the fastest way to diagnose which of these it is?

Test the rule's formula in a spare cell before touching the rule dialog again — this isolates whether the logic itself is wrong or the rule setup around it is the problem.

Symptom	Likely cause
Nothing formats anywhere	Formula returns FALSE for every cell — test it in a spare cell
Works on row 2, wrong after that	Missing $ anchoring
Formula tests TRUE but no color shows	Higher-priority rule with Stop If True
Works for old rows, not new ones	"Applies to" range doesn't cover new rows
Number comparisons all fail	Value is text, not a real number

See writing custom conditional formatting formulas for the $ anchoring rules in full, and VLOOKUP not working for the same text-vs-number trap in a different function.

What other conditional formatting mistakes cause silent failures?

Beyond the four main causes, a handful of setup mistakes cause rules to appear broken when they're actually configured incorrectly from the start.

• Sheet or range protection: a protected sheet can block formatting changes even when the rule fires correctly
• Formula built for the wrong active cell: a multi-cell selection uses whichever cell was active (usually top-left, but not always) as the formula's reference point
• Copy-paste without "Keep Source Formatting": pasting values-only or a plain paste can strip conditional formatting from the destination cells
• Filtering hides rather than removes formatted rows: a filter can make it look like a rule stopped working when the matching rows are simply hidden