
You set up a rule to highlight overdue dates in red, or to colour high sales green, and nothing happens. The cells stay plain, even though the values clearly meet the condition. When excel conditional formatting not working leaves your sheet looking flat and unreadable, the rule itself is rarely broken beyond repair — usually one small detail is stopping Excel from applying it.
The good news is that most causes are easy to check. A rule can point at the wrong range, sit below another rule that overrides it, use a formula that refers to the wrong cell, or wait on a calculation setting that is turned off. This guide works through those causes in order, from the simplest check to the deeper fixes. Nothing here deletes your data, and every fix can be undone by removing the rule you changed.
Table of Contents
- Why This Error Happens
- Quick Checks
- Diagnosing the Problem
- Fix 1: Check the Rule Applies to the Right Range
- Fix 2: Open Manage Rules and Check the Rule Order
- Fix 3: Fix the Formula and the Cell References
- Fix 4: Turn Automatic Calculation Back On
- Fix 5: Fix Numbers Stored as Text and Extra Spaces
- Fix 6: Clear Duplicate and Conflicting Rules
- Fix 7: Check Sheet Protection and Shared Workbook Limits
- Fix 8: Fix Rules Inside Excel Tables and Merged Cells
- Fix 9: Check the Format Is Visible and the Rule Is Not Overridden
- Fix 10: Test the Rule in a New Workbook
- Fix 11: Update Excel and Repair the Office Installation
- What to Avoid
- Frequently Asked Questions
- Conclusion
- Helpful Official Resources
Why This Error Happens
Conditional formatting is a set of rules that Excel checks against your cells. Each rule has three parts: the range it watches, the condition it tests, and the format it applies. If any one of those three parts is wrong, the rule runs silently and changes nothing. These are the reasons that happens most often.
1. The Rule Watches the Wrong Range
Every rule carries an Applies to range, and Excel only formats cells inside that range. If you select one range, create the rule, and then paste data elsewhere, the rule stays with the original range. Rows inserted above the range, or data typed just below it, fall outside the rule and never change colour. This is the most common cause of excel conditional formatting not working, and it is also the easiest to miss, because the rule looks correct when you open the rule list.
2. Another Rule Runs First and Overrides It
Excel applies rules from the top of the list down, and the first rule that matches a cell usually wins. If a higher rule paints the cell white, or a higher rule has Stop If True turned on, the rules below it never get a chance. A sheet that has collected rules over months can hold several rules fighting over the same cells. The rule you want may be working perfectly — it is simply losing the contest to a rule above it.
3. The Formula Refers to the Wrong Cell
Formula-based rules use relative references, which means Excel shifts the formula for each cell in the range. If the formula is written for the wrong starting cell, or if a dollar sign locks a row or column that should move, the test checks the wrong value in every cell. A single misplaced $ sign can make a rule test one fixed cell instead of each cell in turn, so nothing highlights, or the wrong cells highlight.
4. Calculation Is Set to Manual
Excel only re-checks conditional formatting rules when it recalculates the sheet. If Calculation Options is set to Manual, perhaps after working with a large workbook, Excel stops recalculating on its own. Your values change, but the rules are never re-tested, so the colours stay frozen at whatever they were when the sheet last calculated. Pressing a key or typing a value does not help until calculation runs again.
5. Numbers Are Stored as Text
A cell that looks like a number can actually hold text. Numbers pasted from a website, imported from another system, or typed with a leading apostrophe are stored as text, and text does not compare equal to a number. A rule that tests for values greater than 100 will skip a text value of 200, because Excel sees characters, not a number. Extra spaces at the start or end of a value cause the same silent failure.
6. The Sheet Is Protected or the Workbook Is Shared
A protected sheet can block changes to formatting, and some workbook sharing modes limit what conditional formatting can do. If the sheet is protected with formatting locked, Excel may refuse to apply or edit rules at all. Older shared-workbook features also restrict conditional formatting. In both cases the rule exists and looks right, but Excel is not allowed to carry it out, so excel conditional formatting not working persists until the protection or sharing setting is dealt with.
Quick Checks
Two minutes before the deep fixes.
- Click a cell that should be highlighted and open Manage Rules — is there a rule whose range covers that cell?
- Change the value in a highlighted cell and press Enter — does the colour update, or stay frozen?
- Check the Formulas tab and look at Calculation Options — is it set to Automatic?
- Click a value cell and check the formula bar — does the value have a leading apostrophe or extra spaces?
- Look at the sheet tab — does the sheet show signs of protection, such as greyed-out formatting buttons?
- Think about when it started — did excel conditional formatting not working begin after pasting data, inserting rows, or protecting the sheet?
Diagnosing the Problem
| Symptom | Cause | Go to Fix |
|---|---|---|
| Rule works on some cells but not others | Rule range does not cover all the cells | Fix 1 |
| Rule never applies at all | A higher rule overrides it | Fix 2 |
| Wrong cells highlight | Formula refers to the wrong cell | Fix 3 |
| Colours stay frozen after editing values | Calculation set to Manual | Fix 4 |
| Rule skips numbers that clearly qualify | Numbers stored as text | Fix 5 |
| Rules behave oddly after pasting data | Duplicate or conflicting rules | Fix 6 |
| Formatting buttons are greyed out | Sheet protection is blocking changes | Fix 7 |
| Rule fails only inside a table | Table or merged-cell behaviour | Fix 8 |
Fix 1: Check the Rule Applies to the Right Range
Start with the range, because it is the simplest cause and the fastest check. A rule that watches the wrong cells will never highlight the cells you are looking at, no matter how correct the condition is.

