Conditional Formatting in Excel Not Working? Common Fixes

Errors & fixes · Updated November 19, 2026

If conditional formatting in Excel is not working, first select an affected cell and open Home > Conditional Formatting > Manage Rules to check that the rule is enabled, its “Applies to” range includes the cell, and its formula or condition matches the cell’s value. Then check rule order, calculation, and whether the value is stored as the type the rule expects; these are frequent reasons a color or other format does not appear.

How conditional formatting works

Conditional formatting applies a visual format when a rule evaluates as true. A rule can test a value, compare cells, identify dates, or use a formula. The underlying cell value does not change: a cell displayed in red still contains its original number, date, or text. This matters when another formula reads the cell, or when you copy its contents.

Excel checks each rule against the cells in its “Applies to” range. For a formula rule, references are evaluated relative to the cell or cells being formatted. A rule can therefore be logically sound but still produce unexpected results if it was written for a different starting cell or points at the wrong column.

Conditional formatting is also separate from ordinary fill, font, and border formatting. A rule may be present but produce no visible effect if its formatting is subtle, if another rule takes precedence, or if the cell is outside the rule’s range. Looking at the rule and its range is usually more useful than repeatedly clicking a color button.

Check whether the rule covers the right cells

On Excel for Microsoft 365 for Windows, select a cell that should be formatted, then choose Home > Conditional Formatting > Manage Rules. In the “Show formatting rules for” list, choose Current Selection to inspect rules affecting that selection, or This Worksheet to see rules across the sheet. Check the “Applies to” box for the relevant rule. If the cell is missing from that range, the rule cannot format it.

For example, a range such as =$A$2:$A$20 does not include a new record entered in A21. Extend the range to include the new row, or create the rule while the full intended range is selected. Be careful when adding separate ranges: an accidental gap can leave just a few rows untreated. If a rule applies to an entire column, check whether the formula is designed for the first row in that column.

Also inspect the sheet name and selection. A rule may have been created on another worksheet, or the Manage Rules window may initially show only rules for the current selection. If you expect a whole-sheet rule but cannot find it, change the display to This Worksheet rather than assuming the rule was deleted.

Fix formula rules and cell references

For a formula-based rule, the formula must return TRUE for the cells you intend to format. In Manage Rules, choose the rule and inspect its formula. Confirm the formula starts with an equals sign, refers to the right cells, and uses the correct row and column anchors. A common setup is to select the target range first and write the formula as if it were being evaluated for its top-left cell.

For example, suppose task names are in A2:A100, status is in B2:B100, and you want to highlight the entire row A2:D100 when the status is Late. Select A2:D100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =$B2="Late". The dollar sign fixes the status column at B while leaving the row relative, so Excel checks B2 for the first row, B3 for the next, and so on.

If you instead used =$B$2="Late", every row would be evaluated using only B2. That absolute row reference is a common source of repeated or seemingly misplaced formatting. Conversely, leaving the column unfixed in a whole-row rule can make Excel shift the status reference as it formats columns A through D.

Another example: if dates are in C2:C100 and you want to highlight past dates but not empty cells, select C2:C100 and use =AND($C2<>"",$C2<TODAY()). The formula tests that the cell is not blank and that its date is earlier than today. If the dates are actually text, the comparison may not behave as intended; check the values as described below.

Check rule priority and overlapping formats

A cell can be covered by more than one conditional-formatting rule. In Manage Rules, review the rule order and the “Stop If True” option. When applicable, rules higher in the list are evaluated before lower ones; Stop If True can prevent lower-priority rules from being applied once a rule is true. Move a rule up or down if its priority is wrong, and examine the formatting each overlapping rule applies.

For instance, one rule might make overdue dates red, while another makes every date in the current month green. A date could meet both conditions. The final appearance depends on how those rules overlap and which formatting attributes they set. If a cell appears to ignore a rule, temporarily review or disable the competing rule rather than immediately rebuilding the first one.

Some rules affect different formatting attributes, so two rules may contribute different parts of the appearance. A fill can appear together with a font change, for example. Simplify conflicting rules when the desired result is unclear, and use Stop If True only when you deliberately want a higher rule to block lower rules. It is not a general “make this rule work” switch.

Confirm the values are the type the rule expects

A displayed value can look right while being stored differently. A number such as 12 entered as text is not necessarily treated like the numeric value 12. Dates can also be text strings rather than Excel date serial values. A rule that compares numbers or dates may fail or act inconsistently when the cells contain text.

Check a sample cell by selecting it and looking at the formula bar and number format. Try a simple test in a spare cell if needed: compare the value with the expected number, or use a basic calculation to see whether Excel treats it as numeric. For dates, changing the number format may reveal whether Excel recognizes a date; formatting text to look like a date does not convert the text into a real date.

