Remove Conditional Formatting in Excel (7 Easy Ways)

Someone went overzealous with the visual ease of Conditional Formatting, slammed every Rule in the Conditional Formatting book, and made a circus out of a dataset. Too much Conditional Formatting is weighing your file down. Or maybe you're living a slightly easier work life than that and wanting to clear a Rule off the sheet is a small part of a fairly regular day.

You copied some data and carried the ghosts of the Conditional Formatting right with it. Not a problem. Wherever your Excel day is sitting on the scale from manageable to mayhem, removing Conditional Formatting is no crazy tech and we're here to prove that today.

Saving your day are several methods that will help clear Conditional Formatting of a single Rule, multiple Rules, or all the Rules on the worksheet. Some cater to removing all formats (such as color fill, borders, Number Formats, etc.) of a cell and some only deal with Conditional Formatting.

Out with the erasers and let's get removing!

Remove Conditional Formatting in Excel

Method #1 – Using Clear Rules Option

This section covers the Clear Rules options available as a part of the Conditional Formatting feature in Excel. With Clear Rules, you can remove Conditional Formatting rules from the selected cell(s), an Excel Table, a PivotTable, or the whole worksheet.

The plus point of using Clear Rules directly from the feature is knowing that the options will target just the Conditional Formatting rules. That way, only the format overlaid by a Conditional Formatting rule will be removed without removing underlying formats. Below we're exploring the application of each option in Conditional Formatting's Clear Rules.

Remove Rules from Selected Cells

The first option in Clear Rules removes all the Conditional Formatting rules from the selected cells. Note that it will work for a single selected cell the same way it would work for a range; it will only remove the formatting of all Rules from the selection. The Rule will not be deleted from the cells that are not selected.

Let's see how this works with an example. We're experimenting with a sample of monthly sales data of 2 consecutive years. The sales figures are formatted with Data Bars. We will demonstrate how to remove Conditional Formatting from the 2025 data.

Remove Rules from Selected Cells

To remove Conditional Formatting from the selected cell(s) follow these steps:

  • Select the cell(s) you want to remove Conditional Formatting

Remove Rules from Selected Cells

  • From the Home tab, select the Conditional Formatting icon in the Styles Select Clear Rules in the context menu and Clear Rules from Selected Cells from the submenu.

Remove Rules from Selected Cells

And before you know it, the Conditional Formatting will be lifted from selected cells:

Remove Rules from Selected Cells

Note: In this example case, we had selected the cells carrying the Data Bar Rule on the range C3:C14. Therefore, the complete rule was removed. Had we, let's say, only selected C3:C8 and removed the formatting, the rule would still apply to C9:C14 and the Data Bars would adjust according to the formatted figures.

You can view this aspect below where the Conditional Formatting has been cleared from the first two quarters of both years to make a half-yearly comparison:

Remove Rules from Selected Cells

As shown, the complete Rule has not been removed from the rest of the unselected cells carrying the full Rule. The Data Bars have adjusted slightly from accounting for the values in C3:C14 to just C9:C14.

Selecting the entire sheet and using the Clear Rules from Selected Cells option will wipe the Rules from the whole sheet but there's another way to go about freeing the Rules from the worksheet. Read below!

Remove Rules from Entire Sheet

Next is the option to clear all the Conditional Formatting rules from the entire worksheet. Regardless of the selection, all the Rules will be removed from the worksheet, including multiple rules applied on the same range. The steps given ahead will guide you on removing Rules from the entire sheet:

  • Access the Conditional Formatting menu from the Home Hover over Clear Rules and then select Clear Rules from Entire Sheet.

Remove Rules from Entire Sheet

All the Conditional Formatting rules present in the active worksheet will be cleared:

Remove Rules from Entire Sheet

Note: The cell selection doesn't matter with this option; every Conditional Formatting rule from the sheet will be deleted.

Remove Rules from Table

The Clear Rules from This Table option is only enabled in the submenu when an Excel Table is present on the active worksheet and any cell from it is selected. Bear in mind, that Table here refers to a Table created in Excel (e.g. using Format as Table or Insert Table) and not just a range that looks like a table. With the below-mentioned steps, you can remove Conditional Formatting rules from a Table:

  • Select any cell of the Table.
  • To remove the Rules, go to Home tab > Styles group > Conditional Formatting > Clear Rules > Clear Rules from This Table.

Remove Rules from Table

Every Rule in the Table will be deleted, clearing the Table of all Conditional Formatting:

Remove Rules from Table

Notes:

