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.

advancedfilterribbon01

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.

filterselectedcellvalue

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.

autofilterallcriteria01

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.

advancedfiltercriteria02

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!

___________________

You may also like...

10 Responses

  1. Ivan M. says:

    Referring to “just right-click on a cell, click Filter, then click Filter by Selected Cell”

    It’s a real lack of an opposite action in Excel. If we have “Filter by Selected Cell’s Value” then why don’t us have an opposite one, something like “Filter by Selected Cell’s Value (does not equal)”? Sometimes it’s more than convenient to operate with both actions

    It’s strange but there are such BOTH actions when you filter Pivot Table: Keep Only Selected Items and Hide Selected Items

    I’d wish to have the same BOTH ones for a standard AutoFilter right-click menu. Currently we can achive such action only with VB macro command (please share another experience if any)

    • @Ivan M, You’re right, it would be useful to have a “Hide Selected Items” feature in the AutoFilter right-click menu too. It’s much quicker than going to the drop down list and unchecking items.

  2. kenny says:

    Hi Debra,

    Thanks for your brilliant tutorial on Advanced Filter. However, I have a problem when I conduct a ‘Date’ filter. Even though my date criteria cell is formatted as dd/mm/yy, it would not allow me to filter based on that format, especially if I have to use any of the comparative operators such as “=”. E.g. if I have to filter between 12 Jan 2014 (i.e. 12/01/14) and 20 Jan 2014 (i.e. 20/01/14) I have to enter the dates in mm/dd/yy format, thus: =1/20/14.

    Is there anything I can do to enter the criter in dd/mm/yy format i.e. (=20/01/14) and be able to filter without any problem, please?

    Thanks.

    Kenny

  3. kenny says:

    Hi Debra,

    Thanks for your brilliant tutorial on Advanced Filter. However, I have a problem when I conduct a ‘Date’ filter. Even though my date criteria cell is formatted as dd/mm/yy, it would not allow me to filter based on that format, especially if I have to use any of the comparative operators such as “equal to” and “greater than”; also “equal to” and “less than”. E.g. if I have to filter between 12 Jan 2014 and 20 Jan 2014, I have to enter the dates in mm/dd/yy format, thus: “equal to & greater than” 1/12/14 and “equal to” and “less than” 1/20/14.

    Is there anything I can do to enter the date criteria in dd/mm/yy format (i.e. 12/01/14 and 20/01/14) and be able to filter without any problem, please?

    Thanks.

    Kenny
    (Sorry that in my previous post, the comparison operators were not picked up.)

  4. Kenny says:

    Thanks Debra. Your FL0006 which you referred me to is very useful. I have decided to adopt your approach in that sample file.

    Very grateful for your support.

    Kenny

  5. lee fletcher says:

    hi I am wondering if you know anyone who can set up a filet of columns in excel
    at the moment I get a report with lots of columns and need to have a formula compiled to only show certain columns.

    in short I need t to work like this.
    tracking program emails an excel report with 17 columns
    I need to have a formula that I can select when report is open to only show say 8 columns

    the big question is do you know anyone who can do this for me

    regards
    lee fletcher

  6. Ryan says:

    Hi Debra – I need to compare three tables of data that do not have common column headers. Many of the columns have the same data, just have different headings since each table is from a different procurement system. My goal is to use Advanced Filter to lookup row data by UPC, Warehouse, etc. so that I may verify the data is correct/same in the three tables. My question to you is am I able to use Advanced Filter with multiple tables? If not, do I need to go about my data scrubbing in a different way? I appreciate your help! Thank, Ryan

    • @Ryan, an Advanced Filter will only pull data from one table, so it wouldn’t be much help, from what you’ve described. Perhaps you could add a column in each table, to identify its source, then use Power Query to combine the tables into one, and filter the data there.

  7. DZ says:

    Advanced filters suck! On the file having lots of formulas it takes a year to filter because it recalculates after each line.

Leave a Reply to lee fletcher Cancel reply

Your email address will not be published. Required fields are marked *