Excel Advanced Filter (5 Useful Applications)

Microsoft Excel has an awesome feature called the Advanced Filter. It helps us to extract data according to certain categories. In this article, we will know in detail about Excel Advanced Filter with 5 useful examples. So, without any further delay, let’s proceed.

Download Practice Workbook

Get this sample file and try it yourself.

Regular Filter vs. Advanced Filter in Excel

Though both the terms look similar, there are certain differences between regular and advanced filters. Let’s check them at a glance.

• Advanced Filter helps to extract filtered data to other locations simultaneously while the regular filter cannot.
• The Regular Filter is confined to the listed criteria. But the Advanced Filter allows you to work with a complex range of criteria.
• Advanced Filter is very useful for extracting unique records from the dataset.

5 Useful Applications of Excel Advanced Filter

Here is a sample dataset on the public search of coffee-type Keywords. It shows the Search Volume and Traffic percentage of each Keyword as well.

Now, let us apply Advanced Filter to this dataset for different situations.

1. Apply Criteria in Excel Advanced Filter

The Advanced Filter is not only a tool in the Excel Ribbon but also a command that can be performed in various ways. Here we will see how to apply different criteria using the advanced filter.

1.1 One Word Criteria

In this first example, we will find one specific word with an advanced filter.

• First, select a cell within the data that you want to filter.
• Then, go to the Data ribbon and select Filter from the Sort & Filter group.

• Following, click on the Filtering icon at the top of the column and type the word (I have typed “best”) in the Text Filters field.

• Click OK. You will get the filtered data like the image below.

Read More: How to Use Advanced Filter If Criteria Range Contains Text in Excel

1.2 Two Words Criteria

If you want to filter the data for two words, the above method will not work. But, using the same Filter icon, you can filter the data records for two words. For this follow the steps below:

• First, the Text Filters command is in the drop-down and then click on the Custom Filter command.

• Then, a Custom AutoFilter dialog box will appear.
• In the dialog box, you can filter a table for two words. In the first and second drop-down, I select contains.

• In the second drop-down, on the right-side field, I type best and machine.

• Now, you see there are two radio buttons in the dialogue: And and Or.

Using “And”

• If you select “And”, then filtered keywords will have both the words “best” and “machine”.

Using “Or”

• Now see what happens when I select the “Or” radio button.

Read More: Apply Advanced Filter Based on Multiple Criteria in One Column in Excel

1.3 More Than Two Words Criteria

Sometimes, data analysis demands filtering data in complex ways. You might want to filter your data in a complex way with the following criteria:

• The Keywords column will have “best”, “machine”, and “reviews”.
• Search Volume column values will be greater than or equal to 200.
• Traffic column values will be less than or equal to 1.5.

Now let us filter this dataset according to the provided criteria.

• In the beginning, go to the Data ribbon and under the Sort & Filter group click on the Advanced command.

• Then, input the data you want to filter in the List range field.

• Following, in the Criteria range field, input the range where you have made your criteria.

• Lastly, press OK and you will see the result.

Read More: Excel VBA Examples with Advanced Filter Criteria (6 Cases)

2. Advanced Filter Using Excel Formula

We can also apply the advanced filter with the help of a simple formula. Just follow the steps below:

• First, insert the criteria keyword in cell range F5:F6.

• Next, insert this formula in cell G5.

• After this, press Enter.
• You will see that the condition is TRUE for the keyword.

• Lastly, apply the same formula in cell G6 and see the output.

Read More: Advanced Filter with Criteria Range in Excel (18 Applications)

Similar Readings

• How to Apply the Advanced Filter to Copy to Another Location in Excel
• Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)
• How to Create Dynamic Advanced Filter in Excel (2 Applications)
• Use Custom Autofilter in Excel for More Than 2 Criteria

3. Use of Wildcard Characters in Excel Advanced Filter

Sometimes you will come across situations where using Wildcard Characters (?, *, and ~) will save you time. Consider a type of filtering that you want to show only the companies that start with the letter “e”.

