This article was previously published under Q162386
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel 97, you can use a new PivotTable feature called AutoShowto quickly analyze a large set of data to find specific results. Forexample, you can use the AutoShow feature to find the top ten salespeopleper region based on sales figures.
A PivotTable is an interactive table that summarizes and analyzes data fromexisting lists and tables. In earlier versions of Microsoft Excel, it ispossible to sort by PivotTable data fields and to hide items from a row orcolumn field. It is also possible to hide PivotTable field items. However,there is no feature that automatically displays a subset of PivotTablefield data that is updated as the PivotTable is updated.
Microsoft Excel 97 allows you to easily display a subset of the PivotTabledata 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 Exceldisplay the top ten students per teacher based on test grades or the bottomthree golfers per golf course based on golf scores. Each time thePivotTable is updated with new data, Microsoft Excel recalculates anddisplays the top or bottom items.
You can access the following two basic AutoShow settings in the "PivotTableField Advanced Options" dialog box: Manual, or Automatic. If you selectAutomatic, you can specify whether to show the bottom or top items, and youcan specify the number of items to show for the field. Also, when youselect Automatic, you can specify the data field to use when you displaythe top or bottom items.
NOTE: When you select a field and use the AutoShow feature, the field namein the PivotTable is formatted in blue to indicate the state of the field.
Using AutoShow in an Existing PivotTable
Select any cell in the PivotTable, and then click PivotTable Report on the Data menu.
Step 3 of the PivotTable Wizard appears.
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.
In the PivotTable Field dialog box, click Advanced.
In the "PivotTable Field Advanced Options" dialog box, under AutoShow Options, click Automatic.
Click either Top or Bottom in the Show box, and then specify a number for the number of items to display.
Click the data field to use for the subset in the Using Field list.
Click OK again in the PivotTable field dialog box.
Step 3 of the PivotTable Wizard appears.
Click Next, to set more options, or click Finish to complete the PivotTable.
Using AutoShow in an New PivotTable
Open the workbook in which you want to create the PivotTable.
If you want to base the PivotTable on a Microsoft Excel list or database, click a cell in the list or database.
Start the PivotTable wizard by clicking PivotTable Report on the Data menu.
Follow the instructions in the wizard until you see step 3 of the wizard.
In step 3 of the PivotTable Wizard, construct the PivotTable by dragging the desired field buttons into position on the diagram.
After you construct the PivotTable, repeat steps 2 to 9 in the "To Use AutoShow in an Existing PivotTable" section.
To use AutoShow to display the top three student grades, use the followingsteps:
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
Click cell A1 to select it, and then click PivotTable Report on the Data menu.
In step 1 of the PivotTable Wizard, click "Microsoft Excel List or database," and then click Next.
In step 2 of the PivotTable Wizard, type $A$1:$B$7 for the range. Click Next.
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.
Double-click "Sum of Score" to display the PivotTable Field dialog box. In the Summarize By list, click Average and click OK.
Double-click Student to display the PivotTable Field dialog box for row fields, and then click Advanced.
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.
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.
Type 100 in cell B4 to give Mary a score of 100.
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.
For more information about Autoshow, click the Index tab in Microsoft ExcelHelp, type the following text
and then double-click the selected text to go to the "Control the amount ofdetail that appears in a PivotTable" topic. Then, click "Display the top orbottom items for a PivotTable field.