How to Remove Conditional Formatting in Excel?

The Conditional Formatting feature provides an excellent way to highlight or draw attention to data that meets certain criteria or conditions.

It is an effective visual communication technique. Spreadsheet designers and Excel template creators should also know how to remove conditional formatting when needed. 

Too much conditional formatting can make your Excel file slow and look cluttered.

In these cases, removing conditional formatting makes the worksheet more user-friendly and easier on the eye. Alternatively, you may want to apply your own custom conditional formatting instead of the existing formats.

In this tutorial, we will provide a comprehensive guide to removing conditional formatting in Microsoft Excel.

Remove Conditional Formatting from Selected Cells

We can remove conditional formatting from a cell range that we have selected. In our sample dataset below, we have applied conditional formatting that highlights all the sales that are less than $ 300,000.

1. Select the range B2:B5.

2. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Clear Rules and then Clear Rules from Selected Cells.

3. You should see that the conditional formatting has been removed from the selected range.

Note: The above steps would only remove the Conditional Formatting from the selected cells. Any formatting that you have applied manually (such as cell background color, font color font size, or borders) won’t be impacted

Also read: Remove Gridlines in Excel

Remove Conditional Formatting from the Entire Sheet

You may have a situation where conditional formatting has been applied in multiple places in the worksheet.

In this case, you can remove the conditional formatting from the entire sheet, if that is your preference. 

This saves time since you don’t have to individually select the ranges of interest.

In our source dataset, we can see that conditional formatting has been applied to the Sales column and the Units Sold column. 

1. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Clear Rules and then ‘Clear Rules from Entire Sheet’.

2. You should see the following result. The conditional formatting has been removed from the entire worksheet.

Note: If you need to remove Conditional Formatting from multiple sheets (or the entire workbook), you need to select each sheet one by one, and then clear the conditional formatting from it using the above steps

Remove Conditional Formatting from an Excel Table

If you have an Excel Table, that has conditional formatting applied, you can remove this if need be.

In our sample dataset below, we have the revenue of different stores, shown in an Excel Table. Conditional formatting has been applied to highlight cells with values over $70000.

1. Select any cell in the Revenue Column.

2. On the Home Tab, in the Styles Group, click on Conditional Formatting. Select Clear Rules and then Clear Rules from This Table.

3. You should see that the conditional formatting has been removed from the Table.

Also read: Remove Table Formatting in Excel

Remove Conditional Formatting from a Pivot Table

You can apply conditional formatting to a Pivot Table. However, if you no longer need it, you can easily remove this formatting.

In the example below, we have a Pivot Table with conditional formatting applied to the Expenses column.

All the expenses lower than $2000 are highlighted.

1. Select one cell in the Pivot Table.

2. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Clear Rules and then Clear Rules from This PivotTable.

3. The result is the following.

Also read: Using Conditional Formatting with OR Criteria in Excel

Remove Conditional Formatting by Deleting the Actual Rule

You can delete the actual conditional formatting rule, in order to remove the conditional formatting.

In our example below, we have a company’s profits, expenses, and revenue documented for the year. 

Conditional formatting has been applied to the Revenue column.

1. Select range D4:D15 (the range from which you want to remove the conditional formatting).

2. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Manage Rules…

3. Using the Conditional Formatting Rules Manager Dialog Box, select the rule.

4. Click the Delete Rule button.

5. Click Apply and then Click Ok.
6. The Conditional Formatting will now be removed from the range.

Also read: How to Paste without Formatting in Excel (Shortcuts)

Removing Conditional Formatting by Using Shortcut Keys

We can remove the conditional formatting applied to the range through the use of shortcut keys.

In our example below, we have applied icon sets to the Sales column. 

1. Select the range B4:B9.

2. Press Alt + H + L + C + S on your keyboard.
3. The result is the following.

Note: You can also remove conditional formatting from the entire sheet by using Shortcut keys. To do this select the sheet from which you want to remove the conditional formatting. Then press Alt + H + L + C + E on your keyboard (press these keys one after the other).

As a result, all the conditional formatting would be removed from the worksheet.

Remove Conditional Formatting Using the Clear Formats Option

You also have the option of removing conditional formatting using the Clear Formats option. In our sample dataset below, we have a list of items sold. We would like to remove the conditional formatting from the Units Sold column.

