How Do You Remove Only the Conditional Formatting in Excel

While conditional formatting greatly helps to visualize data, having too many of them slows down the entire worksheet. On top of it, similar conditional formatting rules often lead to confusion rather than helping to understand the data. So, you might want to remove them.

However, removing only the conditional formatting can be a little tricky. That’s because the same cells could contain other formatting as well, which may get lost if not done correctly.

To avoid such an issue, you can access each conditional formatting rule and edit them separately. Or, delete the unwanted rule to clear only the conditional formatting from a specific cell range.

Using the Clear Rules Option

The Clear Rules option removes the conditional formatting and the respective conditional formatting rule from the Conditional Formatting rules manager.

Alternatively, you can use the option “Manage Rules” right below Clear Rules to edit or delete a specific rule.

1. Select the cell (s) whose conditional formatting you want to remove.
   
2. If you find it hard to identify cells with conditional formatting in a large dataset, use the Go To Special tool. Otherwise, skip directly to Step 6 and continue.
3. Press Ctrl + G.
4. Click Special on the next prompt.
   
5. Then, select the Conditional formats option and click OK.
   
6. Under the Home tab, select Conditional Formatting > Clear Rules and choose one of the following options.
   
   • Clear Rules from Selected Cells: Remove conditional formatting for the selected cells
   • Clear Rules from This Table: Remove conditional formatting from the selected table
   • Clear Rules from This PivotTable: Remove conditional formatting a pivot table
   • Clear Rules from Entire Sheet: Remove all the conditional formatting from the entire worksheet

Using the Quick Analysis Tool

The Quick Analysis tool is a quick way to remove conditional formatting from the selected cells. It automatically appears at the end of the last cell of the selection and goes away when there’s no active selection. Anyway, you can make it re-appear again.

1. Select the cells with conditional formatting.
2. Press Ctrl + Q to launch Quick Analysis.
   
3. Under the Formatting tab, click Clear.

Using Format Painter

As the name suggests, the format painter paints the formatting of the currently selected cells to other cells. So, you can use it to replace the conditional formatting with any other formatting you like.

However, note that the format painter completely replaces the formatting of the cell where you apply it. Meaning, both the conditional formatting and any other types of formatting will get replaced by the new formatting.

1. Select the cell (s) with the desired formatting.
   
2. Under the Home tab, double-click the Format Painter.
   
3. Drag over the cells whose conditional formatting you want to remove/replace.
   
4. Click the Format Painter icon again. Or, simply press Esc when done.

Using the Clear Format Option

This method is specifically appropriate if you want to remove all kinds of formatting, including cells highlighted using conditional formatting.

While it removes the conditional formatting from the selected cells, their formatting rules still remain intact in the Conditional Formatting Rules Manager.

1. Select the cell (s) where you want to remove the conditional formatting.
2. Under the Home tab, click Clear and select the Clear Formats option. 
   
3. Alternatively, press the shortcut key; Alt + H + E+ F.

Related Questions

How to Copy Cells without Copying Conditional Formatting?

Whenever you copy cells that have a conditional formatting rule, the cells where you paste them will automatically apply the same rules there. To avoid it,

1. Select the cells where you want to paste the copied cell values.
2. Press Alt + E + S.
3. On the Paste Special prompt, choose either the Formulas and number formats or the Values and number formats option.
   
4. Click OK.