6 Ways To Clear All Filters From a Table in Microsoft Excel

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.

Shortcut key combination
Shortcut key combination

To use any of these keyboard shortcuts, click anywhere on the source table.

Press the combination keys of the shortcut.

Clear all filters in table using a shortcut key
Clear all filters in table using a shortcut key

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.

Shortcut won't work on multiple tables
Shortcut won’t work on multiple 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

Clear Filter From
Clear Filter From

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.

Clear all filter command in Data tab
Clear all filter command in 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.

More Commands
More Commands

Find the Quick Access Toolbar icon in the top left corner. Click on that and choose More Commands from the context menu.

Data tab commands in Excel options
Data tab commands in Excel options

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.

Customize Quick Access Toolbar
Customize Quick Access Toolbar

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.

Clear all filters greyed out
Clear all filters greyed out

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.

Clear all filters active
Clear all filters active

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.

Sample dataset 1

For instance, in the dataset shown above, the Fruit filter has been applied to the Category column header.

Clear filter from greyed out
Clear filter from greyed-out

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.

Clear filter from active
Clear filter from active

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.

how to clear all filters from a table in Excel using right click
How to clear all filters from a table in Excel using right-click

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.

Drag and drop filter category out of the Filters box
Drag and drop filter category out of the Filters box

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.

Removed filter of a PivotTable
Removed filter of a PivotTable

Now, drag and drop it above into the Field List box to get rid of the field and filters applied through it.

PivotTable Analyze tab
PivotTable Analyze tab

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.

Clear filter icon
Clear filter icon

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:

VBA script to remove filters
VBA script to remove filters
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
Launch Macro dialog
Launch Macro dialog

Press the Alt + F8 keys together to launch the Macro dialog box.

Select the RemoveTableFilters macro from the list and hit Run.

Enter sheet names
Enter sheet names

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.

Clearled all filters using VBA
Cleared all filters using VBA

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