- Click any cell in the sheet where the rule should apply.
- Go to the Home tab and click Conditional Formatting, then Manage Rules.
- In the Show formatting rules for box, choose This Worksheet so you see every rule on the sheet.
- Read the Applies to column for each rule and check whether it covers the cells you expect.
- If the range is wrong, click the range box and select the correct range on the sheet with the mouse.
- Click Apply, then OK, and look at the sheet to see whether the colours appear.
- If the rule still does nothing, leave Manage Rules open and move on to Fix 2.
The Applies to range is easy to break without noticing. Copying and pasting cells can split one rule into several smaller ranges. Deleting rows can shrink the range. Inserting rows at the very top or bottom of the range can leave the new rows outside it. When you check the range, look for fragments: a rule that should cover A2:A100 but instead lists several small pieces is a sign that pasting has chopped it up. Fixing the range by re-selecting it in one clean drag usually restores the rule at once. If the range looks right but the rule still does nothing, the problem is higher up the list, and Fix 2 will show you how to read the rule order.
Fix 2: Open Manage Rules and Check the Rule Order
Excel runs rules from the top of the list down, and the order decides which rule wins when two rules match the same cell. A rule that sits below a stronger rule may never show its format at all.
- Go to the Home tab, click Conditional Formatting, then Manage Rules.
- Set Show formatting rules for to This Worksheet.
- Read the list from top to bottom and find the rule that should be highlighting your cells.
- Check whether a rule above it formats the same cells with a different colour or with no fill.
- Look at the Stop If True column — a ticked box stops all lower rules for matching cells.
- Use the up and down arrows to move your rule above any rule that conflicts with it.
- Untick Stop If True on higher rules unless you specifically need it, then click Apply and OK.
Stop If True is the quiet troublemaker here. It is useful when you want only the first matching rule to apply, but it is often ticked by accident, or left on from an older rule that no longer matters. When it is ticked on a high rule, every rule below it is ignored for cells that match the high rule — and if the high rule matches a wide range, it can silence most of the sheet. Rule order also matters for icon sets and data bars, which combine with other formats in ways that can hide a fill colour. After reordering, test with a value you know should match, and watch whether the format appears. If the order looks right and the rule still fails, the formula behind the rule is the next suspect, and Fix 3 walks through it.
Fix 3: Fix the Formula and the Cell References
Formula-based rules are powerful, but they are sensitive to exactly how the cell references are written. A reference that is locked when it should move, or relative when it should stay fixed, makes the rule test the wrong value in every cell.
- Select the range the rule should cover, then open Manage Rules and double-click the rule to edit it.
- Read the formula and note which cell it refers to — it should refer to the first cell of the selected range.
- Check for $ signs:
$A2locks the column but lets the row move, whileA$2locks the row but lets the column move. - For a rule that tests each row against a value in column A, the formula should look like
=$A2>100, with the row relative. - For a rule that tests every cell against one fixed cell, lock both parts, like
=B2>$Z$1. - Fix the references, click OK, then Apply, and check whether the right cells highlight.
- Test the formula by typing it in a spare cell — if it returns FALSE where you expect TRUE, the references are still wrong.
The key idea is that Excel writes the formula once, for the top-left cell of the range, and then shifts it for every other cell. If you write the formula while a different cell is active, the whole pattern shifts, and the rule tests a neighbour of each cell instead of the cell itself. That is why the wrong cells light up, or none do. Typing the formula into a spare cell is the honest test: copy the rule formula into a helper column next to your data and see whether it returns TRUE and FALSE in the pattern you expect. When the helper column looks right, paste the same formula back into the rule. If formulas are new to you, start with a simple built-in rule such as Greater Than instead, and only move to formulas once the simple rule works. For a related problem where formulas show errors instead of results, our guide to Excel spill error fix covers that separately.
Fix 4: Turn Automatic Calculation Back On
When calculation is set to Manual, Excel stops re-testing rules after you edit values. The colours freeze, and new values never trigger the rule, even though everything else about the rule is correct.
- Go to the Formulas tab and click Calculation Options.
- If Manual is selected, click Automatic instead.
- Press F9 to force a full recalculation of the workbook right away.
- Change a value that should trigger the rule and watch whether the colour updates.
- If the colour updates now, the setting was the cause — leave it on Automatic.
- Check whether another workbook set the option: calculation mode is shared across open workbooks in the same Excel session.
- Save the file after changing the setting so the workbook reopens with the correct mode.
Manual calculation is often turned on deliberately for very large workbooks, because recalculating thousands of formulas on every keystroke is slow. The trouble is that the setting stays on after the big workbook is closed, and it quietly affects every other workbook you open in the same session. That is why excel conditional formatting not working can appear suddenly in a file you did not change — a different file changed the shared setting. If you must keep Manual on for a large workbook, press F9 after editing values to refresh the rules, or use Shift+F9 to recalculate just the current sheet. For most sheets, Automatic is the right setting, and it costs nothing. If calculation is already Automatic and the colours still freeze, the values themselves may be the problem, and Fix 5 checks whether your numbers are really numbers.
Fix 5: Fix Numbers Stored as Text and Extra Spaces
A rule that compares numbers will ignore values that Excel stores as text. The cells look like numbers, they line up like numbers, and they still fail every numeric test, because Excel compares characters instead of values.

- Click a value cell that the rule should match and look at the formula bar for a leading apostrophe or extra spaces.
- Select the value range, then look at the Number Format box on the Home tab — if it says Text, the values are stored as text.
- With the range selected, go to the Data tab and click Text to Columns, then click Finish without changing anything.
- Alternatively, type the number 1 in a spare cell, copy it, select the value range, and use Paste Special with Multiply.
- To remove extra spaces, use the TRIM function in a helper column, then paste the results back as values.
- Re-check the rule after converting the values, and change a value to confirm the colour updates.
- If some cells still fail, check for non-breaking spaces pasted from web pages, which TRIM alone does not remove.
Text to Columns is the fastest repair because it forces Excel to re-read each cell and store genuine numbers. The Paste Special multiply trick works the same way: multiplying text by 1 turns it into a number. Either method is safe, but work on a copy of the file first if the data matters, so you can compare before and after. Extra spaces are sneakier than text storage, because a value like ” 200 ” with spaces can still look like 200 in the cell while failing an exact-match test. The TRIM function strips leading and trailing spaces, and CLEAN removes non-printing characters. After cleaning, the rule should match at once. If the values are genuine numbers and the rule still fails, the sheet may hold duplicate rules that fight each other, and Fix 6 clears those out.
Fix 6: Clear Duplicate and Conflicting Rules
Copying and pasting formatted cells creates new rules, and over time a sheet can collect dozens of them. Duplicates with slightly different ranges or formats fight each other, and the result looks like excel conditional formatting not working even though every rule is running.
- Open Manage Rules and set Show formatting rules for to This Worksheet.
- Count the rules — a sheet with far more rules than you remember has collected duplicates from pasting.
- Look for rules with the same condition and format but different Applies to ranges.
- Delete the duplicates one at a time, keeping the rule with the widest correct range.
- If the list is very long, use Clear Rules for the whole sheet and rebuild the rules from scratch.
- After clearing, recreate one rule at a time and test each one before adding the next.
- Save the file, then reopen Manage Rules to confirm the list stays short and clean.
Rebuilding from scratch sounds drastic, but it is often faster than untangling fifty overlapping rules. Before you clear anything, take a screenshot of the rule list, or note down the conditions and formats, so you can recreate the rules you want to keep. The usual pattern is that one good rule does the job of twenty fragments. Pasting values with Paste Special and Values in future avoids creating new rules, because it pastes the numbers without the formatting rules attached. If you paste formats deliberately, paste into the same range the rule already covers, rather than into new cells that split the range. A short, clean rule list is also easier to debug the next time a rule misbehaves. If clearing duplicates does not help, check whether the sheet itself is protected, which Fix 7 covers.
Fix 7: Check Sheet Protection and Shared Workbook Limits
A protected sheet can lock formatting, and a workbook shared with the older sharing feature restricts conditional formatting. In both cases Excel shows the rules but refuses to apply changes to them.
- Go to the Review tab and look for Unprotect Sheet — if that button is shown, the sheet is protected.
- Click Unprotect Sheet and enter the password if one is set. If you do not know the password, ask the person who protected the file.
- After unprotecting, test the rule by changing a value that should trigger it.
- If the rule works now, re-protect the sheet with Protect Sheet, but tick the boxes that allow formatting if your version offers them.
- Check whether the workbook is shared: look at the title bar or the Review tab for sharing options.
- If the file uses the older Share Workbook feature, unshare it or move the file to a modern sharing method such as OneDrive or SharePoint.
- Test the rule again after each change, so you know which setting was blocking it.
Protection is there for a reason, so do not leave a shared file unprotected longer than needed. The safe pattern is to unprotect, fix the rule, test it, and then protect the sheet again with the same settings as before. If the file is shared with other people, tell them before you unprotect it, so nobody edits the same cells at the same time. The older shared-workbook feature is a legacy tool that Microsoft has replaced with co-authoring through OneDrive and SharePoint, and it blocks several formatting features. Moving the file to OneDrive keeps the sharing but removes the old limits. If the sheet is not protected and the workbook is not shared, the fault may lie in how the rule interacts with tables or merged cells, which Fix 8 examines. For files that refuse to open at all, our guide to Excel workbook opens blank covers that problem.
Fix 8: Fix Rules Inside Excel Tables and Merged Cells
Excel Tables and merged cells both change how ranges behave, and rules that work on plain ranges can fail inside them. A rule applied to a table column may not extend to new rows, and a rule on merged cells may only test the top-left cell.

