When you’re unable to see all the data in a dataset or table in a Microsoft Excel worksheet, there could be active filters in the columns. Read this article until the end to discover various ways to clear all filters in Excel to dig out filtered data buried in filters.

Excel filters prove incredibly beneficial when delving into extensive datasets. They empower you to swiftly apply filtering criteria to your data, whether based on a single column or multiple columns, through an intuitive interface.

However, alongside their advantages, there are scenarios where it becomes imperative to initiate the process of eliminating all active filters in your Excel dataset. A pertinent example is encountering an Excel file from a coworker that already contains applied filters.

In such cases, only the visible data post-filtering is accessible. If your aim is to comprehend the entire dataset, it becomes essential to clear the applied filters. Instances of individuals neglecting this crucial step can subsequently lead to skewed data analysis outcomes.

What Is a Filter in Excel?

The Excel filter functionality provides a way to zero in on particular subsets of data, streamlining the process of locating desired information. To illustrate, consider a scenario where you possess a compilation of sales figures for assorted products.

Leveraging the filtering capability enables swift identification of high-performing products as well as those requiring some push. These filters extend their utility to both numeric and text-based data sets.

Moreover, their compatibility with other functions like Conditional Formatting and PivotTable enhances their value.

In a nutshell, filters emerge as a versatile instrument that significantly simplifies data manipulation within Excel. You can find the Excel Filter tool in the following locations:

Home > Editing > Sort & Filter

Data > Sort & Filter > Filter

How to Find Out Active Filters in Excel

Several straightforward methods exist for prompt identification of the application of filters to a dataset. The most effective way to verify this is to simply check the Excel Status Bar. It’s the gray strip positioned at the bottom-left corner of the worksheet.

The following visual inspections quickly reveal whether filters are in use:

1. The Status Bar displays Filter Mode when one or more filters are active on the worksheet.

2. If a filter is applied, you’ll see a message such as “3 of 5 records found”. This indicates that certain records are hidden due to filter application.

Upon removing or clearing the filter, these Status Bar indications vanish.

Another visual indicator lies within the column headers. A filter icon will be present on the right side of a column header cell to signify filter application to that column.

Reasons to Clear All Filters in Excel

Here’s why you may want to clear all filters in an Excel workbook or worksheet before starting your data analysis journey:

1. You’re working on a shared Excel file. Other collaborators may have applied filters that suit their work. The same filters may not be applicable to you. So, to apply your own filters, you must first clear all filters in the worksheet.
2. If you’re working on a really large workbook with many worksheets and each worksheet contains thousands of rows, filters will slow down the data analytics work.
3. You’ve accidentally applied a couple of filters to the dataset. Now, you’re unable to get back the unfiltered dataset because you’re not aware of how to clear all filters in Excel.
4. Removing filters is crucial for accurate data analysis, as leaving filters applied might lead to incorrect insights and conclusions.
5. When performing tasks like sorting, grouping, or calculations, starting with an unfiltered dataset ensures accurate results.
6. Keeping filters active can cause misinterpretation of data, leading to erroneous decisions based on incomplete information.
7. Clearing filters maintains the integrity of your data, preventing discrepancies between visible and hidden records.
8. Active filters can inadvertently cause errors when entering new data or making edits, leading to unintended outcomes.
9. Clearing filters establishes consistency in data analysis and reporting, as everyone starts from the same point.

So far, you’ve gone through a thorough knowledge of the filter tool in Microsoft Excel. Now, find below all the common and easy methods to clear all filters in Excel:

Clear All Filters From Column Manually

When the filtered table or dataset is small enough, you can try the following steps:

1. Click the filter icon on a column.
2. Click the Clear Filter From “Column Name” button on the context menu.
3. Repeat the above for all other columns that have the filter icon in the top right corner.

How to Clear All Filters in Excel From Home

Another way to remove all filters from an Excel table is by using two different options given in the Home tab by Microsoft. Let’s explore how the work below:

Using the Sort & Filter Tool

1. Go to the Home tab and navigate to the Editing block.
2. There, click the Sort & Filter button to expand its menu.
3. Click Clear to remove all the filters applied to the table.

4. If you want to turn off filtering, click the Filter icon.

Using the Clear Tool

1. Select the filter row or the column header row of the dataset.
2. Go to Home > Editing.
3. There, click the Clear drop-down menu.
4. On the context menu, choose Clear Formats.
5. The dataset will lose the filtering and the whole data will show up.

The above steps will also delete the cell formatting of the dataset. So, if you’re okay with formatting reset, try this method.

How to Use the Data Tab to Clear All Filters

You can also clear or turn off filtering of your Excel dataset from the Data tab. Here’s how:

1. Go to the Data tab.
2. Navigate to the Sort & Filter commands block.
3. Click Clear to remove all active filters instantly.
4. Click the Filter icon to turn off filtering.

Keyboard Shortcuts to Clear All Filters and Reapply

Excel is highly regarded for its rich keyboard shortcuts. You can do almost anything using the keyboard shortcuts. Find below three different shortcut keys to manage filtering in your Excel worksheet.

1. Highlight the filtered column using the arrow keys.
2. Now, press the Alt + Down Arrow key to open a context menu.
3. Press C to remove the filtering of the selected column.

Alternatively, to clear all active filters, simply press Alt + A + C.

If you simply want to turn off filtering, press Alt + A + T.

How to Use Excel VBA to Clear All Filters

