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

For the purpose of demonstrating how to remove Filter in Excel, we’ll use a sample dataset of a particular salesperson’s sales information, containing 4 columns – 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 has been applied to your dataset.

Look at the header of your dataset or the table. If the drop-down icon appears as a funnel icon, then the Filter is applied. Also, if the row number is Highlighted, it means that some rows are hidden.

Method 1 – Remove Filter from Specific Column

To demonstrate the procedure, we applied Filter in the Region column.

STEPS:

Select the header where Filter is applied, here the Region column header.
Right click on the mouse and it will open a context menu.
Select Clear Filter From “Region”.

The Filter will be removed from the Region column, and you will get back all the data.

Method 2 – Remove Filter from All Columns at Once

To demonstrate the procedure for removing the Filter from multiple or all columns at once, I applied Filter in the Region and Month columns.

STEPS:

Open the Data tab.
From Sort & Filter, select Clear.

The Filter is removed from the columns.

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

Method 3 – Remove Filter from the Entire Excel Table

Here, we want to remove the Filter drop-down.

STEPS:

Open the Data tab.
From Sort & Filter, de-select Filter.

The Filter is removed from the entire table.

Alternatively, you can use keyboard shortcuts ALT + A + T.

Method 4 – Remove All Filters with a Shortcut

The Keyboard Shortcut to remove Filter from the dataset is ALT + D + F + F.

Simply open the sheet where you want to remove the Filter then press the keyboard shortcut to remove it.

STEPS:

Press THE ALT key.

All the tabs of the Ribbon will be selected.

Press ALT + D to redirect to the Data tab.

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

Pressing 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 keys to remove Filter from your sheet.

The Filter will be removed from the dataset.

Method 5 – Using VBA to Remove Filters from All Worksheets of a Workbook

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

In our workbook, Filter has been applied in multiple sheets.

In the All_Column sheet:

In the Remove Filter From Specific Col sheet:

Also, in the From Entire Table sheet:

STEPS:

To open the VBA editor, open the Developer tab >> select Visual Basic.

A new Microsoft Visual Basic for Applicationswindow will appear.

From Insert >> select Module.

Enter 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, we 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 we declared IntC, F1, and F2.
Then, we used a nested For loop to look for where the Filter is applied, and remove it from each worksheet.

Save the code and go back to any worksheet to run the VBA code.
Open the View tab.
From Macros, select View Macros.

A dialog box will appear.

For Macros name, select Remove_Filter_From_All_Worksheet.
For Macros in, select the current worksheet How to Remove Filter in Excel.xlsm.
Click Run.