Excel conditional formatting usually has not disappeared completely: the rule may apply to a different range, be hidden by a higher-priority rule, or no longer match the cell values. In Microsoft 365 for Windows, start at Home > Conditional Formatting > Manage Rules, choose the relevant worksheet in “Show formatting rules for,” and inspect the rules and their “Applies to” ranges before recreating anything.
What “lost” conditional formatting usually means
Conditional formatting is a set of rules that changes a cell’s appearance when specified conditions are met. A cell can therefore look unformatted even though its rule still exists: its value might not meet the condition, or the rule might cover a different set of cells than expected. A rule can also exist but produce a color or font that is difficult to see against the current cell background.
It helps to distinguish three situations. First, the rule is still present but not producing a visible result. Second, the rule exists but its “Applies to” range no longer includes the cells in question. Third, the rule itself has been deleted or lost when the workbook was edited, converted, or replaced. The fix depends on which situation applies, so check before rebuilding rules.
Conditional formatting is separate from ordinary cell formatting. A fill color applied from the Home tab can remain after a conditional-formatting rule is deleted, while a rule can change the displayed color without changing the cell’s underlying value. This distinction explains why a cell may look colored but no longer respond to changes in its data.
Check whether the rule is still there
Select a cell that should be formatted, then use Home > Conditional Formatting > Manage Rules. In the “Show formatting rules for” list, inspect “Current Selection” first. Also check “This Worksheet”: a rule may be stored at sheet level even if the selected cell is outside its range. The worksheet-wide view is particularly useful when a rule seems to have vanished from one small part of a large table.
- Look at the rule description and confirm that its condition matches the data. For example, a rule for values less than zero will not color a cell containing zero.
- Check “Applies to.” Confirm that the range includes the missing cells, not just the original rows.
- Use “Edit Rule” to review the condition and the selected format. A pale font or an unexpected fill can make formatting hard to notice.
- If several rules are listed, check their order and whether “Stop If True” is selected for a rule above the one you expect to see.
Manage Rules can display rules for the current selection, the worksheet, or the workbook, depending on the available view and Excel version. Start with the current worksheet when troubleshooting a particular sheet; a workbook-wide view can help when you do not know where the rule was created. Excel for Mac has a similar Conditional Formatting Rules Manager, though its dialog layout and some controls can differ. In Excel for the web, the available rule-management controls can differ from the desktop app, so open the workbook in the desktop version if you cannot inspect or edit a needed setting.
Repair a rule that covers the wrong cells
A changed “Applies to” range is one of the most common reasons formatting appears to disappear after adding rows, pasting data, or reorganizing a sheet. For example, a rule may cover =$A$2:$D$50 while new records have been added through row 75. The rule can still work perfectly for the original rows, but the new rows are outside its scope.
In Manage Rules, select the rule, edit its “Applies to” range, and include the cells that should respond. Check that the range is neither too small nor unintentionally broad: a rule applied to an entire column can affect unrelated labels or totals. If the rule is intended for a rectangular data area, use that area as its range and test both the first and last rows.
Formula-based rules need particular care because cell references can shift. Suppose the data occupies A2:D100, and each row should be highlighted when its status in column C is “Overdue.” The selected range is A2:D100, and the formula entered in the rule is =$C2="Overdue". The dollar sign fixes the status column while leaving the row number relative, so Excel checks C2 for the first row, C3 for the next, and so on. If the formula instead fixes both the column and row, every row may be judged by C2, producing a pattern that looks missing or incorrect.
When adjusting a formula rule, check the top-left cell of its “Applies to” range. Formula references are evaluated relative to that starting point. If the range begins at row 3 but the formula was written as though it began at row 2, the condition can be offset by one row.
Look for rules that override one another
More than one conditional-formatting rule can apply to the same cell. In that case, the visible result depends on the rules, their priority, and the properties each rule changes. In Manage Rules, rules higher in the list have higher priority. The “Stop If True” option can prevent lower-priority rules from being evaluated once a higher-priority condition is met.
For example, a red-fill rule for overdue items and a green-fill rule for completed items may be easy to distinguish. But if a broad rule applies a white fill to every nonblank cell and takes priority, it can obscure a more specific fill rule. A rule may also set only the font color while another sets the fill, so the result can combine properties rather than simply replace one complete style with another.
Review overlapping rules rather than deleting them at random. Move a more specific rule above a general one when appropriate, revise the conditions so they do not conflict, or use “Stop If True” only when lower rules genuinely should not apply. After each change, test cells that meet each condition and cells that meet none. That helps reveal whether the issue is priority or a condition that never evaluates as expected.
Check for cleared or deleted formatting
If Manage Rules shows no relevant rule for the worksheet, conditional formatting may have been removed. On the Home tab, the Clear menu includes commands for clearing conditional formatting from selected cells or from an entire sheet. A user can also delete a rule in Manage Rules. These actions may happen during cleanup or while applying a different format, and there is no special warning that the workbook’s visual logic is important.
Undo can help if the deletion was the most recent action and the workbook is still open: press Ctrl+Z on Windows. On Mac, use Command+Z. Undo is not a dependable recovery method after closing and reopening the file, after performing many later actions, or when the change was made by another user. If other edits are important, consider saving a separate copy before experimenting with additional changes.
When only a few rules are missing, recreate them through Home > Conditional Formatting. Choose the rule type that matches the requirement, set its formatting, and verify its range in Manage Rules. Avoid copying a random colored cell as a substitute: a visible fill is not the same as a rule that updates when values change.
Consider what copying, sorting, and tables changed
Copying and pasting can affect conditional formatting in ways that are not obvious. Pasting formatted cells may bring additional rules or formats into a destination range; pasting values only avoids copying the source formatting, but it does not necessarily repair a rule already attached to the destination. If a problem began after a paste, inspect the rules on both the source and destination areas and check whether their ranges overlap.
Sorting data usually moves cell contents and formatting according to the sort operation, but a poorly scoped rule or formula with unsuitable references can make the resulting display look inconsistent. Test the condition on a few rows after a sort. When a rule is meant to evaluate each record, use relative row references deliberately, and keep the rule’s range aligned with the data area.
Excel tables can expand as rows are added, but do not assume every conditional-formatting rule will have the intended scope in every workbook. Check “Applies to” after adding records or changing a table’s layout. If the workbook uses a table, also distinguish table banding and table styles from conditional formatting: a table’s alternating row colors can look similar but are controlled separately through the Table Design tab.
Check the file type and recover an earlier version
Saving a workbook as CSV is a frequent source of confusion. CSV stores text values separated by delimiters; it does not preserve Excel’s conditional-formatting rules, formulas, multiple worksheets, or rich cell formatting. If the only copy was saved as CSV and the original workbook is unavailable, reopening the CSV cannot restore those rules. For a workbook that needs Excel formatting and rules, use an Excel workbook format such as .xlsx or, when macros are required, .xlsm.
When formatting disappeared after saving or sharing a file, check whether the file was converted or exported, rather than assuming Excel randomly removed the rules. Ask whether someone edited a copy, downloaded a CSV, or saved over the workbook in another format. If the file is stored in OneDrive or SharePoint, use Version History to inspect or restore an earlier version. In Excel for Microsoft 365, the workbook’s File information area may provide version-history access for cloud files; the exact route can vary with the account and storage location.
Before restoring an older version, compare it with the current file. The older copy may contain the missing rules but lack newer data or edits. A safe approach is to open the earlier version separately, inspect its rules, and then restore or copy only what is needed. For files stored only on a computer, recovery depends on whether a backup or another saved copy exists; Excel cannot reconstruct deleted rules from a CSV or from a workbook that has no recoverable earlier version.
Rebuild and verify the formatting safely
If a rule is genuinely gone and no usable previous version exists, recreate it based on the intended condition rather than on appearance alone. Write down which cells it should affect, what values should trigger it, and which format should appear. Then choose the rule type in Home > Conditional Formatting, set the condition and formatting, and inspect the resulting rule in Manage Rules.
- Test a value that should trigger the rule, one that should not, and a boundary value such as zero or the date at the edge of a deadline.
- Check blank cells and error values if they can occur in the range; decide whether they should be formatted.
- Review the scope after inserting or deleting rows, and confirm formula references on more than one row.
- Save the workbook in .xlsx or .xlsm format, as appropriate, and reopen it to confirm that the rule remains.
For date-based rules, confirm that the cells contain real Excel dates rather than text that merely looks like a date. For color scales, data bars, and icon sets, verify that the minimum and maximum settings suit the current data. If a scale is based on a changing range, adding extreme values can alter the appearance of every cell without deleting the rule. A changed visual pattern is not necessarily lost formatting; it can be a valid rule responding to new data.
Frequently Asked Questions
Why did conditional formatting disappear after I added rows?
New rows may fall outside the rule’s “Applies to” range. Open Home > Conditional Formatting > Manage Rules, select the worksheet, and extend the range if the added rows should use the rule.
Why can I see a rule but not its color?
The cell may not meet the condition, a higher-priority rule may affect the same formatting property, or the selected font and fill colors may be difficult to distinguish. Check the condition, priority, “Stop If True,” and format preview in Manage Rules.
Does saving an Excel file as CSV remove conditional formatting?
Yes. CSV does not preserve Excel conditional-formatting rules. Reopen the original .xlsx or .xlsm file, or recover a suitable earlier workbook version; the CSV itself does not contain the rules.
Can I recover conditional formatting after closing Excel?
Possibly, if a cloud Version History entry, backup, or another workbook copy contains it. Undo is generally useful only while the relevant change remains in the active undo history; it cannot recover rules from a CSV file.
Why does conditional formatting work on some rows but not others?
Check whether the rule’s range includes all those rows and whether a formula uses the intended relative and absolute references. A formula that anchors the wrong row can test one cell repeatedly instead of checking each record.
Related
- Text Split in Excel: Polish Menus and Methods
- Text Split in Excel: How to Separate Data into Columns
- Rate Quotation Format in Excel: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel CONCAT to Remove a File Name SuffixCONCAT in Excel joins text; it does not remove a file-name suffix by itself. To remove the last…
- Excel COUNTIF Not Counting Properly? Fix the CauseIf Excel COUNTIF is not counting properly, check the criterion, the cells’ actual contents, and the range first:…
- Average in Excel Not Working? Common Causes and FixesIf AVERAGE in Excel is not working, first check that the referenced cells contain numbers and that none…
- Excel Goal Seek Not Working? Fix These Common ProblemsIf Excel Goal Seek is not working, first confirm that the Set cell contains a formula linked to…
- Excel Error 0x80070194: OneDrive Fixes and CausesExcel error 0x80070194 usually means Windows could not reach the cloud-file provider for a workbook stored in OneDrive;…
- Why Is My Excel Filter Not Working? Causes and FixesAn Excel filter usually fails because the selected range is incomplete, the data has blank or merged headers,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.