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!
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.
To remove Conditional Formatting from the selected cell(s) follow these steps:
- Select the cell(s) you want to remove Conditional Formatting
- 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.
And before you know it, the Conditional Formatting will be lifted 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:
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.
All the Conditional Formatting rules present in the active worksheet will be cleared:
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.
Every Rule in the Table will be deleted, clearing the Table of all Conditional Formatting:
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.
The Conditional Formatting rules will be removed from the 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
- 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.
The Rule will be deleted from the Rules Manager.
And its related Conditional Formatting will be deleted from the worksheet:
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.
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.
- 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.
The Conditional Formatting is now gone, and the other formats remain as they were:
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.
Find the selected cells clear of Conditional Formatting and all other formats:
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.
- 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.
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:
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.
- In the editor, use the Insert tab to open a Module window by selecting Module.
- 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.
- 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:
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!
