Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Advanced Filtering.

Advanced Filtering

by Allen Wyatt
(last updated January 12, 2017)

8

There are some situations in which AutoFilter just doesn't have the muscle you need when processing your data. For instance, you might need to perform a calculation in a filter comparison. In these cases, you can use the advanced filtering capabilities of Excel.

Advanced filtering requires that you set up a criteria range in your worksheet. This criteria range is not part of your data list, but instead is used to signify how you want filtering to be performed. Typically, you would place your criteria before your data list, but you can also place it after. The important thing is that you separate your criteria from you data list by at least one empty row. Otherwise, Excel may think that the criteria are part of the actual data list.

The criteria are entered in your worksheet such that each column represents a different logical AND comparison, and each row represents a different logical OR comparison. If this sounds confusing, don't be concerned. An example will help clear things up.

Let's say you have a data list that starts in the sixth row of a worksheet. You have set aside the rows above this to specify your filtering criteria. The data list contains columns that describe information in your inventory. There are columns for item numbers, description, location, quantity, value, and the like. There is also a calculated column that indicates the profit derived from each inventory item.

At some time you may want to filter your data list so it shows only a limited subset of your inventory items. For instance, you might want to see only those items for which the quantity is over 2500 and profit is less than 1000, or those items where the quantity is greater than 7500, or those items where profit is under 100. (This is much more complex than you can perform using an custom AutoFilter.)

To set up such a filter, all you need to do is set your criteria. In this case, you would use cells A1:B4 as follows:

 A B
1 Quantity Profit
2 >2500 <1000
3 >7500
4  <100

In this example the first row shows the field names to be used in comparisons, while the second through fourth rows define the actual comparisons. Notice that because there are two tests in the second row, these are considered an AND condition, and those on the other rows are considered OR conditions.

To apply these filtering criteria, follow these steps:

  1. Select any cell in the data list (not in the criteria).
  2. Display the Data tab of the ribbon.
  3. Click Advanced Filter in the Sort & Filter group. Excel displays the Advanced Filter dialog box, with the address of your original data table already filled in, in the List Range box. (See Figure 1.)
  4. Figure 1. The Advanced Filter dialog box.

  5. Select the Criteria Range field. The insertion point should appear within the field.
  6. Use the mouse to select the criteria range, cells A1:B4.
  7. Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7851) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Advanced Filtering.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Setting Horizontal Alignment

You can horizontally align the information in a cell in any of eight different ways. This tip explains not only how to do the ...

Discover More

Removing Items from a Context Menu

Context menus appear when you right-click an item in Excel. If you want to modify the menu that appears, the way to do so is ...

Discover More

Starting Chapters on Odd-Numbered Pages

Want to start a new heading on an odd-numbered page? You can do it with section breaks, obviously, but you can also do it ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Extracting Targeted Records from a List

If you have a bunch of data in an Excel worksheet, you may need to work with just a subset of that data. One way to do that ...

Discover More

Filtering Columns for Unique Values

Given a long list of names, part numbers, or what-have-you, you may need to determine the unique values within the list. The ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2017-01-12 13:01:05

Paul

Regarding Martin's comment on clearing an advanced filter:
The Clear button on the Data tab (close to the Advanced button) works for me.


2017-01-12 10:06:31

Kit

Hi Alan,
I've been following you for YEARS. I appreciate these topics and find they are quite useful. However, this one falls a little short of the usual explanation. Please expound on this topic at least a little further, at least to the point of explaining how to choose the various filtering options posted above. Thanks Alan... and any others who leave comments!!!
Have a blessed day!


2017-01-12 09:55:51

Shandor

Thanks to Bryan for steering us to visual examples, and hey Allen (thanks for the article too): could you add the visuals or explicitly mention in the article Excel's more thorough visual help? Thanks to Martin for the urgent warning about clearing the filters; I had no idea!


2017-01-12 08:28:36

Pete Zicari

Advanced filtering is very valuable but can be frustrating, in that it will sometimes return a row of headings and no data. It is smart, therefore, to always use the "copy to another location" option for ease in figuring out what the problem is. Also, in Excel 2016 for the Mac, the last range you select will not necessarily appear in the box where it belongs (notably the copy-to box). Proceed anyway; you'll find it's just the display.


2017-01-12 08:01:35

Bob Lyons

I'm having two troubles with the Advanced Filter

1. It won't let me use the Greater Than > Less Than < or Not <> features.

2. I have around 350,000 rows of data that I am applying to the Advanced Filter and now it is really really slow.


2013-09-03 08:32:52

Bryan

@spj: the built-in Excel help file for this topic is rather thorough. It shows, for instance how to "perform a calculation in a filter comparison", which Allen teased us with but did not show us. Use the help files instead.


2013-09-02 09:53:10

spj

step by step walk through of a visual example would be more helpful. Thx


2013-08-31 05:22:11

Martin Nicol

Advanced filtering is great but users should be warned to clear filters when finished. If not they will end up with worksheets that have hidden rows that do not unhide with the normal commands.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.