How to clear filters from all worksheets in active workbook in Excel?
When working with an Excel workbook that contains multiple worksheets, it's common to use filters on various sheets to help analyze data effectively. However, after applying filters across several sheets, you may later need to remove all those filters to return your worksheets to their original, unfiltered state. Doing this manually—by switching to each worksheet one at a time and clearing filters individually—not only takes time but also increases the risk of missing a worksheet.
This task often arises in scenarios where you receive a workbook from coworkers or consolidate data from several teams, and you want to ensure all filters are cleared before beginning your own analysis or preparing reports for sharing. Manually handling each worksheet can be inefficient, especially for large workbooks containing dozens of sheets. Therefore, finding a quick and reliable way to clear all filters from every worksheet in your active workbook can save considerable time and effort, while also reducing the risk of overlooking any filtered data.
Below, we introduce a practical method to accomplish this in just a few steps. For advanced or alternative techniques, such as using formula-based approaches or leveraging Excel add-ins, please refer to the additional solutions at the end of this article.
Clear filters from all worksheets in active workbook with VBA code
Clear filters from all worksheets in active workbook with VBA code
To efficiently remove all filters from every sheet in your current workbook, you can use a VBA script. This approach is suitable for those comfortable running macros in Excel and is especially effective in workbooks that combine different types of structured data.
Before starting, please save your work, as running VBA will make bulk changes across your workbook.
1. In the workbook where you want to clear filters, press Alt + F11 simultaneously to open the Microsoft Visual Basic for Applications window. If you are using Excel for the first time, you might need to enable the Developer tab from the options menu.
2. In the opened Microsoft Visual Basic for Applications window, go to Insert > Module to create a new module. Then, copy and paste the following VBA script into the module window. Refer to the screenshot below for reference:
VBA code: Clear filters from all worksheets in active workbook
Sub Clear_fiter()
'Updated by Extendoffice 20250902
Dim xAF As AutoFilter
Dim xFs As Filters
Dim xLos As ListObjects
Dim xLo As ListObject
Dim xRg 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 xRg = xLo.Range
xIntC = xRg.Columns.Count
For xF2 = 1 To xIntC
xLo.Range.AutoFilter Field:=xF2
Next
Next
Next
Application.ScreenUpdating = True
End Sub
3. Press the F5 key or click the Run button to execute the code. Upon execution, all filters—whether standard range filters or table-based filters—across all worksheets in your current workbook will be cleared immediately.
Ensure you have saved your workbook before running macros, as actions made by VBA cannot be undone using Ctrl + Z.
Related articles:
- How to clear filter cache (old items) from Pivot Table in Excel?
- How to clear filters when opening, saving or closing workbook in Excel?
- How to filter data based on checkbox in Excel?
- How to fill series of numbers in a filtered list column in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in