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!
Where is 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.
You may download a Sample File here to follow through.
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
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.
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:
- Names start with “Telecom “
- 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… 🙂
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
Please download the Sample File to practise.
I just found this post last night as I have been looking for a way to put my large searches into my extremely large data and I am in LOVE! What I really am looing for, and hope you can help, is finding a way to do this same concept with Pivot Tables? I work with extremely large amounts of data (pay periods, orders, etc.) and use a lot of pivot tables. I would like to be able to type the names of the employees that I need data for and pull only those selections in the pivot drop down without having to add multiple selections one at a time (as their names are never similar or grouped together). If you have a way to do this same function to a pivot drop down, I would appreciate it greatly.
Would you consider adding a helper column to the data table for the pivot, you can then simply add a report filter to your pivot table. This should be the simplest way.
If adding column is not feasible for whatever reason, GETPIVOTDATA could be a good alternative.
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.
Great solution and great post! I’ve tackled this kind of problem with a SUMPRODUCT formula, but this is a much more straightforward approach.
Thanks. Glad that you like it !
Hey, great post! I often forget that advanced filtering is there but it’s so useful.
Thank you Billy! Glad that you like it. Sometimes we forget the best is just around the corner. 😁