Remove Conditional Formatting in Excel


- 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.


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”.

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


And if you want to remove it from the entire sheet, click “Clear Rules from the 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.


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


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


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”.


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.


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.


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.


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:


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


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


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.