Clear Excel Filters With a Single Click

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, and clear them later. But did you know that you can clear Excel filters with a single click?

Clear the Filters

After you apply filters on Excel table columns, you’ll probably want to see all the data again. To do that, it’s easy to clear one filter at a time, using the Clear Filter command on the drop down list in the column heading.

clearfilterqat03

Or 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:

  • Add a Clear button to the Quick Access Toolbar (QAT).
  • Select a cell in a pivot table, or an Excel table
  • Click the Clear button, and all the filters are cleared.

clearfilterqat012

It Works on Pivot Tables Too

To make this “Clear All Filters” tip even more useful, it works on pivot tables too!

If you use the Ribbon commands to clear a pivot table’s filters, it takes several clicks, because the Clear command is down a few layers.

  • First, go to the Analyze tab under PivotTable Tools
  • Then click Actions
  • Then click Clear Filters.

clearfilterqat022

But, if you add the Clear button to the QAT, you can  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.

______________________