Excel guide · 5 min read
How to remove conditional formatting in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Project | Budget | Spent | % used | Status |
| 2 | Website refresh | 18000 | 16450 | 0.91388888889 | |
| 3 | CRM migration | 42000 | 45900 | 1.0928571429 | Over budget |
| 4 | Trade show | 12500 | 12500 | 1 | |
| 5 | Office move | 30000 | 34200 | 1.14 | Over budget |
| 6 | Training | 8000 | 3150 | 0.39375 | |
| 7 | Brand video | 15000 | 9800 | 0.65333333333 | |
| 8 | Security audit | 22000 | 23100 | 1.05 | Over budget |
Download this sheet as an Excel practice file (.xlsx)
Which option to pick
The Clear Rules menu has up to four choices, and the last two only appear when your selection is inside an Excel table or a PivotTable:
- Clear Rules from Selected Cells: only the cells you selected. Rules that also cover other cells keep working there.
- Clear Rules from Entire Sheet: every rule on the active sheet, wherever it applies.
- Clear Rules from This Table: every rule on the table you're in.
- Clear Rules from This PivotTable: every rule on that PivotTable.
Method 1: clear rules from selected cells
In the sample, column D (% used) has a rule that turns a cell red when the project is over budget. Here's how to take that off without touching anything else.
- Select the cells, for example D2:D8.
- Go to Home > Conditional Formatting (in the Styles group).
- Point to Clear Rules and click Clear Rules from Selected Cells.
- The red fill disappears. The percentage format on D stays, because it isn't part of the rule.
Is there a quicker way for a block of data?
Select the range and click the Quick Analysis button that appears at its bottom-right corner, then Clear Format on the Formatting tab. It removes only conditional formatting. The button doesn't show if the range is empty or only its top-left cell has data.
Method 2: clear every rule on the sheet
When you've inherited a workbook full of rules nobody remembers, start clean. You don't need to select anything first.
- Click any cell on the sheet.
- Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
- Repeat on each sheet that needs it. The command only affects the active sheet.
Method 3: delete one rule and keep the rest
Often you want to drop one highlight, not all of them. Manage Rules lists every rule with the range it covers, which also tells you which rule is colouring a cell.
- Go to Home > Conditional Formatting > Manage Rules.
- In Show formatting rules for, choose This Worksheet. The default, Current Selection, only lists rules that touch the selected cells, which is why a rule can seem to be missing.
- Select the rule. The Applies to column shows the cells it covers.
- Click Delete Rule, then OK.
How do I find every cell that has conditional formatting?
Go to Home > Find & Select > Go To Special, choose Conditional formats and click OK. Choose All to select every formatted cell, or click one cell first and choose Same to select only cells with the same rules. Then use Clear Rules from Selected Cells.
How do I check what a rule does before deleting it?
Click Edit Rule in Manage Rules and read the formula, then try it in a spare cell for the first row of the range.
Result: TRUE
TRUE: CRM migration has spent 45,900 of a 42,000 budget, so a rule with this formula colours its row. Row 2 returns FALSE.
Fix duplicated and fragmented rules
Copying, pasting, inserting rows and using Format Painter all copy rules. After a few months, Manage Rules can show the same rule ten times, each with an odd Applies to such as =$D$2:$D$5,$D$7. The sheet gets slower and the rules start to clash.
You don't have to clear everything to fix it. Keep one copy of the rule and widen it to the full range.
- Open Home > Conditional Formatting > Manage Rules and choose This Worksheet.
- Delete all the duplicates except one, selecting each and clicking Delete Rule.
- In the remaining rule's Applies to box, type the whole range, such as =$D$2:$D$200.
- Click Apply and check the sheet, then OK.
How do I stop rules multiplying?
Paste with Home > Paste > Paste Values or Formulas when you only need the data, since a normal paste brings the source cells' rules along. Insert rows inside the range rather than pasting new ones below it, and use an Excel table, which extends one rule as it grows.
The colour won't go away: it isn't conditional formatting
If Clear Rules does nothing, the colour comes from somewhere else:
- A manual fill: select the cells and choose Home > Fill Color > No Fill.
- An Excel table style: click in the table, then choose Clear at the bottom of the Table Design > Table Styles gallery, or untick Banded Rows.
- A cell style such as Bad or Good: choose Normal under Home > Cell Styles.
- A protected sheet: the Conditional Formatting menu is greyed out until you go to Review > Unprotect Sheet.
Should I just use Clear Formats?
Only if you want everything gone. Home > Clear > Clear Formats removes conditional formats but also number formats, fonts, fills and borders, so the percentages in column D turn back into plain decimals.
Keep the flag, lose the colour
Sometimes people remove a highlight because it's slow or prints badly, but they still need to know which rows are over. A status column does that, and it can be filtered and counted, which a colour can't without extra work. Column E of the sample does this.
Result: Over budget
Over budget for CRM migration. Filled down, three projects show it: CRM migration, Office move and Security audit. Trade show spent exactly its 12,500 budget, so it stays blank.
Common mistakes
Looking for a rule with Show formatting rules for left on Current Selection
Switch it to This Worksheet to see every rule on the sheet.
Using Clear Formats to remove highlighting
That also wipes number formats and borders. Use Clear Rules instead.
Clearing rules from a few cells and expecting them gone everywhere
Clear Rules from Selected Cells only trims those cells out of the rule. Delete the rule in Manage Rules to remove it completely.
Leaving dozens of duplicated rules after copy-paste
Delete the copies and set one rule's Applies to box to the full range.
FAQ
How do I remove conditional formatting but keep the colors?
Excel can't convert rule colours into fixed fills on its own. Add a status column with IF, filter it, and apply a manual fill to the filtered rows before clearing the rules.
How do I remove all conditional formatting in Excel?
Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet. Repeat it on each sheet, since it only affects the active one.
How do I remove conditional formatting from a table?
Click inside the table, then Home > Conditional Formatting > Clear Rules > Clear Rules from This Table. The table style itself stays.
Why can't I delete conditional formatting in Excel?
The sheet may be protected, or the colour may be a manual fill, table style or cell style rather than a rule. Check Manage Rules with This Worksheet selected.
Is there a shortcut to clear conditional formatting?
There's no single shortcut key. Press Alt and follow the ribbon key tips to Conditional Formatting > Clear Rules, or add Clear Rules to the Quick Access Toolbar.
Functions used
Related guides
- Conditional formatting formula not working: how to fix it
- How to use Format Painter in Excel
- How to shade alternate rows in Excel
- How to highlight a row based on a cell value in Excel
- How to clear contents in Excel without deleting formulas
- How to abbreviate large numbers in Excel: thousands (K), millions (M), billions (B)
Updated