How do I use And and Or operators with Excel's Advanced Filter?

A simple AutoFilter example

Viewing subsets of data is a routine task for many Excel users. An AutoFilter lets you limit the data displayed, but it's limited as it depends on the actual data. Excel's Advanced Filter feature requires a bit of setup, but is more flexible and powerful than an AutoFilter. Not only can you use an expression to match records, you can combine expressions using the And and Or operators - now, that's power!

Excel's Advanced Filter feature requires three elements:
  • Data
  • A criteria range, where you specify criteria as an expression.
  • An extract range, where Excel displays the data that satisfies the criteria.

A simple AutoFilter

Before we get into a more advanced example, let's look at a simple AutoFilter example using a partial set of data from the Products table from Northwind (the database that comes with Access). To apply an AutoFilter, you select the column headings in A1:F1 and choose AutoFilter from the Data menu. In Excel 2007 and 2010, click the Data menu and then click Filter in the Sort & Filter group. Excel will display a dropdown arrow for each column in the selection. Using this feature, you can perform simple filtering tasks, such as which products have no units on order. It's quick and easy, but sometimes inadequate. (To remove a filter, simply choose All from the same list.)

This gallery is also available as a TechRepublic blog post. Image created by Susan Harkins for TechRepublic.

By Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.