Here’s a straightforward guide on how to clear all filters from a table in Excel to restore full visibility of your data instantly.
Imagine you’re searching for your favorite book in a huge library, but every shelf has been marked with sticky notes for different genres, authors, and colors. At first, it seems helpful, but after a while, it’s just confusing. You can’t find anything because of all the tags and labels blocking the view.
Excel filters can feel the same way. Filters help narrow things down, but they can pile up and block the bigger picture. Clearing all filters in Excel brings back clarity, just like wiping off those sticky notes in the library. Here’s how to easily clear filters and reset your Excel tables.
Using Keyboard Shortcuts
You can use Ctrl + Shift + L or Alt + D + F + F hotkeys to disable the filter applied in the selected table.
To use any of these keyboard shortcuts, click anywhere on the source table.
Press the combination keys of the shortcut.
The current filtering status will be removed and the entire data will become visible.
Besides unfiltering the table, these shortcut keys also disable the Sort & Filter tool from the selected table.
These hotkeys won’t work on multiple filtered tables.
For example, you can select the header rows of more than one table using the Ctrl-click method.
Upon pressing either Alt + D + F + F or Ctrl + Shift + L won’t clear the filter. The hotkey-based clear filter action on Excel only works on a single table or worksheet.
If you need to clear all the filters applied to multiple tables in the active worksheet or in multiple worksheets of the same workbook, you must use a VBA macro, discussed later in this Excel tutorial.
Using the Clear Filter From Option
Navigate to the source table on your worksheet and click on the filter icon in the header row to which a filter has been applied.
It looks like a tiny funnel with a drop-down arrow.
You should see the right-click context menu. There, you should also find the Clear Filter From “Category” option. In your case, the text within the double quotes, after Clear Filter From syntax, could be a different column header.
Click on the Clear Filter From syntax to remove the applied filter from the table.
Using the Clear Button on the Ribbon
Another quick way to clear all filters from a table in an Excel worksheet is by using the Clear command of the Sort & Filter tool.
Now, you can find the Sort & Filter tool in the Editing block of the Home tab and the Sort & Filter block of the Data tab.
After selecting the source table, head over to any of the above Excel tool blocks in the ribbon menu.
Hit the Clear command button to delete the filter and show the whole table.
You can also modify the Excel Quick Access Toolbar to show the Clear All Filters command. This way, you can remove filters from tables in just one click.
Find the Quick Access Toolbar icon in the top left corner. Click on that and choose More Commands from the context menu.
The Excel Options dialog box will open. Click on the Popular Commands drop-down menu on the left side and choose the Data Tab option.
You’ll find the Clear All Filters command in the left side column. Select that.
Hit the Add button.
The Clear All Filters command should now show below the Customize Quick Access Toolbar column on the right side.
Click OK to save the changes you’ve made.
You should now see the Clear All Filters command in the top left corner of Excel. It could be greyed out because you haven’t selected any filtered table yet.
Highlight a filtered table to activate the command.
You can directly click on it using a left mouse click or press the Alt key and hit the corresponding number key, like 6 in this tutorial, to remove all filters from the chosen filtered table.
Using the Right-Click Context Menu
The right-click context menu is yet another intuitive way to remove all filters from the source table. However, it’ll work flawlessly only if you select the appropriate column header or row of a table to which the actual filter has been applied.
For instance, in the dataset shown above, the Fruit filter has been applied to the Category column header.
If I select Product or any cell below that column, right-click, and hover the cursor over the Filter menu, I don’t get the Clear Filter From option.
However, as I select the Category column, right-click, and hover the mouse crosshair over the Filter overflow menu, the Clear Filter From option becomes visible.
By clicking that, I can easily disable all the filters from the source table.
Clearing Filter From a PivotTable
PivotTable also allows you to remove the applied filters in multiple ways.
Firstly, you can simply click on the field in the Filters box. You should be able to locate that in the PivotTable Fields navigation panel on the right side.
Now, drag and drop it above into the Field List box to get rid of the field and filters applied through it.
If that doesn’t work for you, click on the Clear drop-down menu in the PivotTable Analyze tab and choose Clear Filters from the context menu that shows up.
Finally, click on the filter icon of the applied filter field. It’s usually located above the PivotTable.
Select All in the context menu and hit the OK button to reset the PivotTable filter.
Clear All Filters In the Workbook
If you wish to remove filters from tables in specific or all worksheets of the currently opened Excel workbook, you can use a VBA macro-based automation. It’ll walk you through how to clear all filters from a table in Excel.
This technique involves intermediate-level Excel VBA skills to set up a macro from a given script. Go through the following Excel tutorial first before continuing:
📒 Read More: How To Use The VBA Code You Find Online
Now, use the following VBA script to create a macro on the active workbook:
Sub RemoveTableFilters()
Dim ws As Worksheet
Dim wsNameList As String
Dim wsNames() As String
Dim i As Integer
' Build the list of worksheet names
For Each ws In ThisWorkbook.Worksheets
wsNameList = wsNameList & ws.Name & ", "
Next ws
' Show input box to select worksheets to remove filters
wsNameList = InputBox("Enter the worksheet names separated by commas where filters need to be removed:" & vbCrLf & "Available worksheets: " & vbCrLf & wsNameList, "Remove Table Filters")
' Split the input into an array of worksheet names
wsNames = Split(wsNameList, ",")
' Loop through each worksheet name entered
For i = LBound(wsNames) To UBound(wsNames)
Set ws = ThisWorkbook.Sheets(Trim(wsNames(i)))
' Remove table filters
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
' Loop through each listobject (table) in the worksheet and clear filters
Dim tbl As ListObject
For Each tbl In ws.ListObjects
If tbl.ShowAutoFilter Then
tbl.AutoFilter.ShowAllData
End If
Next tbl
Next i
' Show message box indicating task completion
MsgBox "Task completed successfully!", vbInformation
End Sub
Press the Alt + F8 keys together to launch the Macro dialog box.
Select the RemoveTableFilters macro from the list and hit Run.
You should see a dialog box, showing all the worksheets in the workbook.
Type in the sheet names from which you’d like to remove all table filters and hit OK.
Excel will instantly delete all the table filters only from the specified worksheets.
📚 Read more: Checkout more effortless Excel tutorials on filtering data:
Conclusions
This tutorial has shown you how to clear all filters from a table in Excel using various user interface commands, shortcut keys, and the Quick Access Toolbar. Also, you’ve seen how to reveal the whole dataset in a PivotTable by disabling filters. Finally, you’ve learned how to automate the process and get graphical queues from Excel to clear all filters in specific sheets of a workbook.
Which one did you like the most? Do you know of a better method to clear all filters in an Excel table? Use the comment box below to leave a reply and any valuable feedback should you have any.
0 Comments