1. Select the range B4:B8.

2. On the Home Tab, in the Editing Group, click on the Clear Button.  Select Clear Formats.

3. You should see that the conditional formatting has been removed from the selected range.

Note: This method removes all formatting from the selected range. So, the font will be set to the default, which is Calibri, the font color to black, and the font size set to 11.

Also read: How to Copy Formatting In Excel

Removing Conditional Formatting by Using Format Painter

You can use Format Painter to copy and paste a format from one cell to another cell or group of cells. One can thus use the Format Painter to remove Conditional Formatting. 

In our example below, we have a list of countries and the corresponding laptops sold in each country recorded.

We have used Conditional Formatting to highlight all the values greater than 40 0000.

1. Select any blank cell in the worksheet. In this case, we will select cell D5.

2. On the Home Tab, in the Clipboard Group, select Format Painter.

3. Select range B2:B9. As a result, the format from the blank cell D5 should be copied over and the conditional formatting cleared.

Note: When we use Format Painter to remove conditional formatting from the selected cells, it copies the format of the cell that we selected in Step 1, and then in Step 3, it applies that copied format to the cells that had conditional formatting. So, this is more of replacing the formatting from copied cells than the actual removal of conditional formatting.

Also read: Remove Strikethrough in Excel

Using VBA Code to Remove Conditional Formatting

We have the following dataset showing student names and the corresponding percentages they received on a Math test.

We have conditional formatting applied to the range B4:B18.

Adding an Icon that Will Act as a Button to Run the VBA Code

1. We first need to add an image to the worksheet that we will use as a button, to run a macro when clicked.
2. On the Insert Tab, click on Illustrations.
3. Click on the drop-down under Pictures and select Stock Images…

4. Select the Icons Tab and enter Cleaning in the Search Box. 

5. Choose the following Image and click to Insert.

6. The result is the following. You can reposition or resize the image according to your preference.

7. On the View Tab, in the Show Group, uncheck Gridlines.

8. You should see the following.

Adding the VBA Code to Remove Conditional Formatting

You will now need to add the Developer Tab to the Ribbon. If you are uncertain about how to do this, then please click on the following link.

1. On the Developer Tab, in the Code Group, click Visual Basic.

2. Once you are in the VBE editor, select Insert and then Module.

3. Copy and paste the following code into the Module that we just created.

'VBA Code by Steve from https://spreadsheetplanet.com
Sub Remove_Conditional_Formatting()
Dim rng As Range
Set rng = Range("B4:B18")
rng.FormatConditions.Delete
End Sub

4. Go back to the worksheet, right-click on the image, and choose Assign Macro.

5. Select the Remove_Conditional_Formatting Macro and click Ok.

6. Clicking on the image will result in the conditional formatting being removed from the range.

Also read: Copy Conditional Formatting in Excel

Applying Conditional Formatting – Quick Recap

Let’s look at a simple example, where we will learn how to quickly apply conditional formatting.

We have a sample dataset that shows the performance of each salesperson for the month. We’d like to highlight the sales that are greater than $20 000.

1. Select the range B2:B8.

2. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Highlight Cells Rules and then Greater Than…

3. Using the Greater Than Dialog Box, enter 20000. You can set your own custom format or use one of the default formats provided. In this case, we will select the Custom Format…option.

4. Using the Format Cells Dialog Box, select the Font Tab. Change the font style to Italic. Change the font color to Orange, Accent 2, Darker 25%.

5. Now, while still using the Format Cells Dialog Box, go to the Fill Tab. Select the following Fill.

6. Click Ok and then Ok again.
7. You should see all the cells in the selected range that have a value greater than $20 000 highlighted, with the custom format we set.

Conclusion

We have examined the key methods to remove conditional formatting from a worksheet. Excessive use of conditional formatting, can contribute to a cluttered look.

When taking spreadsheet design principles into account, it often becomes necessary to reduce clutter. This makes one’s worksheet more user-friendly.

Other articles you may also like:

• How to Find Duplicates in Excel (Conditional Formatting/ Count If/ Filter)
• How to Remove Duplicate Rows based on one Column in Excel?
• How to Remove Drop-down List in Excel?
• How to Remove Panes in Excel Worksheet (Shortcut)