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.

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.

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.

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.

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.

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.

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.

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.

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.

[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.

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.