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. You have several options:

• Use the built-in AutoFilters – And, starting in Excel 2007 these are really great!
• 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

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

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.

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

Learn how to “Master Excel in Minutes – Not Months!”

 Subscribe to our RSS Feed

This entry was posted on Monday, December 27th, 2010 at 5:04 PM and is filed under Data Filters and Queries, Excel Training, Podcast Tips & Timesavers. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.