Remove Conditional Formatting in Excel

Last Updated: September 15, 2023
puneet-gogia-excel-champs

- Written by Puneet

When you apply conditional formatting, you also get an option to remove it with a single click. In the below example, you have a table with color scales conditional formatting.

remove-conditional-formatting

To remove this conditional formatting, you have different methods which you can use according to your convenience.

Clear Option to Remove Conditional Formatting

  1. First, select the entire table.
  2. Go to the Home Tab.
  3. And then, Conditional Formatting.
  4. Next, click on “Clear Rules”.
  5. Finally, click “Clear Rules from the Selected Cells”.
clear-option-to-remove-conditional-formatting

When you click on it, it will remove conditional formatting from the selected range.

conditional-formatting-removed

And if you want to remove it from the entire sheet, click “Clear Rules from the Entire Sheet”.

clear-rules-from-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.

manage-rules-to-clear-formatting

Once you click “Manage Rules”, it will show you the dialog box below.

manage-rules-dialog-box

You can select the conditional formatting from here and click the “Delete Rule” button to delete it.

click-delete-rule-button

It will instantly clear the conditional formatting from the selection. And if you remove it from the entire worksheet, you can change the selection from the drop down to “This Worksheet”.

clear-formatting-from-selection

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.

AltHLCS

This shortcut uses the same option we used in the first method, just with keyboard keys.

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. And from here, you use the “Clear Format” option to remove the conditional formatting.

clear-option-from-quick-analysis-tool

Creating a Button on the Quick Access Toolbar

You can also add a button on the quick access toolbar to clear the conditional formatting. Go to the File ⇢ Excel Options ⇢ Quick Access Toolbar ⇢ Choose Commands from “Home Tab” ⇢ Select the “Clear Rules from the Selected Cells” ⇢ Add ⇢ OK.

create-button-on-quick-access-toolbar

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:

AltHEF

Or you can use the clear format option from the Home Tab.

shortcut-to-remove-entire-formatting

As I said, this entirely removes the formatting from the selected range or the cell.

formatting-removed-entirely

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.