# A simple but practical tip of using Advanced Filter

I came across a blogpost from Jon of Excel Campus (one of my favorite Excel sites) last week.  In his post, he showed how to filter for a list of items using a reverse partial match lookup.  Obviously, it is a formula approach.  From his post, you will see how Excel formula can save you from tedious works at workplace.

In this post, I am going to replicate Jon’s example and achieve similar results without a single formula.  Thanks to Advanced Filter!

Yes.  It’s right there – just next to the Filter that most people have used it.

But… I believe there is a high chance that you may have never used it before… 🙂  If that’s the case, please continue to read and you will find it super helpful.

Although with the term “Advanced”, Advanced Filter is indeed not difficult to use at all.

## Situation

What we have is a list of 1000+ company names.  What we need is a list of companies that contains the word “bank” or “Telecom”.  You may wonder why we don’t just do it with normal Filter….

## How to do it with a Filter?

### 1) The silly way

As I said, it is a tedious work.  Some people will browse the list from the filter and click the check box one by one, by eyeball.  Yes by eyeball.

Seriously?  No way… @_@

### 2) Using the Search box in Filter

Well, if you are an experienced user of Filter, you know that you may search for what you need by inputting the key words in the search box.  See below:

While you input “bank”, you will see immediately the filter is smart enough to show you only items that contain “bank”.  What’s is even better is you can ADD other search terms to your filter list – by checking the “Add current selection to filter“.

WOW, that’s good enough you may think.  Well… if we are dealing with only two items, yes it is.  Think about you need to deal with 10 items?  Then you need to repeat the steps 9 times.  That is tedious.  What’s worse?  The frustrating moment comes when you forget to check the “Add current selection to filter” on your final search term.  You know what I mean.

### 3) The proper way – Advanced Filter

#### Setting up

Before it works, we need to set a “Criteria Range” to tell Excel the list of names that we want.  Let’s set the Criteria using D1:D3

Note: The title of the Criteria Range, i.e. D1 in the above example, must be identical to the tile on the column we want to filter, i.e. B1 in the above example.

#### In action

Go to Data tab –> Sort & Filter –> Advanced

For demonstration purpose, I select “Copy to another location”.  You may, of course, “Filter the list, in place”, i.e in the original source location.

### Here we go…

Wait… the matching list should be longer…

Apparently, the Advanced Filter only retreturnsmpany names that begin with “Bank” or “Telecom”…

### A little twist – Using wildcard *

By wrapping the text string with asterisk (*), Excel now interprets the criteria as anything that contains “bank” or “telecom”.

Tip: * is the wildcard for any number of characters

### Here we go…

It is done and as simple as this…… if you need a partial match.  What if you want to differentiate Telecom from Telecommunications?

As you see from the above result, company names with “Telecom” and “Telecommunications” got filter altogether.  Indeed, that is what we instructed Excel to do… filter those contain the text string “telecom”.

To get the names with the word “Telecom” but not “Telecom*”, you will need an intermediate step.

### Another twist – Adding space before & after the text string ” Telecom “

This simple twist instructs Excel to look for (space)telecom(space).  With this trick, the word “telecommunications” does not match the filter criteria.  Of course, this twist assumes each word be separated by space.

So are we done? Not yet… as the trick also excludes two possible scenarios:

2. Names end with ” Telecom”

## Here comes the Advanced part

To cater for the above scenarios, we need to add two more criteria into B4:B5.  That means we need to extend the Criteria range to D5.  See below:

Note: In the sample file, you will see the filtered result at the end with two dummy names, one starts with Telcom; one ends with Telcom.

To be exact, the following are input as criteria:

```B2 - *bank* 'Contains the string bank (any position)
B3 - * telecom * 'Contains the word telecom (in between only)
B4 - ="=Telecom *" 'Starts with the word Telecom
B5 - ="=*Telecom" 'Ends with the word Telecom```
 Note: The * at the end is not necessary.  I put it there to make the last space visible

The input for B4 and B5 are not straightforward.  Nevertheless, once you learn the construction and do some practices, it will turn into another piece of cake… 🙂

## To Recap

If you need to filter for a list of items (strings) regardless position in the source data, i.e. partial match, you may use Advanced Filter to do the job easily, as long as you remember to put * before the strings in your criteria range.  E.g.

```*bank
*telecom
*technology
*holdings```

However, if your requirement is a bit complicated, you may need to know the special construction for

```="=ABC" 'Contains ABC only
="=ABC*" 'Starts with the string ABC
="=*ABC" 'Ends with the string ABC
="=ABC *" 'Starts with the word ABC; note the space
="=* ABC" 'Ends with the word ABC; note the space```

Final note: Advanced Filter is case-insensitive

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.

### 5 Responses to Filter a list of items from a long long list

1. Sabine says:

Hello MF,
I like the filter functions pretty much and use them very often. They are really great! But in older versions the way is a little bit different. You get Auto Filter on Data -> Auto Filter. Then mark the headline of your table. Select a column and click on “customized”. And there you go! You may select your search term under “contains” or “does not contain” in combination with an ‘*’ or ‘?’. It’s a real great thing. And one of the best is, that you can save your filtered view and use it the next time whenever it is needed. Very handy, if you are working with data requests.

Sabine

Like

2. excelesquire says:

Great solution and great post! I’ve tackled this kind of problem with a SUMPRODUCT formula, but this is a much more straightforward approach.

Like

• MF says:

Thanks. Glad that you like it !

Like

3. Billy says:

Hey, great post! I often forget that advanced filtering is there but it’s so useful.

Like

• MF says: