Conditional formatting can stop highlighting cells after a pasted range, a formula change, a PivotTable update, or a save to the wrong file type. The fix usually lives inside Excel's rule manager, calculation settings, or worksheet protection options.
Start with the rule itself, then move through formulas, workbook calculation, protected sheets, and cleanup only when the simple fixes do not bring the formatting back.
Check the rule in the right place
Start here when conditional formatting is missing from cells that used to change color, show icons, or display data bars. Excel may still have the rule, but the rule manager may be showing the wrong selection, worksheet, table, or PivotTable scope.
In Excel for Windows, select a cell in the affected range, then open Home > Conditional Formatting > Manage Rules.
Open Show formatting rules for and choose the range, worksheet, table, or PivotTable report that contains the rule.
In Excel for the web, go to Home > Styles > Conditional Formatting > Manage Rules.
Use Manage Rules in to switch between the current selection and the sheet.
Once the rule appears, click it and check Applies to. A rule pointed at the wrong cells cannot format the range you are checking.
Fix the Applies to range
Use this when the rule exists, but only some cells respond.
- 1.Select any affected cell or the full range that should be formatted.
- 2.Go to Home > Conditional Formatting > Manage Rules.
- 3.Select the rule, click Applies to, then choose Collapse Dialog.
- 4.Select the correct cells on the worksheet, then choose Expand Dialog.
- 5.Click OK to save the corrected range.
Repair the rule condition or format
When the range is right but the result is wrong, edit the rule itself. Go to Home > Conditional Formatting > Manage Rules, select the rule, then choose Edit Rule.
Check the rule type, condition, and Format settings. A rule can be present and still fail because its condition no longer matches the data, or because the formatting choice is not the one you expected.
For a clean rebuild, select the target cells and use Home > Conditional Formatting > New Rule. Create the rule again, confirm the condition and format, then choose OK.
Correct formula based conditional formatting
Formula rules need to return TRUE or FALSE, or 1 or 0. Open Home > Conditional Formatting > Manage Rules, select the formula rule, and choose Edit Rule.
Make sure the formula starts with =. Use relative references when Excel should adjust the reference for each cell in the selected range.
If the formula points to values that can return errors, wrap the referenced logic with IS functions or IFERROR so the rule returns a normal value.
If this method fails, use New Rule to rebuild the formula rule from scratch, then test it on a small range before applying it across the sheet.
Set calculation back to automatic
Use this when conditional formatting depends on formula results and the sheet looks stuck after you edit values. Manual calculation can leave formula-based formatting behind until Excel recalculates.
In Excel for Windows desktop, open File > Options > Formulas. Under Calculation options, set Workbook Calculation to Automatic.
You can also use Formulas > Calculation > Calculation Options > Automatic. Microsoft notes that calculation-option changes affect all open workbooks in desktop Excel.
In Excel for the web, go to Formulas > Calculation Options > Automatic Calculation. That setting affects the current browser workbook only.
Recalculate the workbook
Press F9 to recalculate changed formulas in all open workbooks.
Press Shift+F9 to recalculate only the active worksheet.
Press Ctrl+Alt+F9 to recalculate all formulas in all open workbooks.
Press Ctrl+Shift+Alt+F9 to check dependent formulas and recalculate all formulas.
Mac keyboard shortcuts can vary by keyboard layout and function-key settings.
Clean the data the rule is testing
The most common data problem is numbers stored as text, especially when numeric conditional formatting rules do not trigger.
When Excel shows the warning button on text-formatted numbers, select the cells, choose the warning button, then pick Convert to Number.
If the alert is turned off, enable it from File > Options > Formulas > Error Checking > Enable background error checking.
When the warning button is not available, insert a helper column and use =VALUE(cell_reference), then fill down and use the converted values.
For text comparisons that refuse to match, use TRIM in a helper column, such as =TRIM(A2). Then point the conditional formatting rule to the cleaned value or replace the original data with cleaned values.
Unprotect the sheet
This fixes blocked rule edits on worksheets that are protected.
In Excel for Windows, go to Review > Unprotect Sheet.
You can also use File > Info > Protect > Unprotect Sheet.
To allow formatting while the sheet stays protected, open Review > Protect Sheet, then select Format cells in Allow all users of this worksheet to.
In Excel for the web, use Review > Pause Protection, and enter the sheet password if you are prompted. Use Review > Resume Protection when you are done. Protection itself is turned on and off from Review > Manage Protection > Protect sheet.
The web Format cells permission only covers the Font and Alignment groups on the Home tab. Microsoft states that options in the Conditional Formatting dialog boxes can only be modified in Excel for Windows, so open the workbook in the Windows desktop app when you need to edit a rule on a protected sheet.
Clear broken rules and rebuild
Duplicate, stale, or conflicting rules can make conditional formatting look random. Open Home > Conditional Formatting > Manage Rules, choose the relevant scope in Show formatting rules for, select the unwanted rule, then choose Delete Rule and OK.
To wipe conditional formatting from a selected range, select the cells and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. For the whole worksheet, use Clear Rules from Entire Sheet, then create the rule again from Home > Conditional Formatting > New Rule.
Before deleting anything, click any cell that does not have a conditional format, then find every formatted cell with Home > Editing > Find & Select > Conditional Formatting. To find cells with the same conditional format, select one matching cell and use Home > Editing > Find & Select > Go To Special > Conditional formats > Same.
If this method fails, copy a working conditional format with Home > Format Painter, apply it to the target range, then review formula references afterward in Manage Rules.
Frequently Asked Questions
Why does conditional formatting work in one cell but not the copied cells?
The rule usually points to the wrong Applies to range, or a formula rule is using references that do not adjust correctly across the range. Fix the range first, then review the formula references in Manage Rules.
Why did my conditional formatting disappear after saving the file?
Conditional formatting is not retained in formats such as CSV, TXT, PRN, DIF, or SYLK. Save the workbook as Excel Workbook (*.xlsx) or Excel Macro-Enabled Workbook (*.xlsm).
Can multiple conditional formatting rules conflict with each other?
Yes. In Manage Rules, higher rules take priority over lower rules. Move rules up or down, and check Stop If True when lower rules are being skipped. Stop If True cannot be selected or cleared for data bars, color scales, or icon sets.
What if Excel itself seems to be the problem?
Start Excel in Office Safe Mode to test add-ins, then disable conflicting Excel or COM add-ins. If Excel remains broken, update Office, repair Microsoft 365 or Office, and reinstall only after repair fails.
