XL97: How to Use the PivotTable AutoSort Feature

In Microsoft Excel 97, there is a new PivotTable feature called AutoSort. This feature allows you to automatically sort a PivotTable when you create it, or when you refresh a PivotTable after you change data in the PivotTable.

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 PivotTable fields in ascending or descending order; however, if you refresh the PivotTable after you make edits or additions to the source data you may get unexpected results. For example, the PivotTable may append new items to the end of the list rather than sort the items within the list. To sort a PivotTable in which you changed data, you must sort the PivotTable after each refresh.

The new AutoSort feature for PivotTables in Microsoft Excel 97 automatically sorts a PivotTable despite changes you make to the underlying data. You can access the following three basic AutoSort settings in the "PivotTable Field Advanced Options" dialog box: Manual, Ascending, and Descending. If you select Ascending or Descending, fields that you specify are sorted in ascending or descending order respectively.

To access AutoSort, use the following methods according to whether the PivotTable already exists.

Existing PivotTable

1. Select any single cell in the PivotTable, and then click the PivotTable Wizard on the PivotTable toolbar.
   
   Step 3 of the PivotTable Wizard appears.
2. Double-click the field button you want to sort in the Row or Column fields.
3. In the PivotTable Field dialog box, click Advanced.
4. In the "PivotTable Field Advanced Options" dialog box, click Ascending or Descending.
   
   NOTE: You can click a different field in the "Using field:" list.
5. Click OK.
6. Click OK again in the PivotTable field dialog box.
   
   Step 3 of the PivotTable Wizard appears.
7. Click Next, to set more options, or click Finish to complete the PivotTable.

New PivotTable

1. Open the workbook where you want to create the PivotTable.
2. If you are basing 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 7 in the "Existing PivotTable" section.

For more information about working with PivotTables, click the Index tab in Microsoft Excel Help, type the following text and then double-click the selected text to go to the "Sort data in a PivotTable" topic.