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.

  1. Go to the end of the date range and type in >1 Jan 2007
  2. Use auto filter and
  3. 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?

  1. Go to the end of the range type in >5000
  2. Use auto filter and
  3. 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..

  1. How to Master Keyboard Shortcuts
  2. Fill Handle Shortcuts
  3. 100+ Excel Shortcuts
  4. Shortcuts for Financial Modeling
  5. 60+ Shortcuts for VBA Macros
  6. 60+ PowerPoint Shortcuts
  7. 30 Evergreen Productivity Hacks in Excel

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI