Top 6 Excel advanced filter tips and tricks

Here are my top 6 Excel advanced filter tips and tricks. It is a great tool to speed up and optimize filtering processes in Excel, and it is a good reason to master that more.

I assume that you are familiar with the basic capabilities of this tool.

Here is a list of my top 6 Excel advanced filter tips and tricks in this blog post.

1. Use only a few columns in Excel advanced filter criteria range
2. Return only necessary Excel advanced filter results in a separate table. In other words, filter columns in Excel.
3. Excel advanced filter that filters records that contain the phrase
4. Excel advanced “does not contains” filter
5. Excel advanced filter with AND argument for the same column
6. Filter unique records in Excel by using the advanced filter

1. Use only a few columns in Excel’s advanced filter criteria range

One of the mistakes of inexperienced Excel users is the usage of unnecessary column names in the Excel advanced filter criteria range. All you need is precisely those column names necessary to define what you want to filter.
Here is what it looks like in the advanced filter criteria range.

2. Return only necessary Excel advanced filter results in a separate table. In other words, filter columns in Excel.

Imagine that you have a table with columns that are not necessary for the results. Prepare a table header that contains the names of those columns that you want to keep after in the results of the advanced filter.
After entering the range of the filter criteria and data in the “Copy To” box, enter the range with previously prepared column names. As the result, you will get a table with filtered only necessary columns.

3. Excel advanced filter that filters records that contain the phrase

If you want to filter with Excel advanced filter records that contain a certain phrase, try to use wildcard criteria. The asterisks symbol means that there might be any number of characters, but if you know a certain number of characters, use a question mark for each of them.
For example, here is how to filter any that contains the word “mountain”. The advanced filter is not case-sensitive.

4. Excel advanced “does not contains” filter

If you want to define that something is not in the advanced filter results, use the comparison operators. Here is an example of how to define to exclude records from the advanced filter results anything that contains a certain phrase.

5. Excel advanced filter with AND argument for the same column

If you want to create complex advanced filter criteria for the same column that should use AND operator, repeat the same column name in filter criteria. AND operator works only in the same row.
Here is how it looks.

6. Filter unique records in Excel

There is a check box that ensures that results contain unique records only. If this is the only goal, then you can use it without any criteria, and in the result, you will get only rows when records appear for the first time.
Here is how to filter unique territory names.

Advanced filter in Excel can significantly speed up your filtering capabilities.