XL97: How to Use the PivotTable AutoShow Feature

MORE INFORMATION

A PivotTable is an interactive table that summarizes and analyzes data from existing lists and tables. In earlier versions of Microsoft Excel, it is possible to sort by PivotTable data fields and to hide items from a row or column field. It is also possible to hide PivotTable field items. However, there is no feature that automatically displays a subset of PivotTable field data that is updated as the PivotTable is updated.

Microsoft Excel 97 allows you to easily display a subset of the PivotTable data that is recalculated automatically when the PivotTable is updated. This feature is called the AutoShow feature. This subset can be the largest (top) <n> data values or the smallest (bottom) <n> value fields, where <n> is a number that you specify. For example, you can have Microsoft Excel display the top ten students per teacher based on test grades or the bottom three golfers per golf course based on golf scores. Each time the PivotTable is updated with new data, Microsoft Excel recalculates and displays the top or bottom items.

You can access the following two basic AutoShow settings in the "PivotTable Field Advanced Options" dialog box: Manual, or Automatic. If you select Automatic, you can specify whether to show the bottom or top items, and you can specify the number of items to show for the field. Also, when you select Automatic, you can specify the data field to use when you display the top or bottom items.

NOTE: When you select a field and use the AutoShow feature, the field name in the PivotTable is formatted in blue to indicate the state of the field.

Using AutoShow in an Existing PivotTable

1. Select any cell in the PivotTable, and then click PivotTable Report on the Data menu.
   
   Step 3 of the PivotTable Wizard appears.
2. Double-click the row or column field you want to use to find the top or bottom subset of data.
   
   NOTE: AutoShow works only for row or column fields.
3. In the PivotTable Field dialog box, click Advanced.
4. In the "PivotTable Field Advanced Options" dialog box, under AutoShow Options, click Automatic.
5. Click either Top or Bottom in the Show box, and then specify a number for the number of items to display.
6. Click the data field to use for the subset in the Using Field list.
7. Click OK.
8. Click OK again in the PivotTable field dialog box.
   
   Step 3 of the PivotTable Wizard appears.
9. Click Next, to set more options, or click Finish to complete the PivotTable.

Using AutoShow in an New PivotTable

1. Open the workbook in which you want to create the PivotTable.
2. If you want to base the PivotTable on a Microsoft Excel list or database, click a cell in the list or database.
3. Start the PivotTable wizard by clicking PivotTable Report on the Data menu.
4. Follow the instructions in the wizard until you see step 3 of the wizard.
5. In step 3 of the PivotTable Wizard, construct the PivotTable by dragging the desired field buttons into position on the diagram.
6. After you construct the PivotTable, repeat steps 2 to 9 in the "To Use AutoShow in an Existing PivotTable" section.

Example

To use AutoShow to display the top three student grades, use the following steps:

1. Type the following data in a new worksheet:

   A1: Student B1: Score
   A2: Bob B2: 86
   A3: Sam B3: 59
   A4: Mary B4: 0
   A5: Susan B5: 96
   A6: Jim B6: 78
   A7: Sally B7: 95

2. Click cell A1 to select it, and then click PivotTable Report on the Data menu.
3. In step 1 of the PivotTable Wizard, click "Microsoft Excel List or database," and then click Next.
4. In step 2 of the PivotTable Wizard, type $A$1:$B$7 for the range. Click Next.
5. In step 3 of the PivotTable Wizard, drag the Student button to the Row field, and then drag the Score button to the Data field.
   
   NOTE: Score is titled "Sum of Score" after you drag it to the Data field.
6. Double-click "Sum of Score" to display the PivotTable Field dialog box. In the Summarize By list, click Average and click OK.
7. Double-click Student to display the PivotTable Field dialog box for row fields, and then click Advanced.
8. In the PivotTable Field Advanced Options dialog box, click Automatic under AutoShow Options. Click Top in the Show box and change the number to 3. This step allows you to see only the top three scores from the list. Click OK, click OK again, and then click Next.
9. In step 4 of the PivotTable Wizard, click Existing worksheet, and then click cell D1 on the active sheet to place the PivotTable Report in cell D1. Then, click Finish.
   
   Bob, Sally and Susan and their corresponding scores appear in the PivotTable.
10. Type 100 in cell B4 to give Mary a score of 100.
11. Click cell D1 to select it, and then click Refresh Data on the Data menu.
    
    Mary is listed in the PivotTable because she is part of the top three items.