So far, you explored various methods to clear all filters but most of these involve some sort of manual effort.

If you want to automate the process for a large workbook with many worksheets, you must depend on Excel VBA scripting.

If you know VBA scripting, then you’ll find the method really aligned with your skills. However, if you don’t know VBA scripting, don’t worry.

I’m going to explain below the methods in a step-by-step manner so you can also follow along with the steps in your own worksheet.

Before you can use the VBA scripts mentioned in this Excel tutorial, you must activate the Developer tab on your Excel desktop application. Because, this tab controls everything related to VBA macro, like accessing the VBA Editor or Macro dialog box.

Read this outstanding article to learn how to do it:

2 Ways to Add the Developer Tab in Microsoft Excel

Now that you’ve activated the Developer tab, let’s get started with the VBA scripting method:

1. Bring up the Excel VBA Editor by pressing the Alt + A11 keys.
2. Click the Insert button on the VBA Editor toolbar.
3. Click Module to add a new module.
4. Copy and paste the following VBA script inside the module:

Sub ClearAllFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
End Sub

5. Click the Save button to preserve the code for the future.
6. Close the Excel VBA Editor.

Now, you need to run the code to clear all filters on the selected worksheet. Here’s how:

1. Hit the Alt + F8 keys to bring up the Macro dialog box.
2. Select the ClearAllFilters macro.
3. Hit Run to execute the code.

Use this script to clear all filters from a table in the active worksheet:

Sub ClearAllFilters()
    Dim ws As Worksheet
    Dim tbl As ListObject

    ' Clear filters from regular ranges
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0

    ' Clear filters from tables
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            tbl.AutoFilter.ShowAllData
        Next tbl
    Next ws
End Sub

Clear All Filters From the Excel Workbook

You can use the following VBA script to remove all the active filters in all the worksheets of the workbook in less than a second. Also, the code would work on both normal datasets and tables.

Sub ClearAllFiltersInWorkbook()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range

    ' Clear filters from normal data ranges
    For Each ws In ThisWorkbook.Worksheets
        For Each rng In ws.UsedRange.SpecialCells(xlCellTypeVisible)
            rng.AutoFilter
        Next rng
    Next ws

    ' Clear filters from table data
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            tbl.AutoFilter.ShowAllData
        Next tbl
    Next ws
End Sub

Clear All Filters When Saving Your Excel File

To ensure everyone clears the filter after use, you can add the following VBA script into the workbook. Whenever someone saves the Excel file, the script will reset all the filters.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range

    ' Clear filters from normal data ranges
    For Each ws In ThisWorkbook.Worksheets
        For Each rng In ws.UsedRange.SpecialCells(xlCellTypeVisible)
            rng.AutoFilter
        Next rng
    Next ws

    ' Clear filters from table data
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            tbl.AutoFilter.ShowAllData
        Next tbl
    Next ws
End Sub

Clear All Filters When Closing the Excel File

The VBA script mentioned below will reset all the filters when you close an Excel file. So, the next time someone opens the workbook, they’ll see all the data of a table or dataset.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range

    ' Clear filters from normal data ranges
    For Each ws In ThisWorkbook.Worksheets
        For Each rng In ws.UsedRange.SpecialCells(xlCellTypeVisible)
            rng.AutoFilter
        Next rng
    Next ws

    ' Clear filters from table data
    For Each ws In ThisWorkbook.Worksheets
        For Each tbl In ws.ListObjects
            tbl.AutoFilter.ShowAllData
        Next tbl
    Next ws
End Sub

How to Use Office Scripts to Clear All Filters

Office Scripts brings a better automation option in Excel using the TypeScript programming language. Thus, you can integrate Excel with other Microsoft apps like Outlook, SharePoint, etc., using Office Scripts and Power Automate.

Also, Office Scripts runs on both the Excel desktop and web app.

Find below how to use this scripting tool to clear all filtering in Excel:

1. Click the Automate tab.
2. Click the New Script button inside the Scripting Tools section of Automate.
3. Paste the following code inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
    // Get the active cell.
    const cell = workbook.getActiveCell();

    // Get the tables associated with that cell.
    // Since tables can't overlap, this will be one table at most.
    const currentTable = cell.getTables()[0];

    // If there is no table on the selection, end the script.
    if (!currentTable) {
        console.log("The selection is not in a table.");
        return;
    }

    // Get the table header above the current cell by referencing its column.
    const entireColumn = cell.getEntireColumn();
    const intersect = entireColumn.getIntersection(currentTable.getRange());
    const headerCellValue = intersect.getCell(0, 0).getValue() as string;

    // Get the TableColumn object matching that header.
    const tableColumn = currentTable.getColumnByName(headerCellValue);

    // Clear the filters on that table column.
    tableColumn.getFilter().clear();
}

4. Click the Save script button.
5. Click the Run button to execute the code.

📝 Note: At the time of writing, the Office Scripts functionality will only be available to you if you’ve subscribed to Business Standard or a better Microsoft 365 subscription. If you’re using Microsoft 365 offered to you by your employer, they must enable the feature from their Microsoft 365 admin portal.

Conclusions

Now you know how to clear all filters in Excel. Depending on the size of your Excel workbook, use the best-suited method from the above.

Remember to clear out all the filters used by you during the work so others can get a full view of the file when they open it. Also, tell your colleagues and peers to do the same when they use filters in an Excel worksheet.

Don’t forget to leave a comment below about your experience when following along with the above techniques to remove filters in Excel.