Performing Calculations on Filtered Data

When you filter your worksheet, you limit the data that appears. The ability to focus on the data that s most vital to your current needs is important, but there are a few limitations. One limitation is that any formulas you have created don t change their calculations, even if some of the rows used in the formula are hidden by the filter.

There are two ways you can find the total of a group of filtered cells. The first method is to use AutoCalculate. To use AutoCalculate, you select the cells you want to find the total for. When you do, the total for the cells appears on the status bar, at the lower edge of the Excel window.

When you use AutoCalculate, you aren t limited to finding the sum of the selected cells. To display the other functions you can use, you right-click the AutoCalculate pane and select the function you want from the shortcut menu that appears.

AutoCalculate is great for finding a quick total or average for filtered cells, but it doesn t make the result available in the worksheet. To make the value available in your worksheet, you can create a SUBTOTAL function. As with AutoCalculate, you can choose the type of calculation the function performs .

In this exercise, you use AutoCalculate to find the total of a group of cells in a filtered worksheet, create a SUBTOTAL function to make the same value available in the worksheet, and then edit the SUBTOTAL function so that it calculates an average instead of a sum.

USE the  Calculations.xls document in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\UsingFilters folder, and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the  Calculations.xls document.

1. If necessary, click the January sheet tab.

2. Click cell P5.

3. On the Data menu, point to Filter , and then click AutoFilter .

   A down arrow button appears in cell P5.

4. In cell P5, click the down arrow button and then, from the list that appears,click (Top 10 ) .

   The Top 10 AutoFilter dialog box appears.

5. Click OK .

   Tip  

   Clicking OK here accepts the default setting of the Top 10 AutoFilter dialog box, which is to show the top 10 values in the selected cells.

   The Top 10 AutoFilter dialog box disappears, and the rows with the 10 highest values in column P are displayed.

6. Click cell P6 and drag to cell P27.

   The cells are selected, and on the status bar, in the lower right corner of the Excel window, SUM=36781 appears in the AutoCalculate pane.

   

7. Click cell P37, and then, on the Standard toolbar, click the AutoSum button.

   The formula =SUBTOTAL(9,P6:P36) appears in the formula bar.

8. Press [ENTER].

   The value 36781 appears in cell P37. The value in cell P38 also changes to 134706 , but that calculation includes the subtotal of the filtered cells in the column.

9. Click cell P37, and then, in the formula bar, edit the formula so that it reads =SUBTOTAL(1,P6:P36) and then press [ENTER].

   By changing the 9 to a 1 in the SUBTOTAL function, the function now calculatesan average instead of a sum. The average of the top 10 values in cells P6 through P36, 3678.1 appears in cell P37. The value in cell P38 also changes to 101603.1 , but that calculation includes the average of the filtered cells in the column.

10. If necessary, click cell P37 and then press [DEL].

    Excel deletes the SUBTOTAL formula from cell P37, and the total in cell P38 changes to 97925 .

11. On the Standard toolbar, click the Save button.

    Excel saves your changes.

CLOSE the  Calculations.xls document.