Fix Conditional Formatting in Excel: A Practical Guide

To fix conditional formatting in Excel, open Home > Conditional Formatting > Manage Rules, show the rules for the affected cells, and check each rule’s range, formula, order, and formatting. Correct or remove the rule, then test it with a value that should trigger it. Before changing a workbook, save a copy if the rules are important or complex.

What You’ll Need

The workbook and the worksheet where the formatting looks wrong.
A rough idea of which cells should be formatted and what condition should trigger the formatting.
Microsoft Excel for Microsoft 365 on Windows for the steps below. Excel for Mac has similar ribbon commands; Excel for the web may have fewer options for editing complex rules.

Step-by-Step

Identify the affected cells and save a copy. Select a cell that should be colored, or one that is colored incorrectly. Note the intended range and condition—for example, highlight overdue dates in a status column, or color an entire row when its status is Late. Save the workbook, or use File > Save As to create a working copy. This gives you a way back if removing a rule changes more than expected.
Open the rules that apply to the selection. With an affected cell selected, choose Home > Conditional Formatting > Manage Rules. In the “Show formatting rules for” list, choose Current Selection to inspect rules affecting the selected cells. If you suspect a rule is applied elsewhere on the sheet, choose This Worksheet. Look at the “Applies to” column: it shows the cell range each rule can format. A rule may exist but not affect your cell if that range is wrong.
Check whether rules overlap or are in the wrong order. The Rules Manager lists rules that may apply to the same cells. Select a rule and use the up and down arrows to change its priority. If two rules set the same property—such as fill color—the higher-priority rule can determine what you see. When “Stop If True” is selected for a rule, lower-priority rules are not evaluated for a cell where that rule is true. Turn it off if later rules should also be considered. Avoid changing the order blindly: first identify which conditions overlap and which formatting each one is meant to provide.
Correct the “Applies to” range. Select the rule, edit the range in “Applies to,” and confirm that it covers the intended cells. For example, a rule meant to color A2:D100 should not accidentally apply only to A2, or to an unrelated column. When editing a range, use the range selector if available, or type the reference carefully. If you have added rows since creating the rule, extend the range to include them. Click Apply, then check both a cell inside the range and one just outside it to make sure the boundary is right.
Review the rule type, condition, or formula. Select the rule and choose Edit Rule. For built-in rules, check the selected comparison, value, date period, or text. Make sure the comparison matches your intention: “Greater Than” is not the same as “Greater Than or Equal To,” and a text rule may not match a different spelling or extra space. For a formula-based rule, check the formula and how its references move across the “Applies to” range. For example, with statuses in B2:B100 and rows of data in A2:D100, select A2:D100 when creating the rule and use =$B2="Late". This locks the status column but lets the row number change. If B5 contains Late, the formula evaluates TRUE for row 5 and applies the chosen format across A5:D5. If the formula used =$B$2="Late", every row would instead check only B2, which is often the cause of every row taking the same format.
Confirm the format itself. A rule can work correctly while its formatting is hard to see. In Edit Rule, choose Format and inspect the fill, font, border, and number format. A pale fill on a similarly colored background, or a font color matching the cell fill, can make the result appear missing. Pick a visible format and confirm the dialogs. Also check whether a manually applied fill or font style is making the result difficult to distinguish; conditional formatting can visually override some direct formatting while the condition is true.
Test the repaired rule, then remove only what is unnecessary. Enter or choose a value that should make the condition true, and check that the right cell or row changes. Then test a value that should not match. If the rule is obsolete or duplicated, select only that rule in Manage Rules and delete it. To remove all conditional formatting from a selected range, use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To remove all such rules from the sheet, use Clear Rules from Entire Sheet only after checking that no other area depends on them. Save once the result is correct.

Common Mistakes

Editing the wrong rule. A cell can be affected by several rules, so checking only the first one may miss the cause. In Manage Rules, set “Show formatting rules for” to This Worksheet when the selection view does not reveal the overlap.

Using absolute references everywhere. In formula rules, dollar signs control which references move as Excel evaluates other cells. Lock a column with a reference such as $B2 when every row should check its own value in column B. Locking both the column and row as $B$2 makes every row check the same cell.

Clearing too much. “Clear Rules from Entire Sheet” removes conditional formatting across the worksheet, not just the visible problem. Choose “Clear Rules from Selected Cells” when you want to limit the change, and confirm the selected range before using either option.

Forgetting blanks, spaces, or data types. A cell that looks like a number may be stored as text, and a status such as “Late ” with a trailing space may not match “Late.” Blank cells can also meet some comparison conditions unexpectedly. Check the actual cell contents and the rule’s treatment of blanks.

Expecting the displayed value to be the stored value. A date or percentage can look different because of number formatting. Review the underlying value and the rule condition, not just the appearance of the cell.

Tips

Use Manage Rules with the relevant cells selected, then switch to This Worksheet if rules seem to be missing. This helps distinguish a missing rule from one that is simply outside the current selection.
Give rules a simple purpose and a clearly defined range. Separate a rule that colors a whole row from one that highlights a single status cell if their conditions or formats differ.
Test the rule with one known match and one known non-match. For a date rule, try a date on either side of the threshold; for a status rule, compare an exact matching entry with a different status.
When copying cells, remember that Excel may copy conditional formatting as well as values and ordinary formatting. After pasting, revisit Manage Rules to check for duplicate rules or expanded ranges.
On Windows, Ctrl+Z can undo a recent edit to a rule. On Mac, use Command+Z. Do not rely on undo after many later changes; save a copy before substantial cleanup.

Troubleshooting

The rule is not listed for my selected cell. Open Manage Rules and change “Show formatting rules for” from Current Selection to This Worksheet. If the rule is still absent, check that you are on the correct worksheet and that the formatting is not ordinary cell formatting rather than conditional formatting.

The correct rule appears, but nothing changes. Confirm that the cell lies inside “Applies to,” then check the condition and the underlying value. For formula rules, verify that the formula starts with an equals sign and that the row reference corresponds to the first row of the applied range. Try a known value that should make the condition true.

Every row gets the same color. Inspect the formula’s dollar signs. If each row should check its own status in column B, a pattern like =$B2="Late" keeps the column fixed while allowing the row to change. Check that the applied range starts on row 2 if the formula refers to row 2.

The wrong color appears when rules overlap. In Manage Rules, compare their order, formats, and “Stop If True” settings. Temporarily move the intended rule higher, or adjust the conditions so they do not overlap when that is the desired behavior. Test the result before saving.

The rule works on desktop but not in Excel for the web. Open the workbook in the Excel desktop app to inspect or edit complex rules, particularly formula-based rules or overlapping rules. Excel for Mac uses similar Home > Conditional Formatting commands, though dialog layouts and some available controls can differ by version. If the desktop version shows the rule correctly, check that the web workbook has finished syncing and that you are viewing the same sheet and cells.

Frequently Asked Questions

How do I see all conditional formatting rules in Excel?
Choose Home > Conditional Formatting > Manage Rules. Set “Show formatting rules for” to This Worksheet to view rules across the active sheet, rather than only rules affecting the selected cells.

Why does conditional formatting apply to the wrong cells?
The “Applies to” range may be incorrect, or a formula reference may be locked or relative in the wrong way. Check both the range and the formula’s row and column references in Manage Rules.

How do I remove conditional formatting without deleting cell contents?
Select the cells, then choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes conditional formatting from those cells; it does not delete their values.

Can I fix a rule without deleting and recreating it?
Yes. In Manage Rules, select the rule and choose Edit Rule to adjust its condition, formula, or format. You can also edit “Applies to” or change the rule’s priority there.

Why does conditional formatting stop working when I add rows?
The new rows may be outside the rule’s “Applies to” range. Extend the range in Manage Rules, then test a new row with a value that should trigger the condition. If you frequently add data, consider keeping the rule range aligned with the full working area.

Can I use conditional formatting in Excel for Mac?
Yes. Excel for Mac supports conditional formatting and has similar commands under Home > Conditional Formatting. Some dialog details and controls differ by release; for complicated rule editing, use the Rules Manager available in your installed version.

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.