Skip to content

TechRepublic

  • Top Products Lists
  • Developer
  • 5G
  • Security
  • Cloud
  • Artificial Intelligence
  • Tech & Work
  • Mobility
  • Big Data
  • Innovation
  • Cheat Sheets
  • TechRepublic Academy
  • CES
  • TechRepublic Premium
  • Top Products Lists
  • Developer
  • 5G
  • Security
  • Cloud
  • Artificial Intelligence
  • Tech & Work
  • Mobility
  • Big Data
  • Innovation
  • Cheat Sheets
  • TechRepublic Academy
  • CES
  • See All Topics
  • Sponsored
  • Newsletters
  • Forums
  • Resource Library
TechRepublic Premium
Join / Sign In
Software

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

By Susan Harkins July 23, 2010, 4:23 AM PDT

Image
1
of 9

448420.jpg
448420.jpg
How do I use And and Or operators with Excel’s Advanced Filter?

A simple AutoFilter example

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.

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

Dropdown arrow

Dropdown arrow

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

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

Unit Price and Units In Stock

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.

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

Create an expression

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.

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

Select range

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:

  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

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

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

Identify the column headings

Identify the column headings

6. Click OK.

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

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

Implicit Or operator

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.

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

Specify criteria range

Specify criteria range

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.

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

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

Filtered results

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.

  • Software
  • Account Information

    Share with Your Friends

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

    Your email has been sent

Share: How do I use And and Or operators with Excel's Advanced Filter?
Image of Susan Harkins
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.
  • Account Information

    Contact Susan Harkins

    Your message has been sent

  • |
  • See all of Susan's content

TechRepublic Premium

  • TechRepublic Premium

    Employee privacy policy

    PURPOSE This policy describes the organization’s employee privacy guidelines and outlines employee privacy expectations. From the policy: POLICY DETAILS The organization’s IT equipment, services and systems are intended for business use only. However, the organization acknowledges that staff, consultants and volunteers occasionally require opportunities to make or receive personal phone calls, access personal email, utilize ...

    Published:  March 19, 2023, 12:00 PM EDT Modified:  March 20, 2023, 4:00 PM EDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Security response policy

    PURPOSE The purpose of this Security Response Policy from TechRepublic Premium is to outline the security incident response processes which must be followed. This policy will assist to identify and resolve information security incidents quickly and effectively, thus minimizing their business impact and reducing the risk of similar incidents recurring. It includes requirements for both ...

    Downloads
    Published:  March 19, 2023, 12:00 PM EDT Modified:  March 20, 2023, 4:00 PM EDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    End user data backup policy

    PURPOSE This policy from TechRepublic Premium provides guidelines for reliable and secure backups of end user data. It outlines the responsibilities of IT departments and employees to identify tasks and action items for each group. The policy can be customized to fit the needs of your organization. From the policy: IT STAFF RESPONSIBILITIES IT staff ...

    Published:  March 16, 2023, 12:00 PM EDT Modified:  March 17, 2023, 5:00 PM EDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Electronic communication policy

    This policy from TechRepublic Premium provides guidelines for the appropriate use of electronic communications. It covers topics such as privacy, confidentiality and security; ensures electronic communications resources are used for appropriate purposes; informs employees regarding the applicability of laws and company policies to electronic communications; and prevents disruptions to and misuse of company electronic communications ...

    Published:  March 16, 2023, 12:00 PM EDT Modified:  March 17, 2023, 3:00 PM EDT Read More See more TechRepublic Premium

Services

  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information

Explore

  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • TechRepublic
  • TechRepublic on Twitter
  • TechRepublic on Facebook
  • TechRepublic on LinkedIn
  • TechRepublic on Flipboard
© 2023 TechnologyAdvice. All rights reserved.
  • Privacy Policy
  • Terms of Use
  • Property of TechnologyAdvice