I mean how cool is that if you could just filter the data by cell selection instead of relentlessly checking and un-checking options in the the filter drop down
Well today’s blog post is going to make your life a hell lot easy!
Where is Filter by Selection?
Filter by selection was available in the menu in Excel 2003 until Excel decided that it not an important feature and chose to keep it aside. Well there is way to activate that
- Click on the little arrow in the Quick Access Toolbar
- Go to More Commands
- Under ‘Choose Commands from” pick up Commands not in the Ribbon
- Find Auto Filter (this is filter by selection) and add it to the Quick Access Toolbar (QAT)
- Click Ok
Now when you select any cell and click on AutoFilter in QAT the data gets filtered instantly
Pros and Cons
- Pros : The speed and flexibility is crazy. Since Auto Filter is in QAT, you can also access that using an ALT based shortcut. (In my case it will be Alt 5, because the auto filter is placed on the 5th position)
- Cons : You can only apply a basic filter criteria and not filter the records with sales more than 10,000. It also does not work on Excel tables (that you create using Ctrl T)
Do you think you’ll be using this often, let me know your feedback. I learnt this trick from Mr. Excel aka Bill Jelen
Must Read Posts on Filter Tricks
Other Simple and Smart Tricks
- Simple Charting Hacks
- 16 Time Saving Pivot Table Tricks
- Work with New Window to view multiple sheets at once
- Border Shortcuts in Excel
- Generate Serial Numbers
- Work on Multiple Sheets at Once