Other common value mismatches include extra spaces in text, different spellings, and a status such as “Late ” with a trailing space instead of “Late.” A rule matching the exact text will not match that variation. If a rule is supposed to ignore letter case, note that ordinary Excel text comparisons are generally case-insensitive, but extra spaces and different characters still matter. Correct the source data or deliberately make the rule account for the variation.

Check calculation and refresh behavior

Formula-based conditional formatting depends on the results of formulas and on values such as TODAY(). If the workbook’s calculation mode is Manual, formula results may remain outdated until recalculation. On Windows, check Formulas > Calculation Options and choose Automatic if that is appropriate for the workbook. Press F9 to recalculate; Ctrl+Alt+F9 forces a full calculation of all formulas.

On Mac, the calculation setting is available from Formulas > Calculation Options in current Excel versions, though keyboard shortcuts can differ by keyboard and version. Recalculate from the Formulas tab if the Windows shortcut does not apply. Excel for the web generally recalculates supported formulas automatically, but a workbook’s behavior can still depend on the formulas and features it uses.

Calculation is especially relevant when a rule tests a formula result, a volatile function such as TODAY(), or values updated by a data connection. However, recalculating will not repair a wrong range, a misspelled condition, or a formula with incorrect references. Treat it as a targeted check rather than the default solution for every formatting problem.

Look for overrides, blanks, and merged cells

Manual formatting and conditional formatting are different. A cell’s ordinary fill color may remain underneath a conditional format, and clearing that manual fill does not necessarily remove the rule. To see whether a rule is responsible, open Manage Rules and inspect its preview and range. To remove conditional formatting from selected cells, use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Choose Clear Rules from Entire Sheet only when you intend to remove all such rules on that worksheet.

Blank cells can produce surprising results in comparison rules. For example, a “less than” test may treat blanks in ways that are not what a reader expects, especially when formulas or mixed data are involved. If blanks should never be highlighted, add an explicit not-blank test to a formula rule rather than relying on a comparison to exclude them.

Merged cells can complicate rule ranges because a merged area behaves differently from a group of independent cells. If a rule is misaligned around merged areas, test the logic on an unmerged copy of the data or redesign the layout so each record uses ordinary cells. Tables and newly added rows also deserve a range check: do not assume a manually created rule will always extend exactly as you expect when data is added.

Remove duplicates or rebuild a damaged rule

Workbooks edited over time can accumulate overlapping or duplicated rules. In Manage Rules, look for repeated formulas, unusually fragmented “Applies to” ranges, or rules that cover the same cells with slightly different formatting. Consolidating rules can make the sheet easier to maintain and reduce confusion about priority.

If you cannot identify a rule’s purpose, make a copy of the workbook before removing it. Then use Manage Rules to remove only the suspect rule, or clear rules from a small test range and create a fresh one. Select the intended range before creating the new rule, use a simple condition first, and verify the result on a cell that should clearly match and another that should not.

Excel’s Format Painter copies ordinary formatting, but it is not a reliable substitute for checking conditional-formatting rules and their ranges. When moving data between worksheets or workbooks, verify the destination rules in Manage Rules. Copying cells can bring rules along, create overlaps, or leave references pointing to the original layout.

Frequently Asked Questions

Why does conditional formatting work in one row but not another?
Check the rule’s “Applies to” range and formula references. A range may stop before the later row, or an absolute row reference such as $B$2 may cause every row to test the same cell. Confirm the rule is evaluated relative to the first cell in its range.

Why does Excel highlight the wrong cells?
A relative reference may be shifting across rows or columns differently from what you intended, or a rule may apply to a range with a different starting cell than the formula assumes. Check the formula, the top-left cell of the “Applies to” range, and any higher-priority rules.

Why is my conditional-formatting formula not accepted?
Use a formula rule through Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Start with an equals sign, check that parentheses and text quotation marks are paired, and make sure the formula is valid in the workbook’s regional settings. In standard US-style Excel formulas, arguments are separated by commas.

Does copying cells copy conditional formatting?
Copying cells can copy conditional formatting as well as other cell attributes. It can also introduce duplicate rules or references that do not suit the destination. After copying, check Manage Rules and verify both the formula and “Applies to” range.

How do I remove conditional formatting without deleting cell values?
Select the cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes conditional-formatting rules, not the cell contents. Do not choose Clear Rules from Entire Sheet unless you want to remove the worksheet’s rules.

A reliable troubleshooting order

Start with the affected cell, inspect the rule and its range, then verify the formula or condition against the actual stored value. Next, check overlapping rules and calculation mode. If the behavior is still unclear, test a simple new rule on a small range in a copy of the workbook. This order narrows the cause without risking unrelated formatting or data, and it makes the eventual fix easier to maintain.

Related

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.