Suppose, your data changed, you’re looking for different formatting criteria, or the worksheet is becoming too cluttered with so many conditional formatting rules. In these situations, you need to find out how to remove conditional formatting in Excel to better manage your workbooks.

This effortless article on Microsoft Excel conditional formatting removal will help you explore various methods that suit different scenarios and needs. The methods will range from basic Excel tools to advanced and automated methods.

So, read this article until the end, and don’t forget to follow along the method you like on your own worksheet to practice and memorize.

Reasons to Remove Conditional Formatting in Excel

Find below the scenarios when you’ll need the Excel skill of removing conditional formatting from Excel worksheets or workbooks:

1. The underlying data has changed, and the existing conditional formatting is no longer relevant.
2. The worksheet is becoming visually cluttered. Therefore, removing unnecessary conditional formatting can simplify the visual presentation.
3. During data cleanup or restructuring, you may want to remove conditional formatting applied to cells, cell ranges, tables, and PivotTables.
4. Someone else tried to create conditional formatting rules on your worksheet but those aren’t working due to inaccurate writing of formatting rules. You want to delete these unwanted and non-functional conditional forwarding.
5. You need to apply different criteria for data analysis. Therefore, the current conditional formatting rules no longer align with your analysis requirements.
6. Conditional formatting can sometimes conflict with manually applied cell formatting. Therefore, removing it might be necessary to maintain a consistent appearance.
7. Sometimes, removing the conditional formatting rules is a necessary process to avoid undesired effects on the printed document if you’re preparing the spreadsheet for printing.
8. Your Excel workbook or worksheets can become unresponsive and too slow when there are thousands of conditional formats, especially in an enormous Excel worksheet. Removing all conditional formatting rules and leaving the cell formatting intact might enhance the worksheet’s performance.
9. Some conditional formatting rules may become redundant over time, and removing them helps maintain a more manageable set of rules.

Now that you’ve learned the reasons to delete some or all conditional formatting from cells, tables, and PivotTables, find below the best methods you must practice:

Remove Conditional Formatting Using Clear Formats

Clear Formats enables you to delete all custom formatting inside a cell or cell range. It’s available inside the Editing commands block under the Clear drop-down menu. This is the basic way of clearing formatting generated by conditional formatting rules.

Here’s how you can use Clear Formats:

1. Highlight the cell range containing one or many conditional formatting rules.
2. Click the Clear drop-down menu inside the Editing block.
3. Click on the Clear Formats option on the context menu that pops up.

This method will remove all kinds of custom formatting added to the cells, like cell color, cell border, and number formatting.

You can however copy formatting from the adjacent columns and apply that to the above cell range using the Format Painter tool.

Remove Conditional Formatting Using Format Painter

The Format Painter tool on Excel is an excellent option for you if you want to copy cell formatting from source to target. Thus, you can copy blank cell formatting from an empty cell on your worksheet and apply that formatting to a cell or cell range that contains one or more conditional formatting rules.

Here’s how it’s done:

1. Select any blank cell on your worksheet.
2. Click on the Format Painter button in the Clipboard block.
3. Now, simply highlight one cell or a cell range where you’ve got unwanted conditional formatting.

The target cell will get cell formatting just as the source cell. So, you’ll not only delete conditional formatting effects but also other cell formatting.

Remove Conditional Formatting Using Clear Rules

Clear Rules is a part of the 8 tools of Conditional Formatting tool in Excel. It lets you clear unwanted conditional formatting rules from a cell, cell range, worksheet, table, and PivotTable.

Usually, you should see the Clear Rules from Selected Cells and Entire Worksheet as the active elements in the Clear Rules submenu. However, if your worksheet contains a table and you selected that table, the Clear Rules from This Table feature will get activated.

Similarly, if your worksheet contains a PivotTable and you’re cursor is within the PivotTable, you should see the Clear Rules from This PivotTable as an active element inside the Clear Rules submenu.

Here’s how you can use Clear Rules to get rid of one or many conditional formatting rules from your worksheet:

1. Go to the Conditional Formatting button on the Home tab and click on it.
2. Hover the cursor over the Clear Rules option in the context menu.
3. Choose an appropriate choice between Clear Rules from Selected Cells and Entire Worksheet.

If you plan to select the Clear Rules from Selected Cells option, you must highlight a cell on the Excel worksheet that contains conditional formatting rules.

Similarly, you can select an Excel table and go to Conditional Formatting > Clear Rules > Clear Rules from This Table to delete conditional formatting rules from the table.

If it’s a PivotTable, just select any cell within the Pivot Table and choose Clear Rules from This PivotTable from Conditional Formatting > Clear Rules context menu.

Remove Conditional Formatting Using Manage Rules

The Manage Rules tool of Conditional Formatting in Excel is primarily for managing conditional formatting rules. Managing rules includes tasks like creating a new rule, editing an existing rule, deleting existing rules, and duplicating existing rules.

Therefore, you can also use the Manage Rules tool to get rid of redundant conditional formatting from your Excel worksheet.

Manage Rules has an advantage over other methods mentioned so far that it allows you to pick the exact conditional formatting rule you want to delete.

Also, from the same dialog box, you can delete selected rules from other worksheets of the workbook. No need to visit individual worksheets to delete unwanted conditional formatting rules.

Find below how to use Manage Rules to delete conditional formatting quickly:

1. Open the Excel workbook that needs trimming of certain conditional formatting rules.
2. Now, go to any worksheet.
3. Click the Conditional Formatting button.
4. On the context menu that opens, click on the Manage Rules option.

5. The Conditional Formatting Rules Manager dialog will open.
6. Click on the Show formatting rules for drop-down and choose a worksheet, like Sheet 2.
7. The Rules Manager dialog should update and show all the active rules on the selected worksheet.
8. Select the rule you want to remove.
9. Click on the Delete Rule button.

10. Click the Apply button and hit OK to close the Rules Manager dialog box.

Remove Conditional Formatting Using Keyboard Shortcuts

If you love to use keyboard navigation when working on Excel and wondering “how do you remove only conditional formatting in Excel” using keyboard shortcuts, this method has the answers for you.

To remove conditional formatting using Clear Rules from a selected cell or cell range, press the following keys in cascade Alt + H + L + C + S.

Similarly, use these keystrokes in the order they appear to remove all conditional formatting rules from the whole worksheet: Alt + H + L + C + E.

Do you want to delete formatting rules from a table? No worries! Just select the table on your worksheet and press these keys on your Windows keyboard: Alt + H + L + C + T.

Alt + H + L + C + P is the hotkey to delete conditional formatting from the selected PivotTable.

Remove Conditional Formatting Using the Quick Analysis Tool

When you select a cell range on your worksheet, the Quick Analysis tool shows up at the bottom right corner. It contains basic analysis tools like Formatting, Tables, Charts, etc.

Inside the Formatting section, you should see the Clear Formatting tool as the 6th option. Clicking this will delete all conditional formatting. However, this action won’t delete any cell formatting like borders, font, number formatting, etc.

So, if you want to know how to remove only conditional formatting in Excel leaving other cell formatting elements intact, you must check out this Quick Analysis-based method right now!

Remove Conditional Formatting Using Excel VBA

If you know how to write and run scripts in Excel, you’ll specifically like this method. Here, I’m going to share a few Excel VBA scripts that help you remove conditional formatting in a jiffy.

Also read: 2 Ways to Add the Developer Tab in Microsoft Excel

Excel VBA Macro

In this Excel VBA method, you’ll learn how to create a VBA Macro using the Module element of Excel VBA Editor. Then, you’ll also find out how to execute the macro to delete unwanted conditional formatting from a specific cell range. So, follow these steps:

1. Press Alt + F11 to call the Excel VBA Editor program.
2. There, click on the Insert button.
3. Click on the Module option in the pop-up that opens.
4. Copy and paste this VBA script into the blank module:

Sub DeleteConditionalFormatting()
    Dim ws As Worksheet
    Dim rng As Range

    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet5") ' Replace "Sheet5" with the actual name of your worksheet

    ' Set the cell range
    Set rng = ws.Range("C2:C6")

    ' Clear conditional formatting rules
    rng.FormatConditions.Delete
End Sub

5. Click the Save button.
6. Choose the Go back option on the pop-up that shows up.

7. You should see the Save As dialog.
8. There, click the Save as type drop-down and choose Excel Macro-Enabled Workbook or XLSM.
9. Click the Save button.
10. Close the Excel VBA Editor interface.

To run a newly created macro, press Alt + F8. Choose the macro you want to run from the list and hit the Run button.

Changes made on your Excel workbook or worksheet using an Excel VBA Macro are irreversible. So, create a backup copy before using this method.

Excel VBA Immediate Window

Executing a VBA code in the Immediate Window allows you to keep the workbook’s file type unchanged. It means, you don’t need to change the format from XLSX to XLSM. Find below the VBA script and how to run the code in an Immediate Window:

1. Open the Excel VBA Editor tool.
2. Press Ctrl + G to open the Immediate Window feature.
3. Copy and paste the following code inside the Immediate Window console:

For Each ws In Worksheets: ws.Cells.FormatConditions.Delete: Next ws

4. Take the cursor to the end of the code.
5. Hit Enter.

Quick Access Toolbar Button to Remove Conditional Formatting

If you’re currently on a project to sanitize Excel workbooks and frequently need to delete conditional formatting from many workbooks, you can add a Quick Access Toolbar item for this. Here’s how to do this:

1. Click the File menu on your Excel worksheet.
2. Select the Options button on the left-side navigation panel that pops up.
3. Now, select the Quick Access Toolbar option from the Excel Options category list.
4. Click on the Choose commands from drop-down menu and select the Home Tab option.

5. Now, scroll down the left-side Quick Access Toolbar item list and find Clear Rules.
6. There, choose the option you need. I’ve chosen the Clear Rules from Selected Cells item.
7. Click the Add button to insert this item into the right-side list.
8. Click OK to update the Quick Access Toolbar.

This change takes effect within the Excel desktop software. So, you’ll find the Clear Rules from Selected Cells function or the one you’ve chosen in all the Excel workbooks you open in the future.

Now, whenever you need a conditional formatting rule, simply select the cell or cell range and hit the Quick Access Toolbar icon for the Clear Rules from Selected Cells function.

Also read: How To Add A Form Control Button To Run Your VBA Code

Conclusion

I’ve come across Excel users who keep a backup copy of their workbooks before applying one or many conditional formatting rules to the worksheets. They do this because they are unaware of the fact that one can actually delete conditional formatting.

So, if their employer or client doesn’t like the data formatting after applying a bunch of conditional formatting rules, they would delete the existing workbook and start again from scratch using the backup copies.

Thankfully, you can avoid this trial-and-error approach as you have already learned how to delete conditional formatting from different objects of your Excel worksheet, like cells, tables, worksheets, and so on.

I hope that all the above-mentioned methods to manage conditional formatting rules effectively helped you in your Excel data visualization game. If you know a better method or have some feedback, you can spread your voice by writing a comment in the comment box just below this paragraph.