If Excel conditional formatting stopped working, first check the rule’s applied range and formula, then inspect rule priority and calculation settings. In Microsoft 365 for Windows, open Home > Conditional Formatting > Manage Rules; select the relevant cells or choose This Worksheet to see rules that may not apply to the current selection.
What conditional formatting does
Conditional formatting changes a cell’s appearance when its value meets a condition. A rule can highlight numbers above a threshold, flag dates that have passed, or color an entire row based on a status cell. The underlying value does not change: formatting is an added display layer, not a replacement for the cell’s contents or a way to edit the data.
That distinction helps narrow down the problem. If a value looks wrong, inspect the cell’s contents and number format as well as its conditional formatting. If the value is right but the expected color, font, or border is missing, investigate the rule, its range, and any other rules that could affect the same cells.
Start with the rule manager
Select a cell that should be highlighted, then choose Home > Conditional Formatting > Manage Rules. The manager’s Show formatting rules for menu can display rules for the current selection or for the entire worksheet. Checking This Worksheet is useful when a rule exists but does not appear for the cells you initially selected.
Review the rule type, condition, formatting preview, and Applies to range. A rule can be valid but cover only some of the intended cells. For example, a rule with an Applies to range of =$A$2:$A$20 will not format row 21 or values in column B. Edit the range to match the actual data, and verify that the displayed format is the one you expect.
- If the rule is missing, create or restore it with Home > Conditional Formatting.
- If it is listed but the wrong cells are highlighted, correct Applies to.
- If the range and condition look right, check whether another rule overrides its appearance.
Check the Applies to range and relative references
Ranges often become incorrect after inserting or deleting rows, copying cells, or expanding a list. Excel may adjust a range during these changes, but the result might not cover newly added records. In Manage Rules, confirm the first and last cells in Applies to. A range intended for a growing list may need to be extended, or the rule may need to be applied to the relevant table column.
Formula-based rules also depend on where the range begins. Excel evaluates the formula relative to the top-left cell of the Applies to range. Suppose a worksheet has status values in B2:B100, and you want cells A2:F100 in each row highlighted when the status is Late. With the range set to =$A$2:$F$100, enter this rule formula:
=$B2="Late"
The dollar sign fixes the status column at B, while the row number remains relative so it changes as Excel evaluates each row. If the applied range instead starts at A1, the row reference must be designed for that starting point; a one-row offset can make the rule check the wrong status. Also check the Applies to box for unintended gaps or separate ranges.
Verify formula logic and cell contents
A formula rule may be syntactically valid yet never return TRUE for the data. Check spelling, spaces, comparison operators, and the cell reference. Text comparisons can fail when a status contains a trailing space, such as Late followed by an invisible blank. Dates can fail when a cell contains text that looks like a date rather than an Excel date value. Testing the relevant condition in an ordinary worksheet cell can help reveal what Excel is evaluating.
For a date rule, assume dates are in C2:C50 and the rule applies to C2:C50. This formula highlights nonblank dates earlier than today:
=AND(C2<>"",C2<TODAY())
For a past date, the condition is true and the cell receives the rule’s format; a blank cell is excluded. TODAY() uses the current date, so this rule can change as the date changes. If the rule is meant to flag a different condition—for example, dates due within seven days—the comparison needs to express that condition rather than simply test whether a date is old.
Make sure the formula’s reference matches the top-left cell of the applied range. A formula referencing C2 is appropriate when the first cell in the range is C2; if the range begins elsewhere, adjust the reference. In formula rules, relative references move as Excel evaluates the range, while dollar signs lock a row or column when needed.
Inspect rule priority and overlapping formats
More than one conditional formatting rule can apply to a cell. In Manage Rules, rules are ordered by priority, and the Stop If True option can prevent lower-priority rules from being evaluated once a higher-priority condition is true. If two true rules set different fills or font colors, the higher-priority rule can determine the conflicting appearance. Other nonconflicting formatting may still be supplied by another rule.
For example, a broad rule that colors every nonblank cell may compete with a more specific rule that marks overdue items red. Put the specific rule above the broad one, and consider Stop If True if lower rules should not affect cells that meet the specific condition. Do not rely on rule order alone: confirm that each rule’s condition and range are appropriate. The manager can show rules for the current selection, so switch to This Worksheet if you need to understand all the rules on the sheet.
Look for data-type and display issues
Excel’s stored value may not match what the cell appears to show. A number formatted as text will not behave like a numeric value in every comparison. Likewise, a date-looking string may not be recognized as a date. Imported data, pasted values, and entries with extra spaces are common reasons a rule that worked on some rows fails on others.
Check a failing cell by selecting it and looking at the formula bar. Compare it with a nearby cell that works. If the values are text, convert them to the intended type using an appropriate Excel conversion or by correcting the source data; changing the cell’s display format alone does not necessarily convert text into a number or date. For text rules, check for differences in spelling, spacing, and punctuation.
Also inspect the rule’s formatting itself. A white font on a white fill, a subtle border, or a number format that hides a value can make a rule appear inactive. Temporarily choosing a clearly visible fill can help distinguish a rule-evaluation problem from a formatting choice.
Check calculation settings and changing values
Some rules depend on formulas or functions whose results change when the workbook recalculates. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and confirm the setting is Automatic if the workbook should update calculations as values change. Manual calculation can leave formula results—and therefore conditional formatting that depends on them—out of date until recalculation occurs.
Rules based on TODAY() or other changing inputs can also appear to update at a different time than expected. A rule does not alter its source values, and conditional formatting is not a substitute for a calculation formula. If the displayed condition seems stale, confirm the workbook’s calculation mode and the values feeding the rule before rebuilding it.
Consider copying, tables, and workbook changes
Copying cells can bring conditional formatting along with other formatting, creating duplicate rules or applying an old rule to an unexpected range. Pasting values alone avoids copying the source formatting, while ordinary paste can change the destination’s rules. After a large paste or worksheet reorganization, review Manage Rules for duplicated entries and ranges that no longer match the data.
Excel tables can make rules easier to maintain as rows are added, but they do not remove the need to verify the rule’s scope. Confirm that new rows receive the intended formatting. If data was copied from another workbook, inspect whether the rule’s references still point to the correct sheet and cells. A reference that was meaningful in the source workbook may be irrelevant or broken in the destination.
For a stubborn rule, note its condition and format, then create a simple test rule on a small range. If the test works, the issue is likely in the original rule’s logic, range, or interactions. If it does not, check the cell values and the visible formatting before changing multiple rules at once. Keeping a copy of the workbook before major cleanup makes it easier to recover the original setup.
Windows, Mac, and Excel for the web
The steps above use Excel for Microsoft 365 on Windows. Excel for Mac also has conditional formatting controls under Home > Conditional Formatting, though the layout of Manage Rules and some dialog controls can differ. Excel for the web supports common conditional formatting tasks, but its rule-management options and interface can differ from the desktop application. If a rule is difficult to inspect or edit in the browser, open the workbook in the desktop app and review it there.
When asking for help or following version-specific instructions, identify whether you are using Windows, Mac, or the web version. That detail matters because a menu label or available editing control may not appear in the same place across all three.
Frequently Asked Questions
Why does conditional formatting work in some rows but not others?
The Applies to range may omit the affected rows, or a relative reference may shift differently than intended. Check the full range and confirm that the formula is anchored to the correct row and column.
Why is my conditional formatting formula not highlighting anything?
Check that the formula can return TRUE for the actual cell values. Look for text stored instead of numbers or dates, extra spaces, an incorrect comparison, and a formula reference that does not match the range’s top-left cell.
Can two conditional formatting rules conflict?
Yes. Multiple rules can apply to the same cell. Their priority and Stop If True settings affect which formats are evaluated and which conflicting appearance is shown. Review the rule order and each rule’s range.
Why did conditional formatting stop after I added rows?
The new rows may be outside the rule’s Applies to range, or a paste may have changed the rules. Open Manage Rules, choose This Worksheet, and confirm that the range includes the added data.
Does changing a cell’s format fix a conditional formatting rule?
Not usually. A cell’s ordinary formatting and its conditional formatting are separate. Changing the base fill or font does not repair a wrong condition or range, although choosing a visible rule format can help diagnose whether the rule is triggering.
Related
- Compare Lists in Excel: Find Matches and Differences
- Combine Two Cells in Excel and Keep Both Text
- Combine in Excel: Text, Cells, and Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Slicer Examples for PivotTables and Tables ExplainedUseful Excel slicer examples include filtering a sales PivotTable by region, a product table by category, or a…
- Excel Print Area Shortcut: Set, Add, and Clear ItIn Excel for Microsoft 365 on Windows, select the cells you want to print, then press Alt, P,…
- Budget Template in Microsoft Excel: Find and Use OneYes—Microsoft Excel includes budget templates you can open from File > New and search for with terms such…
- Delete a Blank Page in Word: Step-by-Step GuideTo delete a blank page in Word, turn on formatting marks, find the paragraph mark or break creating…
- Conditional Formatting in Excel: Color Coding GuideYes—conditional formatting in Excel automatically color-codes cells when their values meet rules you set. Select the cells, then…
- How to Delete a Page Range in Word Without Losing TextTo delete a page range in Word, select the content from the beginning of the first page through…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.