How To Remove Conditional Formatting In Excel

Conditional formatting is a variable tool you could use to visualize your data and to see trends and patterns in your data. This feature makes it easy to highlight certain values or makes particular cells easy to identify when the data in that cell meets a specific criteria. For example, in the image below, the font color is red for all cells with values below 5.

Now while you have applied conditional formatting to your document, we will look at how to remove conditional formatting from the document.

How To Remove Conditional Formatting Using The Ribbon Option

For this guide, I will be working with the details of 15 students that took a class test. In my document, I highlighted students who got scores less than 80.

To Remove The Conditional Format of A Single Cell in Excel.

You can do this by following two simple steps:

1.)    Highlight the cell that you wish to remove the format. 

2.)    Under the HOME tab, at the upper right corner to the editing section follow the red arrow in the screenshot down below and click on the little eraser icon CLEAR, then select CLEAR FORMATS from the context menu. So, In my document, I removed the format in the C9 cell.

Notice that immediately we clicked CLEAR FORMATS, the red on number 63 in cell C9 cleared and went back to normal.

To Remove All Conditional Format In Excel.

For this section, I have a new data entry. It is a simple color scale format on the details of the profits a client’s five different websites made from January through the year till June.

Follow the steps below:

1.)    Select text. Do not select all text in your document, just the ones with the format you want to remove.

2.)    Repeat the last procedure.

It automatically erases all formatting you made to selected text.

This might seem confusing but it is exactly the same procedure as the last. My aim was to show that it works for all types of formatting, with complex finances or basic text scores.

Keyboard Shortcut To Remove Conditional Formatting

Now if you thought that last method was too much for you and still want a faster way to carry out this task, read on. There is a keyboard shortcut for this.

 

1.)    On your keyboard, click CTRL + A once or twice depending on how the data is set up in the worksheet. Once is to select the immediate range or table, twice to make sure you capture everything if that is what you want. What I am saying here is that clicking once is if you wish to select a specific text(table or range) to remove its conditional formatting.

And clicking twice to select the whole document.

2.)    Then click ALT + H + E + F

Notice that because I clicked once, therefore selecting only the first table keyed in my instruction, the formatting of my second table was not affected. But if I click twice, therefore selecting the whole document like in my screenshot, key in my instruction, and all formatting is cleared.

All you need to remember is CTRL + A (once or twice) then ALT + H + E + F

Basically, what happened here was that when we selected and clicked ALT + H, H was for the HOME tab in our Ribbon showing us the letter each section stands for, and to CLEAR FORMATS is under the EDIT section therefore the E. Then F for CLEAR FORMATS.

CTRL + A + ALT + H

CTRL + A + ALT + H + E

CTRL + A + ALT + H + E + F

It is as simple as that!

Note: Be careful, if you use the wrong keyboard shortcut you could clear all, and clear all removes everything, the formatting and all your data as a whole. But you can always undo it if you delete the data instead of clearing the format.

VBA To Remove Conditional Formatting From A Selection or Active Sheet

Removing conditional formatting using VBA programming is not as tough as you might have in your head right now. Once you've gotten past your doubt, follow my instructions. Here, I will be working with just our finance data entry for a website from January till June which is formatted to a color scale.

To Remove Conditional Formatting From A Selection

1.)    First open Visual Basic Editor (VBE) by pressing ALT + F11.

2.)    Click on INSERT at the top left corner of the editor window and select MODULE from the context menu.

This should take you to where you will run a code.

3.)    Remember that here I want to remove the conditional formatting from only a selection. I have chosen to remove the formatting from only the E2:E6 range of the able. Now copy the VBA code down below and paste it into the workspace of your VBA editor.

Sub 

RemoveConditionalFormattingFromRange()

Dim WS As Worksheet

Set WS = ActiveSheet

WS.Range("E2:E6").Cells.FormatConditions.Delete

End Sub

Doing this will remove conditional formatting from only the E2:E6 range. You can replace "E2:E6" of my above code with the range you want. You will get the following result.

To Remove Conditional Formatting From An Active Sheet

Now let's learn how to remove the conditional formatting of the whole sheet. Copy and paste the code into your VBA editor workspace.

Sub

RemoveConditionalFormatting()

Dim WS As WorkSheet

Set WS = ActiveSheet

WS.Cells.FormatConditions.Delete

End Sub

This removes conditional formatting from the active sheet. So if you run this code you will get the following result.

If required, you can as well remove the conditional formatting from the entire column or row. The following code will remove conditional formatting from column C.

Sub

RemoveConditionalFormattingFromEntireColumn()

Dim WS As WorkSheet

Set WS = ActiveSheet

WS.Columns(3).Cells.FormatConditions.Delete

End Sub

In the picture below, you would notice that all formatting on column C was removed, including the formatting in the second table.

Lastly, the next code removes conditional formatting from multiple rows. This code is set to remove the formatting from rows 1 to 15.

Sub

RemoveConditionalFormattingFromRows()

Dim WS As WorkSheet

Set WS = ActiveSheet

WS.Rows("1:15") .Cells.FormatConditiona.Delete

End Sub

Notice that the conditional formatting after 15 is not affected.

To Remove All Conditional Formatting From Every Worksheet

Now if you have a large workbook which contains many varied worksheets, each of which has one or another conditional formatting rule. You can easily remove all formatting on each sheet of the work using the code below.

Sub removecond()

Application.ScreenUpdating = False

For Each TmpSht In

ThisWorkbook.Sheets

TmpSht.Cells.FormatConditions.Delete

Next

Application.ScreenUpdating = True

End Sub

Using the codes provided, you can tweak each parameter to rhyme with your document and run your code. Although with VBA editor you have more options for editing. Whichever method method you decide on works perfectly.