How to Remove Filter in Excel: The Fastest Ways

Adding and applying Filter in Excel might not be too challenging. As we all know, the AutoFilter feature is used to hide particular sets of data and display the ones that you need to see at a particular time.

However, there might be cases when you need to look at the complete dataset of your worksheet without omitting any of them. These situations might require the removal of the filters that you applied before. So, how are you gonna pull it off?

Not to worry, in this one, we’ll be discussing how to remove filter in excel with complete details. Just follow the steps and you should be able to do it yourself with ease. Enough with the introductions, let’s get into it.

How to Mark Filters Used in Excel

The first thing you want to do before removing filters in an Excel worksheet is to recognize and mark where the filters are. To do this, all you need to do is, follow the steps below:

  • There might be multiple dropdown buttons in your spreadsheet, you have to look for the ones that have the funnel icon.
  • Also, if a particular row number is highlighted, it also means that some rows have been hidden.

Reasons to Remove Filter in Excel

We’re all aware that when it comes to sort and analyze data, Excel AutoFilter does a splendid job. However, there are some possible setbacks that may lead to the decision of removing them. Let’s point them out:

  • When you’re dealing with a massive amount of data, the use of multiple filters may slow down your PC.
  • If an incorrect filter is applied accidentally, it can become difficult to deal with.
  • Filters make it rather difficult to take a look at all the data at once.
  • A shared excel file containing filters might become hard to manage if the other user doesn’t have the same filter settings.
  • Filters can be applied mistakenly and a small slip of the cursor can make things really difficult to handle.

5 Quick Ways to Remove Filter in Excel

Well, there are more than one way to get rid of a pre applied Filter in Excel. Each method is suitable for a particular type of scenario. We’re going to talk about five ways to go about it. So, let’s begin:

Removing Filter from a Single Column in Excel

To demonstrate the method, we’re going to use the dataset given below.

Removing Filter from a Single Column in Excel 1

  • The first thing you want to do is select the header where the filter is applied.
  • You can easily recognize it by looking at the filter icon.

Removing Filter from a Single Column in Excel 2

  • Now, you have to click on the icon to see the dropdown menu.
  • There you’ll find the Clear Filter From “Article Niche” option.

Removing Filter from a Single Column in Excel 3

  • Select it and the filter should disappear.
  • The results should then look like the image below.

Removing Filter from a Single Column in Excel 4

Clearing All Filters in Excel Worksheet

Now that we’ve learned how to remove Excel Filter in one column, let’s see how to do it in the entire worksheet.

  • The first thing you’ll want to do is, go to the Data
  • Under the Data Tab, there will be the Clear option under the Sort & Filter
  • Select it and all the filters in the selected worksheet should disappear.

Clearing All Filters in Excel Worksheet 1

How to Remove Filter from Excel Table Entirely

This method is even simpler to be honest. All you have to do is this:

  • Just like the previous method, you have to go to the Data
  • Then go to Filter within the Sort & Filter
  • Select it and all the filters will be completely removed from the entire table.

How to Remove Filter from Excel Table Entirely

Note: This can also be done by pressing the Keyboard shortcut: Alt + A + T.

Removing Filter in Excel Using Keyboard Shortcut

A Keyboard shortcut can be used to remove all filters from a particular Dataset. But to do this, you firstly have to select the worksheet you’re willing to remove the filters from.

Let’s talk about how the sequence works:

  • Press Alt to select all the tabs of the Ribbon.
  • Then, pressing Alt + D will direct you towards the Data
  • Alt + D + Fwill then select the Filter option of the Data
  • Finally, press Alt + D + F + F to remove the Filter from the entire dataset.
  • The First F applies the Filter command and the Second F removes it.

Using VBA Code to Remove Filter from Excel

To open the Microsoft Visual Basic for Applications window, press Alt + F11. Then go to Insert, and select Module.

Then, you’ll want to place the code below:

Sub Remove_Filter_From_All_Worksheet()
    Dim AF As AutoFilter
    Dim Fs As Filters
    Dim Lob As ListObjects
    Dim Lo As ListObject
    Dim Rg As Range
    Dim WS As Worksheet
    Dim IntC, F1, F2, Count As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each WS In Application.Worksheets
        WS.ShowAllData
        Set Lob = WS.ListObjects
        Count = Lob.Count
        For F1 = 1 To Count
         Set Lo = Lob.Item(F1)
         Set Rg = Lo.Range
         IntC = Rg.Columns.Count
         For F2 = 1 To IntC
            Lo.Range.AutoFilter Field:=F2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

Now, all you have to do is, run the code from the Macros option under the View tab.

Wrap Up

Now that we’ve gone through all the basic information needed for you to remove filters in Excel, you should be able to get the job in no time. Then again, if time is of the essence, then you might want to follow the fourth method that I explained.

Plus, if you’re used to applying VBA codes, you can use that as well. With that said, let’s close this one up for now. Farewell!

Related Post:

Leave a Comment