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

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

Editor’s Note: This article was originally published in July 2010 and the video tutorial for this article published Dec. 2018; while this program might look a little different, the steps shown in this tutorial are the same.

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.

LEARN MORE: Office 365 Consumer pricing and features

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.

SEE: Crash Course: Microsoft Excel – Beginner (Tech Pro Research)

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.

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.

Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.

Daily Tech Insider Newsletter

Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.

Delivered Weekdays

Sign up today

Also See

• How to add a drop-down list to an Excel cell (TechRepublic)
• Build your Excel skills with these 10 power tips (free PDF) (TechRepublic)
• You've been using Excel wrong all along (and that's OK) (ZDNet)
• A cheatsheet of Excel shortcuts that make inserting data faster (TechRepublic)
• Six clicks: Excel power tips to make you an instant expert (ZDNet)
• 10 advanced formatting tricks for Excel users (TechRepublic)