Cellwise Start free

Excel guide · 5 min read

How to remove conditional formatting in Excel

Quick answerSelect the cells, then go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To remove every rule on the sheet, choose Clear Rules from Entire Sheet instead. To delete just one rule and keep the others, open Home > Conditional Formatting > Manage Rules, set Show formatting rules for to This Worksheet, select the rule and click Delete Rule. Fonts, fills and number formats you applied by hand stay as they are.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDE
1ProjectBudgetSpent% usedStatus
2Website refresh18000164500.91388888889
3CRM migration42000459001.0928571429Over budget
4Trade show12500125001
5Office move30000342001.14Over budget
6Training800031500.39375
7Brand video1500098000.65333333333
8Security audit22000231001.05Over 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.

  1. Select the cells, for example D2:D8.
  2. Go to Home > Conditional Formatting (in the Styles group).
  3. Point to Clear Rules and click Clear Rules from Selected Cells.
  4. 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.

  1. Click any cell on the sheet.
  2. Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
  3. 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.

  1. Go to Home > Conditional Formatting > Manage Rules.
  2. 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.
  3. Select the rule. The Applies to column shows the cells it covers.
  4. 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.

=C3>B3

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.

  1. Open Home > Conditional Formatting > Manage Rules and choose This Worksheet.
  2. Delete all the duplicates except one, selecting each and clicking Delete Rule.
  3. In the remaining rule's Applies to box, type the whole range, such as =$D$2:$D$200.
  4. 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.

=IF(C3>B3,"Over budget","")

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

Updated