Conditional Formatting in Excel Not Working?

If conditional formatting in Excel is not working, first open Home > Conditional Formatting > Manage Rules and check that the rule is enabled, its Applies to range includes the cells you expect, and its condition is actually true for the cell values. Then check the rule’s formatting, priority, and calculation settings; these are the most common reasons a rule appears to do nothing or highlights the wrong cells.

Check the rule and its Applies to range

Conditional formatting is attached to a range, not necessarily to the entire worksheet or even to the cells currently selected. A correct rule can appear broken simply because its range does not include the cells you are looking at. In Excel for Microsoft 365 on Windows, select a cell in the affected area, then choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, inspect the rules shown for the current selection. Change the display option to show rules for the whole worksheet if you need to find rules applied elsewhere.

Read the Applies to box carefully. For example, a rule intended to format a report in A2:D100 may have been left at A2:A20, or may have been extended accidentally to unrelated cells. Edit the range so it covers the intended cells, then select Apply and OK. When you create a rule, select the desired range first; doing so helps Excel set the initial range correctly, though it is still worth verifying it afterward.

If only one part of a range changes, compare the rule’s Applies to range with the cells that are not changing.
If the wrong cells change, inspect overlapping rules and whether the range starts in a different row or column than expected.
If a rule is missing from the current view, switch the Rules Manager to show rules for the whole worksheet.

Copied cells can bring conditional-formatting rules along with them, sometimes creating extra or overlapping rules. If the Rules Manager shows several near-identical entries, do not delete them blindly: first note their ranges and formats, then remove only the rule you have confirmed is unnecessary.

Confirm the condition matches the cell values

A rule only formats cells when its condition evaluates as true. A value that looks like a number may actually be text, and a date-looking entry may also be stored as text. This can make comparisons behave differently from what the display suggests. For example, a rule that highlights values greater than 100 may not treat the text string “120” like the numeric value 120. Check the underlying values by selecting cells and looking at the formula bar; changing the number format alone does not convert text into a number.

Dates are stored as numbers in Excel, with a date format controlling how they appear. A date comparison may fail if some entries are text or if the rule uses an unintended boundary. Similarly, a “Text that Contains” rule searches for text, while a numerical comparison evaluates values. Choose a rule type that matches the data and verify any spaces, punctuation, or spelling in text conditions.

For a quick test, temporarily choose a condition that should clearly be true for one known cell, or apply a simple fill color to that condition. If the test works, the original issue is likely the condition or the source data rather than the formatting mechanism. Check for leading or trailing spaces, inconsistent capitalization where relevant, blank cells, and formula errors if the rule depends on text or calculated results.

Check custom formulas and relative references

Formula-based rules are powerful but sensitive to cell references. Excel evaluates a custom formula relative to the top-left cell in the rule’s Applies to range. If a row-based rule starts at A2, write the formula as though Excel were evaluating A2, even if the formula checks a different column. Lock references with dollar signs only where needed: a dollar sign before a column letter keeps the column fixed, and one before a row number keeps the row fixed.

For example, with status values in D2:D20 and records in A2:D20, create a rule for the range A2:D20 using this formula:

=$D2="Late"

Choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, enter the formula, and set a format. Because the column D is locked but the row is not, Excel checks D2 for the first row, D3 for the next, and so on. If D5 contains the text Late, the rule formats A5:D5. If you instead used =$D$2="Late", every row would check D2, which is usually not the intended behavior for a row-by-row status rule.

Also confirm that the formula’s first reference matches the first row of the Applies to range. A formula written for row 2 but applied starting at row 3 can produce a one-row offset. When a formula rule fails, inspect the formula, the range’s starting cell, and the actual values in the referenced cells together.

Make sure the format is visible

A rule can be working while its effect is difficult to see. In the Rules Manager, select the rule and choose Edit Rule to inspect the format preview. Confirm that the fill, font, border, or number format is one you can distinguish from the cell’s normal appearance. A very light fill, a similar font color, or a border hidden by nearby borders can make a correct rule seem absent.

Conditional formatting changes how a value is displayed; it does not replace the cell’s contents. A red fill does not change a number, and a number format applied by a rule does not convert text to a number. The formula bar continues to show the underlying value or formula. If you expected the rule to alter the value itself, use a formula in the worksheet or another data-cleaning method instead.

Existing cell formatting can also confuse a visual check. Conditional formatting is a separate layer of formatting, so inspect the rule’s chosen style and test it on a cell with a plain background if necessary. You can use Home > Conditional Formatting > Clear Rules to remove rules from selected cells or the entire worksheet, but clearing rules is destructive to those rules. Save a copy first if you may need to restore them.

