How to Use Criteria in Excel Functions and Filters

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.