How to Use Advanced Filter in Microsoft Excel

You have a large dataset on Excel, but you want to filter out some data and make a new dataset. This can be done using the Advanced Filter feature on Microsoft Excel. In this post, we will share practical examples of how to use Advanced Filter in Microsoft Excel.

What is Advanced Filter in Microsoft Excel?

This feature allows you to filter data based on complex criteria using multiple conditions, including text, numbers, and dates. You can specify criteria in a separate range of cells or directly in the Advanced Filter dialog box.

The Advanced Filter also provides options to copy filtered results to a new location, filter data in place, and extract unique records from a dataset. Additionally, you can use wildcards, such as asterisks and question marks, to match partial text values in your criteria.

Here is a short guide to the Advanced Filter feature on Microsoft Excel.

What is the difference between Regular Filter (AutoFilter) and Advanced Filter?

Before you start using the Advanced Filter, you should know that there is a regular filter that is easier to use and may be able to get your job done.

Suppose you want to filter all the data in which the client’s name is Raghu, and the invoice amount is more than Rs. 1000. When using the regular filter, you will need to first filter for Raghu and then use the new dataset to again filter the dataset for invoice amount more than Rs. 1000. By using the Advanced Filter; you can do this in one go.

How to Use Advanced Filter in Microsoft Excel (with examples)

Here are some prominent use cases in which you can use Advanced Filter and how to do it.

1. Use-case #1 – Getting a unique list without any duplicates.
2. Use-case #2  – Getting a new list based on several criteria.
3. Use-case #3  – Creating a new list using wildcard characters.

Make sure to check each example to understand clearly its working

1] Use-case #1 – Getting a unique list without any duplicates.

Suppose you have a dataset in which the information is repeated in different cells. You want to eliminate all the duplications (the duplication can be the result of an error in the data entry or a result of data compilation, or for any other reason).

While there is another option in Microsoft Excel (Excel 2007 onwards), this option will alter your existing dataset. Advanced Filter lets you keep your existing dataset and extract the unique list to a new location on the spreadsheet.

Suppose you have the following spreadsheet:

You want to eliminate the duplication and get another list in another location.

To do this –

• Select all the cells, including the headers.
• Go to the Data tab > Sort & Filter > Advanced (You can also use the keyboard shortcut – Alt + A + Q). This will open the Advanced Filter dialog box.

In the Advanced Filter dialog box, you will see the following options:

• Action: Click on the ‘Copy to another location’ option.
• List Range: Ensure it specifies the dataset from which you want to extract unique records. Ensure that all the headers in the data set are included.
• Criteria Range: Leave this empty.
• Copy To: Specify the cell addresses where you want to get the new list.
• Copy Unique Records Only: Check this option.

This is how the dialogue box should look in our example:

This will give you a list of unique records in another location.

2] Use-case #2  – Getting a new list based on several criteria.

The primary utility of the Advanced Filter lies in the fact that you can use several complex criteria to form a new dataset.

Suppose you have a dataset that looks like this:

Now, you want a new dataset where the region is “US” and the amount is more than “5000”.

Here is how to do it using Advanced Filter:

• The first step is to specify the criteria. You can copy the headers and paste them into another location.
• Then you insert the criteria that you want in the cells below. In this case, since we want the region to be US, we will write “US” in the region column, and since we want the amount to be more than 5000, we will write “>5000” in the amount column.

This is how it will look like:

• Select the first data set and open the Advanced Filter dialogue box (as shown in the previous example).

In the Advanced Filter dialog box, use the following details:

• Action: Click on the ‘Copy to another location’ option.
• List Range: Ensure it specifies the dataset from which you want to extract unique records. Ensure that all the headers in the data set are included.
• Criteria Range: Specify the dataset address in which you have input the criteria. In this example, it will be F1:I2.
• Copy To: Specify the cell addresses where you want to get the new list.
• Copy Unique Records Only: Check this option.

This is what the dialogue box will look like:

• • Click “OK,” and the final results should look like this:

Use-case #3  – Creating a new list using wildcard characters.

You can explain your criteria for a new dataset to Microsoft Excel using wildcard characters.

The following wildcard characters can be used:

• * (asterisk) – This represents any number of characters. For example, ca* could mean cat, careful, cannot, carp1e4, etc.
• ? (question mark) – This represents one single character. For example, Ca??ot could mean Carrot or Cannot.
• ~ (tilde) – This is used to identify a wildcard character (~, *, ?) in the text.

How do you use these wildcard characters to make a filter?

Suppose your spreadsheet looks like this:

You want a new dataset where only clients’ names that start with “A” should be included.

You enter the criteria using the wildcard like this:

Since “A*” will mean any name that starts with an A, this criterion is enough to create the desired list. Similarly, you can use the other wildcards to create new datasets using your desired criteria.

As explained in the previous examples, the other steps for creating the Advanced Filter will remain the same.

Also Read: Manage Named Ranges Better with Name Manager in Excel

Conclusion

Advanced Filter is a powerful tool in Microsoft Excel that allows users to filter data based on specific criteria. It allows users to easily extract the required information from large data sets, saving time and effort. With the Advanced Filter, users can filter data using multiple criteria and apply complex logical operations to narrow down their results. Whether sorting, counting, or analyzing data, the Advanced Filter in Microsoft Excel is essential for anyone dealing with large amounts of data.

The world runs on Microsoft Excel. Learning to use Microsoft Excel can be a gift that keeps on giving. The Advanced Filter option is just one example of the many excellent features of this software. We hope you have enjoyed this tutorial and found what you were looking for.