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.
- Click the Filter icon on the bottom left corner of the column header.
- 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.
- Select the Data tab.
- Click Filter inside the Sort & Filter section. Make sure it isn’t highlighted.
- 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.
- Right-click on any one of the worksheets and select View Code.
- 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
- 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.
- Right-click any worksheet from the Sheets tabs.
- Then, select the Ungroup Sheets option.
- Use any one of the above methods to remove filters.