Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
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.
Learn more about Allen...
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Performing Calculations while Filtering.
Filtering a list means displaying only a part of it. You provide the criteria you want used, and then Excel displays only those list records that match the criteria. Filtering is especially useful if you have a large list and you want to work with only a subset of the records in the list. Other ExcelTips have described different ways you can create and apply filters to your worksheets.
When you are using the advanced filtering capabilities of Excel you can perform calculations during the filtering process. For instance, let's assume you have a large inventory list in a worksheet, and you want to filter the list to show only those records that were in a particular department and that have a higher-than-average profit. The inventory is contained in starts at cell A6 (with your column headings) and the profit is listed in column I. (See Figure 1.)
Figure 1. Example inventory data in a worksheet.
You can use an advanced filter by setting up your criteria in other cells. For instance, let's say that your criteria are in the cell range A1:B2. (See Figure 2.)
Figure 2. Example filtering criteria.
Row 1 contains the names of the columns in your datasheet that you want compared in the filtering. Thus, cell A1 contains the name "Item" because you want the value under it (in cell A2) to be used in filtering the data table based on the contents of the Item column. There is no column name in cell B1 because you aren't keying the criteria on a column's contents; you want it based on a calculation. Here are the formulas you should place in cells A2 and B2:
This example provides for a text comparison related to the department number (in cell A2) and a comparison of the profit for the item (I7, which is a relative cell reference and therefore changes for each comparison) to the average profit for the entire inventory ($I$7:$I$42, which is an absolute reference and therefore does not change for each comparison). If an absolute reference had not been used for the AVERAGE function, the wrong results would have been generated by the filtering.
When you apply an advanced filter to your inventory data (as described in other ExcelTips), the result, using the above criteria, is that only those records that had a profit greater than the average (the average in I7:I42) were displayed.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2982) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Performing Calculations while Filtering.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!