How to Remove Conditional Formatting in Excel? (5 Easy Ways)

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.

Dataset with Conditional Formatting
  1. Select the range B2:B5.
Screenshot showing the range B2:B5 selected.
  1. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Clear Rules and then Clear Rules from Selected Cells.
Clear Rules from Selected Cells option, highlighted.
  1. You should see that the conditional formatting has been removed from the selected range.
Result of removing conditional formatting from only the Selected Cells.

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

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. 

Conditional formatting applied in more than more place in the spreadsheet.
  1. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Clear Rules and then ‘Clear Rules from Entire Sheet’.
Clear Rules from Entire Sheet option, highlighted.
  1. You should see the following result. The conditional formatting has been removed from the entire worksheet.
Result of removing conditional formatting from the entire sheet.

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.

Source data for the table conditional formatting example.
  1. Select any cell in the Revenue Column.
one cell in the Table selected.
  1. On the Home Tab, in the Styles Group, click on Conditional Formatting. Select Clear Rules and then Clear Rules from This Table.
Clear Rules from This Table option, highlighted.
  1. You should see that the conditional formatting has been removed from the Table.
result of removing the conditional formatting from the Table.

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.

source dataset for the Pivot Table example.
  1. Select one cell in the Pivot Table.
one cell in the Pivot Table, selected.
  1. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Clear Rules and then Clear Rules from This PivotTable.
Clear Rules from This PivotTable option, highlighted.
  1. The result is the following.
result of removing conditional formatting from the Pivot Table.

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.

the source dataset conditional formatting example.
  1. Select range D4:D15 (the range from which you want to remove the conditional formatting).
range D4:D15, selected.
  1. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Manage Rules…
Manage Rules... option, highlighted.
  1. Using the Conditional Formatting Rules Manager Dialog Box, select the rule.
conditional formatting rule, selected.
  1. Click the Delete Rule button.
Delete Rule button, highlighted.
  1. Click Apply and then Click Ok.
  2. The Conditional Formatting will now be removed from the range.
Conditional Formatting removed from the range.

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. 

icon sets conditional formatting applied to the Sales column.
  1. Select the range B4:B9.
source data set for the conditional formatting example highlighted.
  1. Press Alt + H + L + C + S on your keyboard.
  2. The result is the following.
result of removing conditional formatting using shortcut keys.

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.

source dataset for the Clear Formats example.
  1. Select the range B4:B8.
the range B4:B8 selected.
  1. On the Home Tab, in the Editing Group, click on the Clear Button.  Select Clear Formats.
the Clear Formats option, highlighted.
  1. You should see that the conditional formatting has been removed from the selected range.
the conditional formatting 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.

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.

the source data set for the example.
  1. Select any blank cell in the worksheet. In this case, we will select cell D5.
cell D5, selected.
  1. On the Home Tab, in the Clipboard Group, select Format Painter.
the Format Painter button, highlighted.
  1. Select range B2:B9. As a result, the format from the blank cell D5 should be copied over and the conditional formatting cleared.
the formatting cleared from the range.

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 applied that copied format to the cells that had conditional formatting. So this is more of replacing the formatting from copied cells than actual removal of conditional formatting

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.

the source dataset for the VBA example.

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…
the Stock Images...option highlighted.
  1. Select the Icons Tab and enter Cleaning in the Search Box
Cleaning being entered into the Search Box.
  1. Choose the following Image and click to Insert.
the selected image, highlighted.
  1. The result is the following. You can reposition or resize the image according to your preference.
Screenshot showing the image on the spreadsheet.
  1. On the View Tab, in the Show Group, uncheck Gridlines.
Screenshot showing the Gridlines option, highlighted.
  1. You should see the following.
Screenshot showing the result of removing the gridlines.

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.
Screenshot showing the Visual Basic option, highlighted.
  1. Once you are in the VBE editor, select Insert and then Module.
Screenshot showing the Module option, highlighted.
  1. 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
  1. Go back to the worksheet, right-click on the image, and choose Assign Macro.
Screenshot showing the Assign Macro... option, highlighted.
  1. Select the Remove_Conditional_Formatting Macro and click Ok.
Screenshot showing the macro selected.
  1. Clicking on the image will result in the conditional formatting being removed from the range.
Screenshot of clicking on the button.

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.

Screenshot showing the source dataset for the conditional formatting example.
  1. Select the range B2:B8.
Screenshot showing the range B2:B8 selected.
  1. On the Home Tab, in the Styles Group, click on Conditional Formatting.  Select Highlight Cells Rules and then Greater Than…
Screenshot showing the Greater Than... option, highlighted.
  1. 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.
Screenshot showing the Custom Format... option, highlighted.
  1. 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%.
Screenshot showing the Font options set and highlighted.
  1. Now, while still using the Format Cells Dialog Box, go to the Fill Tab. Select the following Fill.
Screenshot showing the fill option selected.
  1. Click Ok and then Ok again.
  2. 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.
Screenshot showing the result of applying a custom format.

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: