XL: PivotTable Fields Display in Incorrect Order

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In the versions of Microsoft Excel listed above, when you use the PivotTable Wizard, you may not be able to add fields below the last field in the row, column, or data area. When this occurs, the added field pushes the last displayed field out of view. Because of this, the order in which you add fields to the table in the PivotTable Wizard is not reflected in the resulting pivot table.

Additionally, you may not be able to edit all of your row, column, or data fields in the PivotTable Wizard.

CAUSE

The PivotTable Wizard in Microsoft Excel 5.x and 7.x can only display up to 12 row fields, 9 column fields, and 5 data fields; although you can add more fields to the PivotTable with the PivotTable Wizard, you cannot add them to the bottom of the list of fields once the list reaches these limits. You can only add fields above the last field displayed. When you do this, the last field is pushed out of view.

After you add the limit of fields that can be displayed in the PivotTable Wizard, additional fields that you add are added in the wrong order. For example, if you add seven fields to the Data area in the PivotTable Wizard, the first four fields are displayed in the order in which you added them, but the last three fields are added in the opposite order in which you added them, and display in this incorrect order in the resulting pivot table.

WORKAROUND

Adding Fields in the Correct Order

To add a number of fields in the PivotTable Wizard so that they appear in the order in which they are listed on the worksheet, do the following:

1. Add the first 4 data fields in the order in which you want them to appear in the pivot table.

2. Add additional fields in the reverse of the order in which you want them to appear.

Note that you can also change the order of the fields after you create the PivotTable by dragging the fields to the new location.

Editing and Moving Data Fields

To edit data fields not visible in the PivotTable Wizard, select the desired data field in the pivot table and choose PivotTable Field from the Data menu or a shortcut menu. To access the shortcut menu for a data field, select one of the data fields in the PivotTable and click the right mouse button.

To move data fields not visible in the PivotTable Wizard, select the cell containing the data field to move, and drag the data to the desired location in the PivotTable.

Editing and Moving Row and Column Fields

To edit row or column fields not visible in the PivotTable Wizard, double- click the desired field button in the pivot table or select the desired field button and choose PivotTable Field from the Data menu.

To move row or column fields not visible in the PivotTable Wizard, drag the field button to the desired location in the pivot table.

STATUS

This is a known problem in the versions of Microsoft Excel listed at the beginning of this article. In Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition, these limitations do not apply; these versions of Microsoft Excel have been redesigned to allow more row, column, page, and data fields.

REFERENCES

"User's Guide," version 5.0, Chapter 4