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.

Dropdown arrow

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

Unit Price and Units In Stock

An Advanced Filter and And
Now, suppose you wanted to know which products with a price of $20 or more have 10 or less units currently in stock. This filtering task has two requirements – two criteria – and you want to satisfy them both. In other words, the product must be $20 or more and have 10 or fewer units in stock. An AutoFilter just can’t do that, so let’s try an Advanced Filter.

The criteria range, in this case, requires only two columns: Unit Price and Units In Stock. You could copy just those column headings to an out of the way place. I recommend copying all of the headers – you might need them for another filter.

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

Create an expression

Next, you need to state your filtering requirements in terms Excel can understand, using an expression. In this case, both expressions are simple comparisons:

Unit Price: >=20
Units In Stock: <=10

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

Select range

As you can see, the criteria range is above the actual data. This placement is efficient and easily accessible. Both expressions are in the same row – row 2. By placing both expressions in the same row, Excel knows to apply an implicit And operator to combine the expressions.

The extract range is optional. To copy the data to another location, specify the columns you want to extract. Be careful, as the headings must be an exact match to the original column headings. If you leave the extract range empty. Excel will extract all of the columns – simply select the top-left cell in the range. To simplify the example, we’ll extract in place.

All that’s left now is to apply the filter as follows:

Click any cell in the data range.
Click the Data menu, and then click Filter | Advanced Filter. In Excel 2007 and 2010, click the Data tab and then click Advanced Filter in the Sort & Filter group.
Retain the default setting, Filter the List In-Place.
Excel automatically fills in the List Range, correctly in this case.
Specify the Criteria range, A1:F2. You only need to identify the column headings

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

Identify the column headings

6. Click OK.

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

Implicit Or operator

Eight products have a price of $20 or more and have 10 or fewer units in stock. To remove the filter, click the Data menu, then click Filter | Show All.

An Advanced Filter and Or
To specify an implicit Or operator, you must place the expressions in separate rows. The criteria shown below will find products with a price of $20 or more or products with 10 or fewer products in stock.

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

Specify criteria range

After adjusting the criteria range by moving one of the expressions down a row, apply the new filter as follows:

Click any cell in the data range.
Click the Data menu, and then click Filter | Advanced Filter. In Excel 2007 and 2010, click the Data tab and then click Advanced Filter.
Retain the Filter the List In Place setting, the default.
Excel automatically fills in the List Range, correctly in this case.
Specify the Criteria range—that’s A1:F3. Notice that this time, the range includes row 3.

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

Filtered results

6. Click OK. Many records meet one or the other criteria.

You can use an Advanced Filter with just one expression, but using implicit And and Or operators opens the door for some very complex but powerful filters. Just be careful that the expressions and their placement make sense, in terms of applying the And and Or operators.

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