Excel How To Clear All Table Header Filter At Once

Excel is excellent in data analysis and data visualization if you have a large set of data needs to be graphically visualized. Excel can do that for you rather quickly, one problem with a large set of data is the amount of repetition when filtering through the dataset is reset individual column becomes tedious. This is true when there are multiple columns and millions of rows to sort through.

2015-12-29_1324

The usual workflow if you are going to reset all the columns are select each column header and expand it’s dropdown and find the option “Clear Filter From `Column header`” and repeat that for all the columns. As you can imagine if there are tons columns, it’s a painfully tedious process to go through.

2015-12-29_1330

There is also a shortcut that will do the reset with one click, but it’s not obvious and it’s located under DATA tab. Depends on where is your cell currently selected, by pressing Clear it will clearing all filters or the specific columns.

2015-12-29_1328

We can do an extra step to enhance the workflow by adding the Clear button to the Quick Access Toolbar. If you click the dropdown of the Quick Access Toolbar > More Commands …

2015-12-29_1328_001

Under “Choose commands from” Data Tabs and select “Clear” Add it to the Customized Quick Access Toolbar.

2015-12-29_1329

You will see the button appear on the very top of the toolbar. If you have any cell selected, by clicking this button it should reset all Column’s filter.

2015-12-29_1329_001

If all columns are reset to default, the “Clear” button will be greyed out.

2015-12-29_1329_002

There you have it, a quick way to reset all Column filters in excel.

Jonathan Hu

Programming by day, Web Development, Canucks & Movies for spare time!
Co-founder of Next of Windows and a cool geek 🙂

Last updated: 01/19/2016

Posted in: How to , Office
Discover more: , ,