How to Use Excel Advanced Filter?

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, you will learn

– How to use Excel advanced filter applying criteria.
– The use of Excel advanced filter using formula and wildcard characters.
– Extraction of unique list using Excel advanced filter.
– How to insert advanced filters in column intervals.
– The use of Excel advanced filter to copy to another location.
– The solutions if Excel advanced filter doesn’t work as expected.

While preparing this article, we have used Microsoft 365 but the methods mentioned in this article are also applicable in the other versions as well.

Excel Advanced Filter is used to extract particular data subsets based on complex criteria, assisting in targeted analysis, research, project management, financial analysis, and improved inventory management.


Download Practice Workbook


What Are the Differences of 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.

What Are Some 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. How to 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.

Depending on the number of words we are using as the criteria for the filter, there can be slight changes in the usage.

1.2 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. How to Utilize 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. How to Use 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.

Read More: Use Advanced Filter to Exclude Blank Cells in Excel


4. How to Use 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.

Read More: Apply the Advanced Filter to Copy to Another Location in Excel


5. How to 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.

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


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.


What to Do When Advanced Filter Is Not Working in Excel?

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.


Knowledge Hub


<< Go Back to | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

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

  4. Reply
    tsegay gebreselasie Sep 19, 2023 at 6:35 PM

    good be continue like this

    • Hello Tsegay Gebreselasie,

      Our aim is giving you the best Excel-related content like this.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo