How to Remove Conditional Formatting in Excel (Shortcut + VBA)

I love using Conditional Formatting in Excel. It allows me to quickly highlight and format cells based on specified conditions.

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:

ALT + H + L + C + S

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

ALT + H + L + C + E

To use the above keyboard shortcut, first activate the sheet from which you want to remove the conditional formatting rules, and then use it.

Also read: Remove Formulas (but Keep Data) in Excel

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:

1. Select the cells from which you want to remove the formatting
2. Click on the Quick Analysis tool icon that appears at the bottom right of the selection

3. Click on the ‘Clear Format’ option

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.

Also read: Conditional Formatting Not Working in Excel

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:

1. Select the range of cells from which you want to remove the formatting
2. Click the ‘Home’ tab

3. Click on the Conditional Formatting option

4. In the options that show up, click on the ‘Clear Rules’ option
5. Click on the ‘Clear Rules from Selected Cells’ option

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:

1. Select any cell that does not have conditional formatting applied to it (this is the cell whose formatting would be copied). In this example, I have selected cell F2

2. Copy this cell (you can use the keyboard shortcut Control + C or right-click and then click on Copy)
3. Select the cells from which you want to remove the conditional formatting

4. Right-click on the selection
5. Click on the Paste Special option. this will open the Paste Special dialog box

6. In the dialog box, select the ‘Formats’ option

7. Click Ok

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:

1. Select the cells from which you want to remove the conditional formatting
2. Click the ‘Home’ tab

3. In the Editing group, click on the ‘Clear’ option
4. In the options that show up in the drop-down, click on ‘Clear Formats’

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:

ALT + H + E + F

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:

1. Click the Developer tab in the ribbon (if you don’t see the developer tab, here is a detailed guide on how to get it)
2. Click on the Visual Basic icon

3. In the VB editor that opens up, click the ‘View’ tab and then click on ‘Immediate Window’. This will make the Immediate window box appear in the VB editor. You don’t need to do this step if you already see the Immediate Window in the VB editor

4. Copy and paste the below line of code in the immediate window

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

5. Place the cursor at the end of the code line

5. Hit the Enter key
6. Close the VB editor

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:

'Code developed by Sumit Bansal from https://trumpexcel.com
Sub RemoveConditionalFomatting_AllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Cells.FormatConditions.Delete
Next ws
End Sub

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:

1. Select the cells that have the conditional formatting rules that you want to edit
2. Click the Home tab
3. Click on the ‘Conditional Formatting’ icon

4. Click on the ‘Manage Rules’ option. This will open the ‘Conditional Formatting Rules Manager’ dialog box

5. In the dialog box, you would see all the conditional formatting rules that are applied to the selected cells. Select the one that you want to delete
6. Click the ‘Delete Rule’ button

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 Excel
• How to Copy Conditional Formatting to Another Cell in Excel
• How 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