How to Remove Auto Filters in Excel

While the auto filter doesn’t delete your data, it can hide multiple rows of the data based on the filter criteria. 

So, if you accidentally applied the wrong filter, the necessary data can stay hidden. To unhide them, you need to remove the auto filter.

You can remove the filter for a single column or across all the columns in your entire dataset as per your necessity. This applies to the data containing tables as well.

How to Remove the Auto Filter for a Single Column?

If you just want to remove the auto filter for a particular column, you can do so using the Filter icon.

1. Click the Filter icon on the bottom left corner of the column header.
2. Select the Clear Filter from “Names” option. Here, the name of the column header is “Names”. 
   

Note: Once you have removed the filters for a column, the above option will be greyed out.

How to Remove the Auto Filter for All the Data in a Worksheet?

If you particularly have a large dataset and don’t want to manually remove the filter for each column, you can follow these steps. 

1. Select the Data tab.
2. Click Filter inside the Sort & Filter section. Make sure it isn’t highlighted.
   
3. Alternatively, you can simply click the Clear button next to it. Or, you can just press the shortcut key Alt + D + F + F.
   

How to Remove Auto Filter in Multiple Worksheets?

If you have multiple worksheets, it isn’t convenient to remove filter for each worksheet manually. Instead, you can run a VBA code to do so.

1. Right-click on any one of the worksheets and select View Code.
   
2. Then, enter the following VBA code into the open module window.

Sub clear_filter_workbook ()

    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim XRange As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set XRange = xLo.Range
         xIntC = XRange.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Then, press F5 to run the code.

Why Is My Filter Button Greyed Out?

If your Filter button is greyed out, you cannot remove filters. 

This can happen when you have grouped multiple worksheets. When they are grouped, you can see “Group” next to the workbook name in the title bar. In such cases, you need to ungroup the sheets first.

1. Right-click any worksheet from the Sheets tabs.
2. Then, select the Ungroup Sheets option.
   
3. Use any one of the above methods to remove filters.