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.

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.