How to Apply Criteria for Advanced Filters in Excel

You want to use Excel to do more than just store data. You want to use Excel Filters in order to get information from your data set. In order to Filter the data, you need to create and apply criteria.

Options for Filtering Data:

Advanced Filtering

• Use the built-in AutoFilters – And, starting with Excel 2007 these include “Natural Language Filters!”
• Use the Advanced Filter Dialog Box and your own Criteria Range – that is what I demonstrate in this lesson.

Now – since Excel 2007 – that the built-in filters include Date Filters, Text Filters and Number Filters, I do not use the Advanced Filter as much as in the past. However, there are at least two “must have” reasons to use Advanced Filters:

1. To use “Calculated Field” criteria for your filters – e.g. to create a filter from a field that is not in your data set.
2. To extract “Unique Records” that meet your criteria – and to copy them to another location.

I demonstrate both of these “must haves” in this video lesson.

How to Create an Advanced Filter

1. Begin by inserting several blank rows above your Excel Data Set. These new rows will be used for your Criteria Range.
2. Copy the Data Labels to the top row of your new Criteria Range. I like to link these with a formula – e.g. =A8 to maintain consistency with the Data Labels.
3. Type or copy the values, comparison operators or formulas for your criteria in the row(s) below your criteria labels.
4. Go to the Data Tab on the Ribbon and choose the Advanced Filter Command and use the Dialog Box Options.

Distinguish “OR” criteria from “AND” criteria

• “OR” criteria use separate rows in your criteria range. You are selecting the records that match one OR more criteria.
• “AND” criteria are written on the SAME ROW. You are filtering for records that match ALL the criteria on that row of your criteria range.

Computed Criteria in Filters

With Advanced Filters, you can use Computed Criteria for your filters. The formula must return a value that is either TRUE or FALSE. And the Label for your computed criteria CANNOT be the same as the labels in your data set.

In this lesson I show you how to filter for Invoices that have been paid – but paid “late” – after the “due date.”

Extract Unique Records with Advanced Filter

I show you how to copy the unique records – your list of customers – to a new worksheet. This is a great tip that I picked up by reading John Walkenbach’s books! Watch the video to see how this is done.

Watch My Video in High Definition

Click here to watch this video lesson in High Definition, Full Screen Mode on the DannyRocksExcels YouTube Channel.

Master Excel in Minutes Resources

I have created extended length video tutorials for Excel. I invite you to visit my online shopping website to: Learn how to “Master Excel in Minutes – Not Months!”

How to Apply Advanced Filters in Excel [ 10:45 ] Play Now | Play in Popup | Download (1445)