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.
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.
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.
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 …
Under “Choose commands from” Data Tabs and select “Clear” Add it to the Customized Quick Access Toolbar.
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.
If all columns are reset to default, the “Clear” button will be greyed out.
There you have it, a quick way to reset all Column filters in excel.
Co-founder of Next of Windows and a cool geek 🙂
Latest posts by Jonathan Hu (see all)
Last updated: 01/19/2016