While Conditional Formatting is great, it’s also volatile – which means that if you have a lot of conditional formatting rules applied to large data sets, it can slow down your Excel file. So, I also have to make sure that I remove conditional formatting from datasets where I don’t need it anymore. In this short tutorial, I will show you a couple of easy ways you can use to remove conditional formatting from a selected range of cells, the entire worksheet, or the workbook. I will also show you how you can remove specific conditional formatting rules while keeping the rest.
Keyboard Shortcut to Remove Conditional Formatting
Below is the keyboard shortcut to remove Conditional formatting rules from the selected range of cells: To use the above keyboard shortcut, you first need to select the cells from which you want to remove that Conditional formatting and then press these keys in succession (one after the other) And below is the keyboard shortcut to clear conditional formatting rules from the entire worksheet To use the above keyboard shortcut, first activate the sheet from which you want to remove the conditional formatting rules, and then use it.
Clear Conditional Formatting Using the Quick Analysis Tool option
Another quick way to quickly clear conditional formatting from the selected cells is by using the Quick Analysis tool option. When you select a range of cells, it would show you the quick analysis tool icon at the bottom right of this election, where you would have one-click access to the clear formatting option. Below I have a data set where I have conditional formatting applied to the scores of students. And here are the steps to quickly remove conditional formatting from these cells that contain the scores: Note that this is only going to remove the conditional formatting from the cells, it is not going to remove any other formatting such as fill color or borders or font style, etc.
Remove Conditional Formatting Using the ‘Clear Rules’ Option
If you think that the keyboard shortcut is long and quite hard to remember (which I agree it is), you can also use the inbuilt ‘Clear Rules’ option in the ribbon to clear conditional formatting rules from a selected range of cells or from the entire worksheet. Below are the steps for removing conditional formatting from a selected range of cells: The above steps would remove conditional formatting from the selected range of cells only, and if you have applied it anywhere else in the worksheet, it would remain unaffected. In case you want to get rid of all the conditional formatting in the worksheet, you can choose the “Clear Rules from Entire Sheet” option. In the clear rules option, you would also see two additional options – “Clear Rules from This Table” and “Clear Rules from This Pivot Table”. Unless you’re working with a Pivot table or an Excel Table, these would be grayed out, and would only become available when you select a cell inside an Excel Table or Pivot Table.
Remove Conditional Formatting Using ‘Paste Format Only’ Hack
When you copy and paste a cell over another cell, it would not only copy and paste the value of the cell, but also the formatting. Excel also allows you to only copy and paste the formatting from the copied cell, which is something we can use to remove conditional formatting from any cell or range of cells. Below I have a data set where I have conditional formatting applied to the scores of students. Here are the steps to remove the conditional formatting from these cells using the Paste Special technique: The above steps would override the formatting of the selected cells and copy the formatting from the cell we selected in Step 1. One drawback of this method is that it would remove all the formatting from the selected cells (not just the conditional formatting). So if you copy formatting from a cell that has no formatting applied to it and paste the formatting on the selected range of cells, along with the conditional formatting, you would also lose any other formatting such as fill color, border, font style, etc.
Clear All Formatting (Including Conditional Formatting)
Another way you can remove conditional formatting from a selected range of cells is by clearing all the formats. Doing this would remove all the formatting such as cell color, bold/italics, borders, font size, etc. (as well as conditional formatting). Below are the steps to clear all the formatting from the selected range of cells in Excel: The above steps would leave the content of the cell as is, but remove all the formatting from it. You can also use the below keyboard shortcut to clear all the formatting:
Remove Conditional Formatting from All Worksheets in One Go (using VBA)
If you have a lot of worksheets in the workbook and you want to remove Conditional formatting rules from all these worksheets, doing it one worksheet at a time would be time-consuming. A quick way to clear conditional formatting rules from all the worksheets in a workbook is by using a simple VBA macro code. Below are the steps to use a simple one-line VBA macro code to quickly cycle through all the worksheets in your workbook and remove conditional formatting from each sheet: When you place the cursor at the end of the code line and hit the enter key, that line of code is executed. The above code uses a simple For Each Next loop, where it loops through each worksheet in the workbook and uses Cells.FormatConditions.Delete method to delete conditional formatting from each sheet. Note that this would only remove conditional formatting and all the other formatting such as cell color, font color/size, borders, etc would not be impacted. One big benefit of using the Immediate window to run simple codes like these is that you don’t have to worry about anything else (such as saving the file as a macro-enabled workbook). You can run the code using the steps shown above and close the VB editor. Pro Tip: Remember that the changes made using a VBA code are irreversible, so always make a backup copy before running the VBA code In case you’re looking for the whole code that you can use in the module in the VB Editor, you can use the code below: If you use the above code (by copy-pasting it in a module in your workbook), make sure to save your workbook as a macro-enabled file (or else the code would be last the next time you open the file)
Remove Specific Conditional Formatting Rules
So far, I have shown you how to remove all the conditional formatting rules from the selected cells or the entire worksheet. But in some cases, you may not want to get rid of conditional formatting completely. Rather, you may want to edit or delete a few conditions formatting rules while keeping the others. Below I have a data set where I have student names in column A and their scores in columns B, C, and D. In the cells that contain their score, I have two conditional formatting rules
Highlighting cells where the score is less than 30 in red font in red fill color and Highlighting cells where the score is more than 80 in green color
Now let me show you how to only clear one of the conditional formatting rules while keeping the other rule in place: The above steps would delete the selected rule only. If you want to delete multiple conditional formatting rules, you can repeat the process by selecting the rule that you want to delete and then clicking the Delete Rule button. Note that it does not allow you to select multiple rules, so you would have to do it one by one only. In the dialog box, you also get options that allow you to edit any existing rule, duplicate a rule, or add a new rule from scratch. In this tutorial, I showed you how to remove conditional formatting rules using keyboard shortcuts or using the inbuilt ‘Clear Rules’ option in the ribbon. I also covered how to remove conditional formatting rules from all the worksheets in a workbook in one go using the group worksheets method or using a simple one-line VBA code. And finally, I covered how to remove some of the conditional formatting rules while keeping the rest. I hope this tutorial was easy to follow and would be helpful in your day-to-day work. Other Excel tutorials you may also like:
How to Remove Table Formatting in Excel (Easy Guide)Apply Conditional Formatting Based on Another Column in ExcelHow to Copy Conditional Formatting to Another Cell in ExcelHow to Remove Cell Formatting in Excel (from All, Blank, Specific Cells)Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)How to Apply Conditional Formatting in a Pivot Table in Excel
