← Back to Error Directory

Conditional Formatting Not Working — Complete Fix

No error message, no warning — just cells that should be highlighted and aren't. Here's how to find out why.

Table of Contents

1. Why This Fails Silently

Unlike a formula error, a conditional formatting rule that doesn't work simply does nothing — no dialog box, no error indicator, no visual clue at all beyond the absence of the formatting you expected. This makes it one of the harder categories of problems to diagnose, since there's no error message to search for or interpret; you're troubleshooting an absence rather than an explicit failure.

2. Interactive Demo: Absolute vs Relative Reference

This is the single most common cause — see exactly how an absolute reference breaks a rule meant to apply across a whole range.

3. Cause 1: Absolute References in Formula Rules

When building a formula-based conditional formatting rule, the reference needs to be relative (or mixed, depending on your intent) so it correctly shifts for each individual cell within the applied range. An absolute reference like $B$2 locks every single cell in the range to check that one exact same cell, rather than checking itself:

=$B$2>100      — every cell in the range checks only B2, which is almost never what you want
=B2>100         — each cell correctly checks its own row's value

The row number should almost always remain relative in a rule applied down a column, and vice versa for a rule applied across a row.

4. Cause 2: Rule Priority and Stop If True

When multiple conditional formatting rules apply to overlapping ranges, they're evaluated in priority order, top to bottom, as listed in Home → Conditional Formatting → Manage Rules. By default, once a rule's condition is met and its "Stop If True" checkbox is enabled, Excel stops evaluating any lower-priority rules for that cell entirely — meaning a rule further down the list might be technically correct but never gets a chance to apply because a higher-priority rule already claimed that cell.

5. Cause 3: Applies To Range Has a Gap

If rows or columns were inserted after the conditional formatting rule was originally created, the newly inserted cells sometimes fall outside the rule's "Applies To" range, depending on exactly where the insertion happened relative to the rule's boundary. Check Manage Rules and review the "Applies to" column for each rule to confirm it still covers your full intended range, including any recently added rows or columns.

6. Cause 4: Wrong Anchor Cell When Writing the Rule

When creating a formula-based rule, Excel uses whichever cell was active (selected) at the moment you opened the New Formatting Rule dialog as the reference point for interpreting your formula's relative and absolute references. If you selected the wrong starting cell before building the rule, even a correctly-written relative formula can end up offset from where you intended, checking the wrong cell relative to each row.

7. Reviewing the Rules Manager Systematically

  1. Select any cell within the range you expect to be formatted.
  2. Go to Home → Conditional Formatting → Manage Rules, and change the "Show formatting rules for" dropdown to This Worksheet to see every rule, not just ones tied to your current selection.
  3. Review each rule's formula, applied range, and Stop If True setting from top to bottom.
  4. Temporarily disable higher-priority rules one at a time (uncheck their checkbox) to isolate whether a priority conflict is masking a lower rule you expected to see.

8. Frequently Asked Questions

Why is my conditional formatting rule not applying to any cells?

This is most often caused by absolute cell references inside a formula-based rule that should be relative, causing the rule to check the same single cell repeatedly instead of adjusting for each cell in the applied range.

Why does one conditional formatting rule override another?

Rules are evaluated in the order listed in the Conditional Formatting Rules Manager, from top to bottom, and by default a rule stops evaluating lower-priority rules once a higher one applies, unless Stop If True is unchecked on that rule.

Why does conditional formatting work in some cells but not others in the same range?

This typically happens when the rule's Applies To range doesn't actually cover every cell you expect, often due to a gap left after inserting rows or columns that fell outside the original rule boundary.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.