#Excel Add current selection to filter. What it does?

Get multiple filter criteria with Excel Auto-Filter

This post is in response to the following Mr.Excel video

where Mr. Excel Bill Jelen solved the problem with Advanced Filter.

Indeed I got a similar question from a colleague recently for filtering multiple criteria on a single column.  This is perfect timing to write this post to demonstrate a less-known feature of Auto-Filter.

First of all, have you ever noticed the “Add current selection to filter?

Example Tip - Add current selection to filter

Maybe yes maybe no.  Even though you have seen it before, you may have no idea what it does.  Let’s explore it now.

Here’s the situation

We have a list of account numbers (that are stored as text).  What we want to achieve is to filter all account number that starts with either 6, 7, or 8.

Excel Tip - Add current selection to filter 1

Solution

You may download Sample File to follow along.

Step 1 – Filter those starts with 6

  • Make sure you have Auto-Filter turned on
  • On the unfiltered list, click the dropdown on the header
  • In the “Seach box”, input 6*  (* is the key in this step.  * is the wild card for any text of any length.  In short, 6* here means anything starts with 6)
  • OK

Excel Tip - Add current selection to filter 2

Step 2 – Filter those starts with 7 and Add current selection to filter

  • On the filtered list, click the dropdown on the header
  • In the “Seach box”, input 7*
  • Check the “Add current selection to filter” (Note: This is exactly where the “Add to current selection” come in place.)
  • OK

Excel Tip - Add current selection to filter 3

WOW.  I have a filtered list with account number starts with 6 or 7 now!

Step 3 – Filter those starts with 8 and Add current selection to filter

  • On the filtered list, click the dropdown on the header
  • In the “Seach box”, input 8*
  • Check the “Add current selection to filter” 
  • OK

As simple as this! 🙂

If you want to add more filter criteria to the list, simply repeat the steps.  The thing is you HAVE TO remember to check the “Add to current selection to filter” every time you want to add criteria.  As you see, this is a step-by-step approach to add multiple criteria to a filtered list.  You may find it

  1. Not efficient if you have too many filter criteria (in this case, Advanced Filter should be a better alternative)
  2. A bit frustrated when you forgot to check the “Add to current selection to filter“, especially when it happens for the last criteria… Trust me.  I experienced that a lot.  😛

What happens if we forget to check the “Add to current selection to filter“?

Excel Tip - Add current selection to filter 4.gif

Excel will replace the filtered list, instead of adding it to the current list.

Tip: In this case, don’t panic.  Simply undo and repeat the steps.

Although it is not perfect, it does serve the purpose of having multiple filter criteria within the auto-filter itself.  What is your use case for this?  Please share with you by leaving comments.

 

Here’s the video on my YouTube channel.  Please turn on CC for English subtitles.

 

Want more examples of advanced filter? Here you go: Filter a list of items from a long long list

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.