The cell selection doesn't matter when removing Rules from a Table. With any cell selected outside the Table, the Clear Rules from This Table option will be disabled in the submenu. The option will be enabled with any cell in the Table selected.

Clear Rules from This Table will not just remove a single Rule, but any number of Rules present in the Table, removing all Conditional Formatting.

Recommended Reading : How to Remove Table Formatting in Excel

Remove Rules from PivotTable

The last Clear Rules option in Conditional Formatting is Clear Rules from This PivotTable and is also enabled with a selection in the PivotTable. PivotTables are used in Excel to extract data from a dataset into a different layout, layered by the user.

That coupled with Conditional Formatting provides a powerful and meaningful tool for data analysis. But for whenever it's time to sweep away the Conditional Formatting from your PivotTable, you can do so as shown in the following steps:

  • Select a cell in the PivotTable.
  • Head to the Clear Rules options in Conditional Formatting located in the Home Select Clear Rules from This PivotTable.

Remove Rules from PivotTable

The Conditional Formatting rules will be removed from the PivotTable.

Remove Rules from PivotTable

Note: Like its preceding option, Clear Rules from This PivotTable isn't affected by which cells are selected as long as it is any cell in the PivotTable.

Method #2 – Using Manage Rules Option

You may have seen the Manage Rules option while clearing Conditional Formatting and we can also use that option to remove Conditional Formatting in Excel. Manage Rules leads to the Conditional Formatting Rules Manager where you can select and delete Rules to remove the related Conditional Formatting. The steps in this section will show you how to use the Manage Rules option to clear Conditional Formatting in Excel.

  • Select the relevant cells on the worksheet.
    • To remove one Rule – Select any cell containing that Rule.
    • To remove more than one Rule – Select a cell from each of the ranges containing the Rules
    • To remove all Rules – Select the entire sheet.
  • Open the Rules Manager by selecting the Manage Rules option in the Conditional Formatting

Method #2: Using Manage Rules Option

  • The Rules Manager is used to administer all the Conditional Formatting rules of a selection. The Show formatting rules for bar right now shows Current Selection so the Manager will display the Rules for the cells selected on the worksheet. Instead of selecting all the cells on the worksheet, switch this option from Current Selection to This Worksheet any time to display all the Rules on the sheet.
  • Choose the Rule from the Manager that you want to delete and hit the Delete Rule

There is no option for multiple selection, so you'll have to delete each rule individually.

Method #2: Using Manage Rules Option

The Rule will be deleted from the Rules Manager.

Method #2: Using Manage Rules Option

And its related Conditional Formatting will be deleted from the worksheet:

Method #2: Using Manage Rules Option

The Show formatting rules for option in the Rules Manager can be used to display Rules from the selection on the sheet, the entire sheet, and from other sheets in the workbook. Therefore, Rules from other sheets can also be deleted to remove their Conditional Formatting through the Rules Manager.

Method #3 – Using Shortcut Keys

Conditional Formatting can be removed using shortcut keys to navigate to the Clear Rules options in Conditional Formatting. Here is how the shortcut keys work:

Alt + H + L + C

Up until here, the shortcut will reach the Clear Rules options.

Alt displays the shortcut key for the tabs above the Ribbon in Excel.

H selects the Home tab.

L selects the Conditional Formatting icon in the Home tab.

C selects the Clear Rules option in the Conditional Formatting menu.

Further, the Clear Rules options can be selected by pressing the relevant key S, E, T, or P.

Method #3: Using Shortcut Keys

The final shortcut keys become:

Alt + H + L + C + S for Selected Cells

Alt + H + L + C + E for Entire Sheet

Alt + H + L + C + T for This Table

Alt + H + L + C + P for This PivotTable

Method #4 – Using Quick Analysis Tool

Conditional Formatting can instantly be removed with Quick Analysis in Excel. Quick Analysis is a convenient tool in Excel that works on a range and has groups of features that can be applied in just a couple of clicks. The Formatting section of Quick Analysis includes a Clear button which erases Conditional Formatting rules which we know by now will also remove Conditional Formatting.

Quick Analysis' Clear option will not touch the base formats of the cell, making this another effective way of clearing Conditional Formatting, maintaining the other cell formats. What you need to do to use the Quick Analysis tool to clear Conditional Formatting is mentioned as follows:

  • Quick Analysis is easily accessible after selecting a range so select your target cells.
  • The Quick Analysis icon will flash at the foot of the selected cells when the cursor hovers over the selection.

