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
- First, select the entire table.
- Go to the Home Tab.
- And then, Conditional Formatting.
- Next, click on “Clear Rules”.
- 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.
Alt ⇢ H ⇢ L ⇢ C ⇢ S
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:
Alt ⇢ H ⇢ E ⇢ F
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.
More on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel
- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Apply Conditional Formatting Based on a Date
- Applying Color Scales using Conditional Formatting
- Data Bars in Excel using Conditional Formatting
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel