Why Your Excel Conditional Formatting Rules Stop Working

You built a conditional formatting rule that worked fine last week, and now half your cells aren't highlighting anymore. Nothing in the sheet looks different. This trips up more people than almost any other Excel formatting feature, and it's almost never a bug.

The most common cause: copy and paste

Conditional formatting rules are tied to specific cell ranges. When you copy a formatted cell and paste it somewhere else, Excel often creates a brand new rule instead of extending the range of the old one. Do this a few times across a big sheet and you end up with a dozen overlapping rules, some referencing ranges that no longer make sense, some referencing formulas that have shifted.

To see what's actually going on, go to Home, then Conditional Formatting, then Manage Rules, and change the dropdown at the top from "Current Selection" to "This Worksheet." You'll usually find far more rules than you expected, several of which do nothing anymore.

Formula based rules and relative references

If your rule uses a formula, like =$B2>100, the dollar sign placement matters more than it looks. A formula rule copied to a new range keeps its relative and absolute references exactly as written, just shifted. If the row or column references weren't locked correctly to begin with, the rule slowly drifts as it gets applied to more rows, and cells stop matching the condition you thought you set.

Rule order and "Stop If True"

Excel checks rules in order and stops at the first one that matches, if that rule has "Stop If True" checked. An older, broader rule sitting above a newer, more specific one will quietly block the new one from ever firing. In Manage Rules you can drag rules up and down to fix the order, or clear that checkbox if you don't actually need it.

The fastest fix

Rather than hunting through everything, select the whole range you care about, clear all conditional formatting from it (Conditional Formatting, Clear Rules, Selected Cells), and rebuild one clean rule from scratch. It takes two minutes and it's usually faster than figuring out which of ten overlapping rules is misbehaving.

If you're maintaining a shared workbook other people edit, check Manage Rules every so often anyway. Rules pile up quietly, and nobody notices until the formatting looks wrong.