How to Clear Filters in Excel

Last updated: August 19, 2026

In this article, we’ll walk you through various methods to clear filters in Microsoft Excel, ranging from basic techniques to advanced solutions.

Clearing a Filter vs Removing a Filter

Almost every filter question comes down to one distinction that Excel never explains. "Clear" and "remove" are two different commands with two different outcomes, and picking the wrong one is why people end up back on Google.

Rule of thumb: if your rows are back but the little arrows are still in the header, that is not a bug. You cleared the filter, which is almost always what you actually wanted.

How To Identify Active Filters

Before clearing filters, you need to know if they are applied:

• Look for the funnel icon in the column header.

  

• Check the status bar at the bottom of the Excel window; it may indicate that a filter is active.

  

Two more tells worth knowing. A plain dropdown arrow means the column can be filtered but currently is not, while a funnel means that column is doing the filtering. And the row numbers down the left edge turn blue and skip numbers wherever rows are hidden by a filter. Black, continuous row numbers with rows missing means something other than a filter is hiding them.

Different Ways to Clear Filters in an Excel Sheet

There are various ways to remove filters in Excel. We have explained each of them below:

1. Clearing filters from a single column.

If you only want to clear the filter on one column:

• Click the filter icon (funnel) in the header of the filtered column.

• Select Clear Filter from [Column Name] from the dropdown menu.

• The column will revert to showing all data.

Worth remembering that filters stack. If three columns are filtered, clearing one still leaves rows hidden by the other two, which is why this feels like it "did not work" when the real answer is that another column is still filtering.

2. Clearing all filters in a worksheet.

You can clear filters across the entire worksheet in two ways. They include:

• Using the ribbon:

  • Navigate to the Data tab on the Ribbon.

  • Click Clear in the Sort & Filter group.

• Using a keyboard shortcut:

  • Press Alt + A + C.

  • All filters will be removed instantly.

This clears every filtered column at once and leaves the dropdown arrows in place. Microsoft documents the underlying behaviour in its guide to filtering data in a range or table.

3. Removing filter drop-downs entirely.

To remove filter drop-down arrows from column headers:

• Using the ribbon:

  • Go to the Data tab.

  • Click the Filter button to toggle off filters.

• Using a keyboard shortcut:

  • Press Ctrl + Shift + L to toggle filters on or off. This will leave no filter drop-down arrow.

This is also the fastest way to wipe a complicated filter setup and start clean: press Ctrl + Shift + L twice. The first press strips filtering and every criterion with it, the second press puts fresh, empty dropdowns back.

4. Using VBA to clear filters.

Advanced users can automate the process using VBA (Visual Basic for Applications). If you can't write a VBA code, you can ask an AI tool like ChatGPT to write a code to clear all filters.

• Open the Developer tab (enable it from Excel settings if it’s not visible).

• Click Visual Basic and insert a new module.

• Copy and paste the following code:

  Sub ClearAllFilters()

  Dim ws As Worksheet

  For Each ws In ThisWorkbook.Worksheets

  If ws.AutoFilterMode Then ws.ShowAllData

  Next ws

  End Sub

• Run the macro to clear filters across all sheets.

Two things trip people up with this. First, ShowAllData throws runtime error 1004 if it runs when nothing is actually filtered, which is exactly why the If ws.AutoFilterMode guard above matters. Copy that line, not just the ShowAllData call. Second, AutoFilterMode only reports whether the dropdowns exist, not whether any criteria are set. If you want to test for an active filter specifically, check ws.FilterMode instead.

Excel Tables are a separate case, because a Table has its own filter object that ShowAllData does not reach. To clear those, loop the ListObjects collection and call .AutoFilter.ShowAllData on each one.

Read more: How to use VBA codes in Excel.

Excel Filter Keyboard Shortcuts

Worth committing the first two to memory. The rest are useful once you filter often.

Press Alt + A + C as three keys one after another, not held together. On Mac, use Cmd + Shift + F to toggle filtering, since the Alt accelerator sequences do not exist there.

