Software optimize

How to use And and Or operators with Excel's Advanced Filter

Use implicit And and Or operators with Excel's Advanced Filter feature to create complex, but powerful, filtering combos.

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!

This blog post is also available as a TechRepublic Photo Gallery.

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.)

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.

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

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:

  1. Click any cell in the data range.
  2. 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.
  3. Retain the default setting, Filter the List In-Place.
  4. Excel automatically fills in the List Range, correctly in this case.
  5. Specify the Criteria range, A1:F2. You only need to identify the column headings and the criteria row or rows.
  6. Click OK.

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.

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

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

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

About

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.

1 comments
120717-000048
120717-000048

Quote: 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,.... This is not true since you can firstly select one criteria and then select the second one on the remaining rows. This is exactly the AND funtion. For OR I agree with you. BR Wolfgang