• First, select a cell within the data that you want to filter.
• Then, apply the filter as we described before.
• Next, click on the Filtering icon at the top of the column and type “e*” in the Text Filters field.

• Click OK. You will get the filtered data like the image below.

Read More: How to Use Advanced Filter with Wildcard in Excel

4. Advanced Filter to Extract Unique List

If you want to fetch data from repetitive values (see attached image), this method will help you.

Let’s check the process below:

• As discussed above, open the Advanced Filter dialogue box from the Data tab.
• Here, insert the List range B4:D15.
• Following, mark checked the Unique records only box.

• After this, press OK.
• That’s it, you have your unique list of keywords.

Read More: How to Use Advanced Filter for Unique Records Only in Excel

5. Insert Advanced Filters in Column Intervals

Do you know what intervals are? 1 – 5, 8 – 15; these are intervals. Want to filter a column for different intervals? Let’s follow the steps below:

• First, make the Criteria range like the following image.

• Now, insert the List and Criteria range in the distinctive boxes.

• Finally, press OK and you will see the result.

Read More: Advanced Filter with Multiple Criteria in Excel (15 Suitable Examples)

Excel Advanced Filter to Copy to Another Location

As we mentioned before, the advanced filter is capable of copying the filtered dataset to another location. Let’s see how it works.

• First, create a new sheet beside the old one.

• Then,  insert the criteria in cells F5, G5 and H5.

• Now, open the Advanced Filter dialogue box.
• Here, select Copy to another location.

• Then, insert the List range according to the dataset.
• Following, insert the Criteria range.
• Lastly, insert the location in the new sheet where you want to copy in the Copy to box.

• Finally, press OK and see the final output.

Read More: How to Use Advanced Filter to Copy Data to Another Sheet in Excel

[Fixed!] Excel Advanced Filter Not Working

There are numerous reasons for excel advanced filter not working. Here, check these 2 reasons:

• Check if the header title of both dataset and criteria range mismatch like below:

• Otherwise, find out if there is any error or blank cell like this:

Try to resolve them by carefully inserting the header title or inserting the correct value and your advanced filter will work properly.

Read More: How to Use Advanced Filter to Exclude Blank Cells in Excel (3 Easy Tricks)

Excel Advanced Filter: Knowledge Hub

• Advanced Filter with Multiple Criteria
• Advanced Filter with Criteria Range
• Create Dynamic Advanced Filter
• Use of Advanced Filter for Date Range
• Apply Advanced Filter Based on Multiple Criteria in One Column
• Use of Advanced Filter for Unique Records Only
• Use of Advanced Filter If Criteria Range Contains Text
• Use of Advanced Filter to Exclude Blank Cells
• Apply the Advanced Filter to Copy to Another Location
• Use of Advanced Filter to Copy Data to Another Sheet
• Excel Advanced Filter: Apply “Does Not Contain”
• Use of Advanced Filter with Wildcard
• Use of Auto Filter and Advanced Filter
• Use of Advanced Filter in VBA
• Excel VBA Examples with Advanced Filter Criteria
• Advanced Filter with Multiple Criteria in a Range Using VBA
• VBA to Copy Data to Another Sheet with Advanced Filter
• Remove Advanced Filter
• Excel Advanced Filter Not Working

Conclusion

I hope this article will help us to know about Advanced Filter in Excel. Do you want to add something or some special technique you know about data filtering in Excel? Please share in the comment box; it will be highly appreciated. Also, keep an eye on ExcelDemy for more articles like this.

Related Articles

• How to Remove Advanced Filter in Excel (5 Effective Ways)
• Excel VBA: Advanced Filter with Multiple Criteria in a Range (5 Methods)
• How to Remove Duplicate Names in Excel (6 Simple Methods)
• VBA to Copy Data to Another Sheet with Advanced Filter in Excel
• How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)
• Use Advanced Filter for Date Range in Excel (2 Easy Ways)
• How to Use Auto Filter and Advanced Filter in Excel