How to Set Up an Excel Advanced Filter

Poor Advanced Filter! It’s hidden in a dark corner of the Ribbon’s Data tab, overshadowed by its better known, and more popular sibling, AutoFilter.
Even without the Ribbon command, it’s easy to filter a list with the built in AutoFilter feature — just right-click on a cell, click Filter, then click Filter by Selected Cell.
Instantly, the list is filtered, and if the list didn’t already have drop down arrows in the headings cells, those are added automatically, when the AutoFilter is applied.

Using an AutoFilter

With an AutoFilter, you can select a criteria in each column of the list, to see the records that meet all of the criteria. For example, see all the orders with a specific customer AND a specific product.
In the screen shot below, the list is filtered to show orders where the customer is MegaMart AND the product is Milk.

Advantage to Advanced Filter

However, with an AutoFilter, there is a limitation — you can’t see all the orders with a specific customer OR a specific product. To filter a table for one set of criteria OR another, you can use an advanced filter.
It takes a bit more effort to use the Advance Filter feature. You’ll need to set up a criteria range, to let Excel know what you want in the filter results.
But, there are definite advantages – you can create complex criteria, and you can send the results to a different location in the workbook. In the example shown below, the criteria range is in G1:H3.

  • The heading cells match the columns in the list that’s being filtered.
  • The first row of criteria request a specific customer – MegaMart – and any product
  • The second row of criteria request a specific product – Mile – and any customer
  • When the filter runs it will return any orders where the customer is MegaMart OR the product is Milk.


Download the Sample File

For more information on Advanced Filters, and to download the sample file, please visit the Advanced Filter page on my Contextures website.

Video: Set Up an Advanced Filter

To see the steps for setting up the criteria range, and running the Advanced Filter, please watch this short video.

Or, watch the video on YouTube: How to Set Up an Excel Advanced Filter

Please Take the Number Format Survey

In completely unrelated news, I’m trying to find out which number format settings are most popular in pivot tables.
If you have a minute, please answer the 3 questions to help me see which number formats are most often used in pivot tables. Thanks!