Advanced filters. Who knew?

I had a real face palm moment the other day which, having no shame, I thought I’d share.

 

I imagine we all know that when using an advanced filter you can use standard criteria with a criteria range (headers match the column names and you enter values as the criteria for each column) and you can also use formulas as criteria when things are more complicated (in this case the headers should NOT match a column header. I usually just leave them blank).

 

Remarkably, it had never occurred to me that you could combine the two at once. Yes, I know – I’m an idiot. But there you go. You can. It’s actually really useful – especially if you want to have multiple options on one or more fields while using several criteria fields, where with regular criteria you would have to have a row for every combination of options you wanted. This is particularly useful if you are using code to filter data and want to allow users to select any combination of options.

For example:

Raw data file:

 
 
Simple advanced filter:
Note that Item1 filter only applies to Jim

 
 
Corrected the criteria so Item1 applies to both:
 

 
 
Adding more options and you can see how the criteria rows start to multiply:
 

 
 
So let’s use a formula as criteria (note row 9 is the first row of data, NOT the header row):
 

 
 
And finally, let’s combine the two methods:
 

 
 
I leave it to you to work out what the criteria range would look like for this permutation if you didn’t use formulas… 🙂

2 thoughts on “Advanced filters. Who knew?

  1. Pingback: Help Needed - VBA Advanced Filter

  2. Pingback: How do you get an advanced filter to work in Excel, with OR values?

Leave a Reply

Your email address will not be published. Required fields are marked *