- Click inside the table and check whether the rule range covers the whole table column, including new rows.
- If new rows fall outside the rule, extend the Applies to range in Manage Rules to cover them.
- Consider converting the table back to a plain range if the table behaviour keeps breaking rules: click the table, go to Table Design, and choose Convert to Range.
- Find merged cells in the rule range: select the range and check whether Merge and Center is active on the Home tab.
- Unmerge the cells if you can, because only the top-left cell of a merged block holds a value.
- If you must keep merged cells, write the rule formula to test the top-left cell of each merged block.
- Test the rule by changing a value in a new table row and in a merged block, and confirm both respond.
Tables are usually helpful: they extend formulas and formatting to new rows automatically. Conditional formatting rules, however, do not always extend with them, especially rules created before the table existed. The fix is to check the Applies to range after adding rows, and to extend it by hand when needed. Merged cells are a different problem. Merging hides the values in all but the top-left cell, so a rule that tests each cell sees empty cells where you see one wide value. The cleanest fix is to avoid merging inside ranges that carry rules, and to use Center Across Selection for headings instead, which centres the text without merging the cells. If the rule works on plain ranges but fails in one table, rebuilding the rule after converting the table to a range usually settles it.
Fix 9: Check the Format Is Visible and the Rule Is Not Overridden
Sometimes the rule is running, but the format it applies is invisible. White text on a white fill, a pale fill on a pale background, or a font colour that matches the cell colour all look exactly like a rule that is not working.
- Open Manage Rules, double-click the rule, and click Format to see the exact format it applies.
- Check the Fill colour and the Font colour — if they are the same or very close, the format is invisible.
- Choose a strong, obvious test format, such as a bright yellow fill with dark text, and click OK.
- Click Apply and look at the sheet — if the bright format appears, the rule was running all along.
- Check for manual formatting on the cells: select the range and clear direct formatting, then test again.
- Check whether a data bar or icon set on the same cells is hiding the fill — move the fill rule above them in Manage Rules.
- Once the test format shows, change the format back to the colours you actually want.
Manual formatting deserves a closer look. When you paint a cell by hand with the fill bucket, that direct format sits on top of the cell, and it can mask a conditional format in some views, especially when the manual fill is dark. Clearing direct formats with Clear Formats removes that layer, but it also removes other hand-applied formatting, so use it on a copy first if the sheet is heavily styled. Colour choice matters too: a light grey fill on a white sheet is easy to miss, and a red font on a pink fill is hard to read. Test with loud colours first, prove the rule runs, and then choose the subtle colours you want for the finished sheet. If a loud test format still shows nothing, the workbook itself may be the problem, and Fix 10 isolates it. For display problems where data itself looks wrong, our guide to Excel filter dropdown not working covers a related fix.
Fix 10: Test the Rule in a New Workbook
Testing in a fresh workbook separates a broken rule from a broken file. If the same rule works in a new file, the original workbook carries the fault. If it fails in a new file too, the rule itself is written wrong.