Method #4: Using Quick Analysis Tool

  • Click on the icon and select the Clear button in the Formatting
  • With the cursor pointed at one of the options in the tool, a sample preview will be shown on the selected cells.

Method #4: Using Quick Analysis Tool

The Conditional Formatting is now gone, and the other formats remain as they were:

Method #4: Using Quick Analysis Tool

Method #5 – Using the Clear Formats Option

Use the Clear Formats option in Excel to remove Conditional Formatting. Clear is a super quick feature to rid the selected cells of their contents, formatting, comments, hyperlinks, or of everything. The Clear Formats option focuses on just the format of the cell, keeping the value and other elements of the cell intact.

It is to be noted, however, that while this option will remove Conditional Formatting, it won't just stop there and will also wipe off all formatting. Therefore, use Clear Formats where the cells are devoid of other formats or when you want to remove all the formats, including Conditional Formatting, by choice. We'll now show you how to remove Conditional Formatting and all formats with Clear Formats:

  • Select the cells you want to clear the formats from.
  • In the Home tab, select the Clear button in the Editing group and choose Clear Formats from the list.

Method #5: Using the Clear Formats Option

Find the selected cells clear of Conditional Formatting and all other formats:

Method #5: Using the Clear Formats Option

Comparing the cells cleared of the format, it is evident that they have lost other formatting with Conditional Formatting. Use Clear Formats for keeping the cell contents in their plain form.

Method #6 – Using Format Painter Option

With Excel's Format Painter, Conditional Formatting applied to cells can be removed. Format Painter is a copying tool in Excel that copies the formats from one cell or range to another. The copying includes Conditional Formatting rules. Likewise, we can use an empty cell to copy its format onto a formatted cell. We can show you how to use Format Painter in Excel to remove Conditional Formatting from cells with the steps below:

  • Select the cell or range from where you want the format copied. We have selected an empty cell.
  • Click on the paintbrush icon in the Clipboard group of the Home This is the Format Painter.

Method #6: Using Format Painter Option

  • After clicking on the Format Painter, the selected cell or range will be surrounded by a marching ants border, indicating that the cell has been copied by the Format Painter. The paintbrush icon will remain selected while the cursor also takes on a small paintbrush. This means that the Format Painter is ready to format the destination cells with the copied format.
  • Click and drag the target range to reformat it.

Method #6: Using Format Painter Option

When the mouse button is released, the selected cells will be reformatted as per the copied cell(s), the paintbrush cursor will be gone, and the Format Painter icon won't remain selected anymore. Along with all formats, the Conditional Formatting has also cleared from the range:

Method #6: Using Format Painter Option

Note: As per the copied cell, columns C and D have lost their formatting.  If there is a cell that contains the formatting you want to keep or apply to the range, you can choose that cell instead of choosing an empty cell or you can go for other options to remove Conditional Formatting that will delete the rule but maintain the underlying format.

Method #7 – Using VBA

Here is another option that you can make good of that won't rob the cells of their other formats while removing Conditional Formatting. For that, we need to apply a code via VBA in Excel for Conditional Formatting rules to be deleted. VBA automates tasks in Office applications and is particularly useful for repetitive tasks.

Let's try removing Conditional Formatting with VBA. You'll find the required code and the detailed steps downstairs:

  • As the code is featured around reformatting the cell selections, you will need to begin by selecting the cells intended for Conditional Formatting
  • To access the Visual Basic editor, press the Alt + F11. If your Excel's Developer tab is enabled, you can click on the Visual Basic button in that tab.

Method #7: Using VBA

  • In the editor, use the Insert tab to open a Module window by selecting Module.

Method #7: Using VBA

  • This code is to be copy-pasted in the Module window:
Sub Remove_Conditional_Formatting()
Dim rng As Range
Set rng = Application.Selection
rng.FormatConditions.Delete
End Sub

The code is built to delete format conditions and hence the Conditional Formatting rules of the range selected on the worksheet.

Method #7: Using VBA

  • After entering the code, select the Run button north of the toolbar. The code can also be run with the F5

The code entered in VBA is run on the cell selection on the worksheet so you can close the Visual Basic editor now. Back to the sheet, the selected cells have been freed of Conditional Formatting, without impacting the other formats:

Method #7: Using VBA

Those were plenty of options to remove Conditional Formatting nine ways to Sunday. We hope we did the job of showing you how easy every method is and how you can remove just Conditional Formatting or all formats with it. Your next Excel revelation is being prepped. Stick around and keep Excel tricking!

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.