Clear Excel Filters With a Single Click

I use Excel tables in almost every file that I build, these days. They come with a default filter, and it’s easy to sort and filter the table’s data with those drop down lists.

clearfilterqat03

Clear the Filters

When you want to see all the data again, it’s easy to clear one filter at a time, or you can go to the Data tab, and click the Clear button, to clear all the filters in the active table.

clearfilterqat04

Make It Easier

If you don’t have time for extra clicks, when you’re clearing filters all day long, here is a quick way to clear them. Thanks to AlexJ, who shared this tip. You can find more of AlexJ’s Excel sample files on my Contextures site.

To make this a one-click process, you can add a Clear button to the Quick Access Toolbar (QT). Then, select a cell in a pivot table, or an Excel table, click the button, and all the filters are cleared.

clearfilterqat012

It Works on Pivot Tables Too

To make this tip even more useful, it works on pivot tables too!

As you know, if you use the Ribbon commands to clear a pivot table’s filters, it takes several clicks. First, you go to the Analyze tab under PivotTable Tools, then click Actions, and then click Clear Filters.

clearfilterqat022

But, if you add the Clear button to the QAT, you use it to clear the active pivot table too. Think of all the time that will save you!

Video: Quickly Clear Pivot Table Filters

Watch this short video to see the steps for setting up the button, and clearing the filters.

Or watch on YouTube: Quickly Clear Filters in Excel Tables and Pivot Tables

______________________

You may also like...

9 Responses

  1. Khushnood Viccaji says:

    Very nice tip Debra !
    I already have two icons for “Filter on active cell value” and “AutoFilter ON/OFF” on my QAT.
    This will be the cherry on top :-)

    Now if only there was another button to clear the filter applied only on the active column.
    That is, if I have applied filters on multiple columns, and wish to clear only the active-cell-column’s filter, it would be nice to be able to do that with a macro or a single-click ;-)

  2. Goutam says:

    Alt+A C = fastest way to clear the filters.

  3. Jon Acampora says:

    Thanks for the reminder on that shortcut Goutam. I have the clear filters added to the QAT as Debra suggests. All buttons on the QAT are assigned a keyboard shortcut (Alt+QAT button number). So if your clear filters button was in positions 1-9, the keyboard shortcut for the QAT would technically be faster. One key instead of two. But that’s just being nit picky… :-) Either way it’s one of the shortcuts I use most often. Thanks!

  4. Dominic says:

    Thank you. Thank you. Thank you. You’re a gem.

  5. Shashank says:

    Thanks for d tip. But how to do this when we are reading the Excel in Perl script?

  6. Ankit Sengar says:

    how i add filter in in one cell only

  7. Tanoj says:

    When we are working on Excel sheet. chosen filter option in same sheet for three columns ,if we want to remove one filter among three without disturbing other. please advice short cut.

    Regards,
    Tanoj

  8. Saroj says:

    Thank you. I’m working a lot with filters and I go to filters 2 3 steps deep. It was a pain in fingers to clear each filter with the mouse click. Now with the macro, I can clear them all with keystroke.

Leave a Reply to Khushnood Viccaji Cancel reply

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