## 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?

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.

## Solution

### 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

### 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

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 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