When you apply conditional formatting, you also get an option to remove it with a single click. In the example below, you have a table with conditional formatting of color scales.
We will learn methods for removing conditional formatting rules from your Excel, including individual cells, specific ranges, entire worksheets, and across multiple sheets at once.
Clear Option to Remove Conditional Formatting
This method is quite a straight forward where you use the default option in Excel for the clearing the conditional formatting.
- Select a Cell or Range – To do this, click on a cell to select it. If you want to select a range of cells, click and drag your mouse over the cells. For an entire worksheet, click the triangle in the top-left corner where the row and column headers intersect.
- Go to the Conditional Formatting Option – After selecting the cells, the next step is to access the Conditional Formatting menu. This can be found in the Home tab of the Excel ribbon. Click on Conditional Formatting to open a dropdown menu.
- Go to Clear Rules: Once the dropdown menu opens, click the Clear Rules option. Hovering your mouse over this option will open a side menu containing clearing options.
- Choose the Clear Rule Option: The side menu that appears when you hover over Clear Rules gives you two options: Clear Rules from Selected Cells or Clear Rules from Entire Sheet. You can choose the option you want. If you only want to clear the conditional formatting from the specific cells you selected, choose Clear Rules from Selected Cells. On the other hand, if you want to remove all conditional formatting rules from the entire worksheet, irrespective of whether you’ve selected any cells or not, choose Clear Rules from Entire Sheet.
When you click on it, conditional formatting will be removed from the selected range.
And if you want to remove it from the entire sheet, click “Clear Rules from the Entire Sheet”.
Use the Manage Rules Option to Clear the Conditional Formatting
Once you select the range where conditional formatting is applied, go to the Home Tab ⇢ Conditional Formatting ⇢ Manage Rules.
Once you click “Manage Rules”, it will show you the dialog box below.
You can select the conditional formatting from here and click the “Delete Rule” button to delete it.
It will instantly remove the conditional formatting from the selection. If you remove it from the entire worksheet, you can change the selection from the drop-down menu to “This Worksheet.”
Using a Keyboard Shortcut
If you have a lot of conditional formatting, you need to learn the shortcut, which can help you remove it without using any option.
Alt ⇢ H ⇢ L ⇢ C ⇢ S
This shortcut uses the same option we used in the first method, just with keyboard keys.
Using a VBA Code to Remove Conditional Formatting
Excel does not provide an option to remove conditional formatting from the entire workbook. You can use the below VBA code for this.
Sub ClearCF()
Dim mySheet As Worksheet
For Each mySheet In ThisWorkbook.Sheets
mySheet.Cells.FormatConditions.Delete
Next mySheet
End Sub
It’s simple to use and removes conditional formatting rules from the entire workbook in one go. To use this code:
- First, open VBA Editor using the keyboard shortcut Alt + F11.
- After that, right-click on any objects in the Project Explorer, go to “Insert,” and click “Module.”
- Then, paste the code into the module.
- Dim mySheet As Worksheet: This line declares a variable mySheet that will be used for the each worksheet in the workbook.
- For Each mySheet In ThisWorkbook.Sheets: This line starts a loop that will go to the each worksheet in the workbook. Each time through the loop, mySheet will represent the next worksheet.
- mySheet.Cells.FormatConditions.Delete: This line deletes all conditional formatting rules from every cell in the current worksheet.
- Next mySheet: This line ends the loop. After this line is run, if there are any worksheets left in the workbook that haven’t been processed, the code jumps back to the starting of the loop (For Each).
Use the Clear Option from the Quick Analysis Tool
When you select the range with conditional formatting, Excel shows you the Quick Analysis Tool at the bottom right.
From here, you can use the “Clear Format” option to remove the conditional formatting.
Creating a Button on the Quick Access Toolbar
You can also add a button on the quick access toolbar to clear the conditional formatting:
- Click on the “File” tab to open the File. You will see a list of options on the left-hand side. Click on the “Options” at the bottom of the list and click on it. This will open the Excel Options dialog box.
- Now click on the Quick Access Toolbar category. There’s a dropdown menu next to “Choose commands from:”. Click on this menu and select All Commands. Scroll down the list of commands until you find Clear Conditional Formats. Click on this to select it.
- Click on the “Add >>” button in the middle of the dialog box. It moves the command to the list on the right, with all the commands displayed on the Quick Access Toolbar. Click OK at the bottom of the Excel Options
It will give a button on the quick access toolbar to clear the conditional formatting with a single click.
Clearing the Entire Formatting
You can also clear the entire formatting if you have nothing other than conditional formatting. You can use the keyboard shortcut:
Alt ⇢ H ⇢ E ⇢ F
You can also use the clear format option from the Home tab.
As I said, this entirely removes the formatting from the selected range or the cell.
Out of all the methods we have discussed above, the best is to use the option from the Conditional Formatting drop-down on the home tab, the keyboard shortcut, and the creating button on the quick access toolbar.
More on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel
- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Apply Conditional Formatting Based on a Date
- Applying Color Scales using Conditional Formatting
- How to Add Data Bars in Excel
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel