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.

Excel Advanced Filter


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.

Apply Criteria in Excel Advanced Filter

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

Apply Criteria in Excel Advanced Filter

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

Apply Criteria in Excel Advanced Filter

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.

Note: Copy to the field is inactive (above image) because Filter the list, in-place option is selected above.

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.

Advanced Filter Using Excel Formula

  • Next, insert this formula in cell G5.
=B5>700

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

Note: Here is an important thing you should remember. All formulas are identical because every criterion in the Criteria range works independently.

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.

Use of Wildcard Characters in Excel Advanced Filter

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

Advanced Filter to Extract Unique List

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.

Insert Excel Advanced Filters in Column Intervals

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

Excel Advanced Filter to Copy to Another Location

  • 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:

[Fixed!] Excel Advanced Filter Not Working

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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

6 Comments
  1. Nicely done, but I think there should be a clarification on the “Formula” section; you don’t put the formula in the first cell in the “Formula” column and drag down, but instead put the identical formula in each cell of the “Formula” column. I was getting wonky results until I realized that.

    • Thanks for the input, Jomili.
      Yes, the formulas are identical as every criterion in the Criteria range is unique. I am going to add the note there 🙂

  2. Hi Kawser,

    How are you doing? I have been practicing this lesson (for which one a big THANKS to you). Could you kindly help me with formatting for intervals? I can’t get what I have done wrong:(
    After filtering using Advanced Filter i have got the empty field..

    Your answer would be really helpful!!
    Thanks a lot!

    • Kateryna,
      Thanks for your kind words. Glad to know that it helps.
      You will get empty cells if no data is available for the filtering. What criteria did you use to filter data in intervals?

  3. Reply
    Roger Gruenenfelder Dec 14, 2016 at 9:00 AM

    Your blog is a marvel. Nevertheless I miss the opportunity to download , lets say above instructions and teaching for later ealuation together with the example file.
    Is it possible to also invent a pdf file for download ?
    I live in the remote Philippines and internet access or solid internet is, well, sometimes a game…..So it would be helpful if one can review your articles offline. Copy/paste seems not the best idea or is it?
    Thanks for suggestions, Roger

    • Roger, this option (make PDF of the blog posts) is on my plan. Will execute in shortest possible time.
      Best regards and thanks for the comments.

Leave a reply

ExcelDemy
Logo