- Press Ctrl+N to open a new blank workbook.
- Type a few test values in column A, such as 10, 50, 150, and 250.
- Select the values, go to Conditional Formatting, and choose Highlight Cells Rules, then Greater Than.
- Enter 100, choose a bright format, and click OK.
- Check whether 150 and 250 highlight — if they do, Excel itself is fine.
- Now recreate your original rule in the new workbook with the same formula and range shape.
- If the original rule works in the new file, copy the data from the old file into a new file with Paste Special and Values, then rebuild the rules.
A new-workbook test also catches add-in interference. Add-ins that change how Excel calculates or formats can suppress rules in specific files, and a fresh workbook with no add-in data behaves differently. To test for add-ins, close Excel, then reopen it in Safe Mode by holding Ctrl while Excel starts, and test the rule there. If the rule works in Safe Mode, disable add-ins one at a time from File, then Options, then Add-ins, until the rule works normally. Do not download so-called Excel fixer tools from unknown websites — they cannot repair a workbook and often carry unwanted software. Stick to the built-in tools and the official repair in Fix 11. If the rule fails even in a new workbook in Safe Mode, the installation itself needs attention, and Fix 11 covers that. For pivot tables that show stale data, our guide to Excel pivot table not refreshing covers that refresh problem.
Fix 11: Update Excel and Repair the Office Installation
If the rule fails in every workbook, including new ones in Safe Mode, the Excel installation may be damaged or out of date. Updating and repairing Office fixes broken program files without touching your workbooks.
- Open Excel, go to File, then Account, and click Update Options, then Update Now.
- Wait for the update to finish, restart Excel, and test the rule in a new workbook.
- If the rule still fails, close all Office apps completely.
- Open Settings in Windows, go to Apps, then Installed apps, and find Microsoft 365 or Microsoft Office.
- Click the three dots, choose Modify, and select Quick Repair, then follow the prompts.
- Restart the computer after the repair and test the rule again in a new workbook.
- If Quick Repair does not help, repeat the steps and choose Online Repair, which downloads fresh program files and takes longer.
Online Repair needs an internet connection and can take a while, so run it when you do not need Excel for an hour. It reinstalls the Office program files but keeps your documents, settings, and activation. Before either repair, save and close every open workbook, and make a copy of any file you cannot afford to lose — repairs are safe, but a copy costs nothing. After the repair, test with the simple Greater Than rule from Fix 10 before rebuilding complex formula rules. If excel conditional formatting not working survives an update and an Online Repair, the fault is almost certainly in the specific workbook, not in Excel: rebuild the workbook by copying values into a fresh file, as described in Fix 10, and recreate the rules one at a time. Contact Microsoft Support through the official help pages linked below if the problem follows you into every new file even after a repair.
What to Avoid
- Do not keep adding new rules on top of old ones — clear duplicates first, or the list becomes impossible to debug.
- Do not use Stop If True unless you mean it — one ticked box can silence every rule below it.
- Do not write formula rules while the wrong cell is active — the references shift and the rule tests the wrong cells.
- Do not paste formatted cells into new ranges without checking the rule list — pasting splits ranges and creates fragments.
- Do not leave calculation on Manual after finishing a large workbook — it freezes rules in every open file.
- Do not download Excel fixer tools from unknown websites — they cannot repair rules and often carry unwanted software.
- Do not protect a sheet and then wonder why rules cannot change — check protection before rebuilding rules.
Frequently Asked Questions
Why do my rules work on one sheet but not on another?
Because rules belong to the sheet where they were created, and each sheet has its own rule list. Open Manage Rules on the sheet that fails and check whether the rule exists there at all, and whether its range is correct. Copying a sheet copies its rules, but copying cells does not always carry them. Fix 1 shows how to check the range on the failing sheet.
Why does the rule highlight the wrong cells?
The formula almost certainly refers to the wrong starting cell, or a $ sign locks a row or column that should move. Edit the rule, check the formula against the top-left cell of the range, and test the formula in a helper column. Fix 3 walks through relative and absolute references with examples.
Why did the rule stop working after I pasted new data?
Pasting can split the Applies to range into fragments, create duplicate rules, or bring in values stored as text. Open Manage Rules and look for split ranges and duplicates, and check whether the pasted values are genuine numbers. Fixes 1, 5, and 6 cover each of those causes.
Can a rule use a formula that refers to another sheet?
Yes, but the reference must be written carefully, and the other sheet must be part of the same workbook. A formula that refers to another workbook will not work reliably for conditional formatting. Keep cross-sheet references simple, test the formula in a helper cell first, and prefer direct references where you can.
Why does the rule work when I open the file but stop later?
That pattern points to calculation being switched to Manual by another workbook, or to an add-in that interferes after loading. Check Calculation Options on the Formulas tab, press F9 to recalculate, and test in Safe Mode to rule out add-ins. Fixes 4 and 10 cover both checks.
Should I rebuild all my rules from scratch?
Only if the rule list is long and tangled with duplicates. A short, clean list is easier to fix rule by rule. If you do rebuild, note down the conditions and formats first, clear the rules, and recreate them one at a time, testing each one. Fix 6 explains the safest way to clear and rebuild.
Conclusion
When excel conditional formatting not working leaves your sheet plain, the fix is almost always the range, the rule order, the formula references, or the calculation setting — not a broken Excel. Check the Applies to range first, then the rule order and Stop If True, then the formula, and finally the calculation mode and the values themselves. Finish with a simple test: change one value that should trigger the rule and watch the colour change. That one visible change is the proof that counts. Set the rule, test the value, and let the colours do the reading for you.
