Using the good old excel filters is one of the most common operations you must be doing on your data. And of course there are shortcuts and hacks that you can use to filter your data faster and smarter
In today’s tutorial I’ll share with you some of cool shortcuts and tricks you can use to while applying filters Excel
First.. you must know Auto Filter !
Auto filter (also referred as Filter by Selection) is one of the hidden gems of Excel. I wrote on post on how to activate and use auto filter a while ago. If you haven’t read it, I’ll strongly recommend you to go check it out. If you are more of a video person, you can quickly grab the video underneath 🙂
Caution: This (auto filter) trick does not work with Tables
Now that you know auto filter, I am going to share 3 auto filter tricks
Shortcut 1 – Filtering a Range with Dates
Let’s say that I would like to filter all dates on or after 1 Jan 2007.
- Go to the end of the date range and type in >1 Jan 2007
- Use auto filter and
- You’ll have the data filtered with dates after 1 Jan 2007
Shortcut 2 – Filtering a Range with Numbers
Imagine a list of numbers. What if you want to filter all numbers greater than 5000?
- Go to the end of the range type in >5000
- Use auto filter and
- You’ll have the data filtered with numbers more than 5000
A few variations
The process remains the same but what you type in the last cell of the range can fetch you a different (or let’s call it a smarter) results
|Range Containing||Last Cell Filter||Filter Result|
|Text (or Numbers stored as text)||???||Filter for all 3 letter words|
|Text||?a*||Filter for words which have any first letter and second letter “a”|
|Text||* Corp||Filter for words which end with ” Corp” (with a space before Corp)|
|Text||>=s||Filter for words which start with or after the alphabet “s”|
More of a video guy ?
Before I close, I would like to credit Bill Jelen (aka Mr Excel) for sharing this trick
A few more shortcut tactics..
- How to Master Keyboard Shortcuts
- Fill Handle Shortcuts
- 100+ Excel Shortcuts
- Shortcuts for Financial Modeling
- 60+ Shortcuts for VBA Macros
- 60+ PowerPoint Shortcuts
- 30 Evergreen Productivity Hacks in Excel