Review rule order and Stop If True

When multiple conditional-formatting rules apply to the same cell, their order and formats can affect the result. Open Home > Conditional Formatting > Manage Rules and inspect the priority list. Use the move-up and move-down controls to change priority when the intended format is being overridden. For applicable rules, Stop If True prevents lower-priority rules from being evaluated for that cell after the selected rule is true. This option is useful for deliberately exclusive categories, but it can block a later rule that you expected to contribute formatting.

Suppose one rule colors overdue rows red and another colors high-value rows blue. A row that meets both conditions may show a result affected by rule priority and by which formatting attributes each rule sets. If the outcome is unexpected, temporarily disable or move one rule to isolate the cause, then restore the intended order. Do not assume the bottom rule always wins; review the priority shown in the Rules Manager and which formatting attributes overlap.

Check calculation and formula results

If a conditional-formatting rule depends on formulas, confirm that the worksheet calculations are current. On Windows, choose Formulas > Calculation Options > Automatic if automatic calculation is appropriate for the workbook. In Manual mode, formula results may not update until calculation is triggered, so a rule based on those results can appear stale. Recalculate using the controls in Excel’s Formulas tab, or use the workbook’s established calculation process.

Also inspect the formula in the worksheet cell that the rule relies on. If it returns an error, blank, or a different value than expected, the formatting condition may not be met. A formula can display a formatted result that looks like a number while returning text, so check the formula and the actual result rather than relying only on appearance. Be cautious when changing calculation settings in a shared or complex workbook; a workbook may have been set to Manual intentionally to manage calculation time.

Look for copied rules, tables, and merged cells

Formatting rules can multiply when worksheets are copied, cells are pasted, or ranges are repeatedly extended. The Rules Manager is the best place to identify overlapping Applies to ranges and duplicates. If you use Format Painter or paste formatting, review the resulting rules afterward; copying a visual style can also carry conditional formatting in some workflows. When adding new rows to an Excel table, check whether the rule’s range includes those rows rather than assuming the rule expanded exactly as intended.

Merged cells can make range-based formatting harder to reason about because one displayed area may represent multiple underlying cells. If a rule behaves inconsistently around merged areas, test it on an unmerged range or simplify the layout. For a report that needs consistent row formatting, a regular rectangular range or an Excel table is generally easier to manage than a range with many merged cells. Keep the first cell of a formula rule aligned with the top-left cell of the range being formatted.

When to rebuild or simplify a rule

If the rule’s range, condition, and format are all difficult to verify, rebuilding it can be quicker than troubleshooting an inherited set of rules. Before deleting anything, record the current condition, formatting, and Applies to range, or save a separate copy of the workbook. Then remove only the confirmed problem rule and create a clean replacement over the intended range. Test it against one cell whose expected result is known, and then expand or confirm the full range.

Use a built-in rule, such as Highlight Cells Rules, when the condition is simple and the rule’s choices fit the task. Use a formula rule when formatting depends on multiple fields or an entire row. Keeping rules narrow, clearly named where possible through careful organization, and limited to the required range makes future edits easier. It also reduces the chance that a rule intended for one report section affects another.

Frequently Asked Questions

Why is Excel conditional formatting not changing when I edit a cell?
Check whether calculation is set to Automatic if the rule depends on formulas, and confirm that the edited cell is inside the rule’s Applies to range. Then verify that the new value actually meets the rule’s condition.

Why is conditional formatting highlighting the wrong row?
For a formula rule, the formula’s relative references are evaluated from the top-left cell of the Applies to range. Check that the formula’s row reference matches the range’s first row, and use dollar signs to lock only the columns or rows that should remain fixed.

Why does conditional formatting work in some cells but not others?
The rule may cover only part of the data, or different cells may contain different types of values, such as numbers in some rows and text in others. Review the Applies to range and compare the underlying values in affected and unaffected cells.

Does conditional formatting change the value in a cell?
No. It changes the cell’s appearance, such as its fill, font, border, or displayed number format. It does not replace the underlying value or formula.

How do I remove conditional formatting without deleting cell data?
Select the relevant cells and use Home > Conditional Formatting > Clear Rules, choosing the option for the selected cells. This removes formatting rules, not the cell contents, but it also removes those rules permanently unless you undo immediately or restore a saved copy.

Can conditional formatting be wrong even if the formula looks correct?
Yes. The formula may be correct for a different starting cell, the Applies to range may be offset, a higher-priority rule may affect the result, or the referenced cells may contain text or errors. Check all four before replacing the formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.