Advanced Filter allows us to quickly filter based on several criteria that are predefined in a separate table.
Advantages of Advanced Filter over “normal” filter:
- No need to choose each item we want to filter (imagine wanting to filter for 100 values out of 1,000), we can create a table instead.
- Allows us to filter based on several columns at once, using “AND” & “OR” operators.
- AND – all conditions have to be met
- OR – At least one condition has to be met
Advanced Filter example
In the table below, we would like to filter for people who live in London, Manchester, Glasgow or Cardiff:
Of course, you can manually tick the 4 cities, but what if you had to filter for 50 cities?
Let’s use Advanced Filter to do this!
First step – We will create a table with one column, where we will list the cities for which we wish to filter. Above that list we will put a header.
Important: For Advanced Filter to work, we have to give our new column the same header as in the table we are filtering, for our example that will be “City”:
Second step – We will go the the “Data” ribbon and click on “Advanced”
Third step – We will now select the ranges for our list range (the range we want to filter) and criteria range (the table we created in the first step):
Success! Your table should now look like this:
To un-filter, simply click on “Clear” in the “Data” Ribbon:
And now let’s see it in action:
Advanced Filter with multiple columns
Besides filtering for multiple values, we can also use Advanced Filter to filter using several columns.
Filtering using AND logic:
This filter is useful when we want all the criteria to be met, for example:
We want to find all the people who are named Alan and like chess. How will we do this?
Answer: we’ll create a table that uses “AND” by putting all the criteria values in the same row.
We asked the Advanced Filter to show us all the people who are named Alan AND like Chess. We did that by putting all the criteria in one row and using headers that match the original table.
And here’s the final result:
Filtering using OR logic:
This filter is useful when we want at least one of the criteria to be met, for example:
We want to find all the people who are either named Laura OR live in Leicester. How will we do this?
Answer: we’ll create a table that uses “OR” by putting all the criteria values in separate rows:
We asked the advanced Filter to show us all the people who are either named Laura OR live in Leicester (or both!). We did that by putting all the criteria in separate rows and using headers that match the original table.
Here’s the result:
Filtering numerical values using Advanced Filter
We can use Advanced Filter when we want to filter for all numerical values which are larger/smaller than a specific value.
We would follow the same steps as in previous examples, except in the criteria we will add “<“ or “>”.
And the result will be:
Tip – You can also use these operators:
<> – Does not equal to…
>= – Equals to or larger than…
<= – Equals to or smaller than…
Using wildcards in Advanced Filter
Excel Wildcards can be extremely useful when filtering complex tables.
We can use the “?” and “*” wildcards combined with Advanced Filter to do magics!
The ? (Question mark) wildcard
When we use “?” wildcard, Excel knows we want it to guess ONE character.
For example, if we look for the first name “B?n”, it will return a list filtered for first names that have the sequence b?n within them:
The result will be:
The * (Asterisk) wildcard
When we use “*”, Excel understands that we want it to guess an unknown number of characters – Anything between zero or more characters.
For example, if we look for the First Name “D*d” it will return a list filtered for first names that have the sequence “D*d” in them:
And the result:
Creating a new table from the filtered range
We learned how to filter a table, but what if we want to create a new table from the filtered data?
Simply click “Copy to another location” and mention where you want to have the new table in the “Copy to” field:
Warning – When you copy the filtered list to another location, you can’t undo (ctrl+z)
Practice Advanced Filter
Now, time to practice Advanced Filter in Excel!