How to Remove Filter in Excel (5 Easy & Quick Ways)

In the Excel sheet, we do apply Filter to do an analysis of particular data. While showing the required data Filter hides other data. Anyone may need the other data for further analysis or any other purpose. To get data back all the data from your sheet you will need to remove Filter. In this article, I’m going to explain how to remove filters in Excel.

For the purpose of the demonstration, I’m going to use a sample dataset of a particular salesperson’s sales information. The dataset contains 4 columns; these are SalesPerson, Region, Month, and Sales.

By Deselecting the Filter icon you can remove filters in Excel.

How to Know If Filter Has Been Used?

Before removing the Filter, you will need to make sure that the Filter is applied to your dataset. For that, you’ll need to look into the header of your dataset or the table.

If the drop-down icon is converted into a funnel icon that will mean that Filter is applied. Also, if the row number is Highlighted that will also mean that some rows are hidden.

How to Remove Filter in Excel: 5 Easy Ways

1. Remove Filter from Specific Column in Excel

Depending on your need you can remove Filter. If you want to remove Filter from a specific column then you can do it easily.

To demonstrate the procedure to you, I’ve taken a dataset where I applied Filter in the Region column.

To know how to apply Filter you can check this article Filter Data.

First, select the header where Filter is applied.

⏩ I selected Region column header.

Next, right click on the mouse and it will open a context menu.

⏩ Select Clear Filter From “Region”.

Hence, it will remove the Filter from the Region column, and you will get back all the datasets.

2. Remove Filter from All Columns at Once

In the case of all columns or in multiple columns with Filter then you also can remove all Filters at once.

Let me show you the procedure,

Here, I’ve taken a dataset where I applied Filter in the Region and Month column.

First, open the Data tab >> from Sort & Filter >> select Clear

Therefore,  it will remove the Filter from the columns.

If you want you can use the keyboard shortcut ALT + A + C to remove Filter from all columns.

3. Remove Filter from the Entire Excel Table

If you want to remove the Filter from the entire table, you can do it by using the ribbon feature.

Here, I want to remove the drop-down of Filter.

To begin with,

Open the Data tab >> from Sort & Filter >> select Filter

As a result,  it will remove the Filter from the entire table.

A similar operation you can perform by using keyboard shortcuts; ALT + A + T.

4. Shortcut to Remove All Filters

If you want, you can use the Keyboard Shortcut to remove Filter from the dataset.

The keyboard shortcut is ALT + D + F + F

Open the sheet from where you want to remove the Filter then press the keyboard shortcut to remove the Filter.

I’m going to describe to you how the keyboard sequence works.

First, press ALT.

It will select all the tabs of Ribbon.

Second, ALT + D will redirect to the Data tab.

Then, ALT + D + F will select the Filter command of the Data tab.

Finally, ALT + D + F + F will remove the Filter from the dataset. (One click on Filter command applies Filter another click removes it)

You also can use the keyboard shortcut CTRL + SHIFT + L to apply or to remove the Filter.

Open the sheet then press the CTRL + SHIFT + L key to remove Filter from your sheet.

Hence, it will remove the Filter from the dataset.

5. Using VBA to Remove Filters from All Worksheets of Workbook

In case your workbook contains multiple worksheets where Filter is applied rather than removing all the Filter manually you can use the VBA to remove Filters from all of your worksheets at once.

Let me show you, which worksheets of my workbook Filter are applied.

In the All_Column sheet.

In the Remove Filter From Specific Col sheet.

Also, in the From Entire Table sheet.

To open the VBA editor,

First, open the Developer tab >> select Visual Basic

➤ Now, a new window of Microsoft Visual Basic for Applications will appear.

Next, from Insert >> select Module

Now, type the following code in the Module.

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

Here, in the Remove_Filter_From_All_Worksheet sub-procedure, I declared the variable AF as AutoFilter, Fs As Filters, Lob as ListObjects, Lo as ListObject, Rg as Range, and WS as Worksheet.

Also, as Integer I declared IntC, F1, and F2.
Then, I used a nested For loop to look for while the Filter is applied and it will remove the Filter from each worksheet.

Now, Save the code and go back to any worksheet to run the VBA code.

Then, open the View tab >> from Macros >> select View Macros

A dialog box will appear. From there select Macros name and Macros in.

⏩ From Macros name I selected Remove_Filter_From_All_Worksheet. In Macros in selected the current worksheet How to Remove Filter in Excel.xlsm.

Then, click Run.

Hence, it will remove the applied Filter from all the sheets.

You can see, the applied Filter is removed from the sheet From Entire Table.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.

Download to Practice

Conclusion

In this article, I have shown 5 ways to remove Filter in Excel. These ways will help you to remove filters easily. Feel free to comment down below for any types of queries and suggestions.

<< Go Back to Filter in Excel | Learn Excel