On undo: Ctrl + Z does reverse filter changes, but only one criterion per press, and only within the current session. Once the file has been saved and reopened, undo cannot bring a filter setup back. Use Clear instead.

How To Clear Filters in an Excel Table

If your data is a formatted Excel Table rather than a plain range, clearing works normally but removing the arrows does not. This catches people out constantly.

• To clear the criteria: exactly as above. Data > Clear, or Alt + A + C. All rows return.
• To remove the arrows: Ctrl + Shift + L is unreliable inside a Table. Instead click any cell in the Table, open the Table Design tab, and untick Filter Button in the Table Style Options group.

One trap is worth flagging. Hiding the Filter Button does not clear a filter that is already applied. If you hide the arrows while a filter is active, the rows stay hidden and you have removed the only visible way to get them back. Always clear first, then hide the buttons.

If you would rather stop the range behaving as a Table altogether, see how to remove a table in Excel, which walks through converting it back to a normal range.

Why Is the Clear Button Greyed Out?

How To Clear Filters on a Protected Sheet

On a protected sheet, whether you can touch filters at all was decided when the sheet was protected, not now.

When protecting a sheet under Review > Protect Sheet, the owner sees a list of permissions to allow. Ticking Use AutoFilter lets other people use and clear filters that already exist. Ticking Sort covers sorting separately. If neither box was ticked, the filter controls are locked and the only route is to unprotect the sheet first.

The important limitation: even with Use AutoFilter enabled, users can only work with dropdowns that were already in place before protection was applied. Nobody can add filtering to a new column or toggle filtering off entirely on a protected sheet. If you are the file owner and you want readers to be able to reset the view, apply the filters first, then protect the sheet with Use AutoFilter ticked.

Rule of thumb: filter permissions on a protected sheet are set once, in advance. If Use AutoFilter was not ticked at protection time, no amount of clicking will clear that filter.

Rows Still Hidden After Clearing the Filter?

You cleared every filter, the funnel icons are gone, and rows are still missing. The filter was not the culprit. Work through these in order:

1. Manually hidden rows. Clearing a filter never unhides rows that someone hid by hand. Press Ctrl + A to select the sheet, then Home > Format > Hide & Unhide > Unhide Rows. Full walkthrough here: how to unhide all rows in Excel.
2. A collapsed group or outline. Look for small plus and minus buttons in the left margin. Click the highest numbered outline button at the top left to expand everything.
3. A second filtered column you missed. If you cleared one column by hand rather than using Data > Clear, another column may still be filtering. Use Alt + A + C to be certain.
4. Rows with a height of zero. These look hidden and do not respond to Unhide in some versions. Select the surrounding rows and set an explicit row height under Home > Format > Row Height.
5. A frozen pane hiding the top rows. Check View > Freeze Panes and unfreeze.

Troubleshooting Common Issues

Here are some issues you may encounter while clearing filters in Excel:

• Filters not clearing: Ensure the worksheet is not protected.

• Merged cells: Merged cells can interfere with filtering. Unmerge them before applying or clearing filters.

• Hidden rows: Clearing filters does not unhide rows manually hidden.

• The filter is missing rows you expect: blank rows split a range into separate blocks, and Excel only filters the block containing the active cell. Delete the blank row, or select the full range before switching filtering on.

• Numbers stored as text: a column mixing real numbers with text-formatted numbers filters inconsistently. Convert the column to a single data type first.

• Slicers and pivot tables: these do not respond to Data > Clear. Use the small clear filter icon in the top right corner of the slicer, or press Alt + C with the slicer selected.

Final Thoughts on "How To Clear Filters in Excel"

The whole topic gets easier once the two commands are separated. Clear gives your rows back and keeps the dropdowns, and Alt + A + C does it across the whole sheet in a keystroke. Ctrl + Shift + L switches filtering off completely and takes the arrows with it. Neither one can harm your data, since a filter is only ever a view. And if the rows are still missing after both, the problem was never a filter.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions

Related Articles

How to Remove a Table in Excel

How to Unhide All Rows in Excel

How to Unhide an Excel Worksheet

How to Fix Excel Formula Not Working Error

How to Remove Characters from Left in Excel