How to Use Criteria in Excel Functions and Filters

Criteria for Advanced Filter

Criteria for Advanced Filter

When you apply a Filter to an Excel data set, you use Criteria. In a filter, only the records that can answer “TRUE” to the criteria that you set will appear. The records that answer “FALSE” are hidden while the filter remains in place.

Logical Functions

The IF(), AND() and OR() Functions belong to a group of Logical Functions. In their function arguments, they evaluate a “Logical Test” and can perform one action is the answer is “TRUE” and a different action if “FALSE.” 

  • The AND() Function – each of the Logical Tests must be met in order to return the answer “TRUE.”
  • The OR() Function – if any condition is met, the answer that is returned is “TRUE.”
  • I frequently “Nest” an AND() Function or an OR() Function inside an IF() Function to test multiple criteria.

Advanced Filters in Excel

While many Excel users are familiar with Filtering, in my experience, I find that many people get confused when it comes to using Advanced Filters. In this video tutorial, I try to eliminate this confusion. Watch and you will learn how to:

  • Write Criteria for Advanced Filters using “AND” criteria and “OR” Criteria.
  • Copy the “Filtered Records” to another location
  • Use the Keyboard Shortcut Ctrl + Shift + L to quickly “toggle” normal filter drop-down menus on or off.

 

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Defintion on my YouTube Channel – DannyRocksExcels

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 Filter Dialog Box

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!”

Create Advanced Filters for your data list

In this Excel Video Lesson we will teach you to use Advanced Filtering of your data list.

Here are the steps to follow in this video lesson:

  1. Apart from your data table write your Criteria. Criteria must consist of at least 2 rows – a Header and at least 1 row of Criteria.
  2. With an active cell selected in the data table, click Data, Filter, Advanced Filter.
  3. Accept the default setting to Filter the Data in-place and also the range for the data list. Select the cells for your Criteria and click OK.
  4. The Advanced Filter applies the Criteria to show only those records that meet your Criteria.
  5. When you write your Criteria on 1 row, ALL of the criteria must be met – i.e. the “AND” option.
  6. When you write your Criteria on multiple rows, each criteria is met independently – i.e. the “OR” option.

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

  • AutoFilter your list
  • Apply Custom Filters to your data list