In Microsoft Excel 97, you can use a new PivotTable feature called AutoShow
to quickly analyze a large set of data to find specific results. For
example, you can use the AutoShow feature to find the top ten salespeople
per region based on sales figures.
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
- 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.
- 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
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
- Follow the instructions in the wizard until you see step 3 of the
- 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 following
- 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
- 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
- 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
- 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
Mary is listed in the PivotTable because she is part of the top three
For more information about Autoshow, click the Index tab in Microsoft Excel
Help, type the following text
and then double-click the selected text to go to the "Control the amount of
detail that appears in a PivotTable" topic. Then, click "Display the top or
bottom items for a PivotTable field.
Article ID: 162386 - Last Review: October 26, 2013 - Revision: 2.0
- Microsoft Excel 97 Standard Edition
|kbnosurvey kbarchive KB162386|