XL97: Limits of PivotTables in Microsoft Excel 97

ID: Q157486

The information in this article applies to:

SUMMARY

This article discusses some of the limitations of PivotTables in Microsoft Excel 97.


MORE INFORMATION

PivotTables are interactive tables that quickly summarize, or cross-tabulate, large amounts of data. Within a PivotTable, you can rotate the rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest.

What Are the Limits of PivotTables That I Should Know About?

The following limits apply to PivotTables in Microsoft Excel 97.

Maximum Size

There is no fixed maximum size. The maximum size is usually limited only by the amount of available memory on your computer.

Column Fields

The product of the number of items in all column fields in a PivotTable cannot exceed 32,768.

For example, assume you create a PivotTable that contains five column fields. The fields contain 10, 5, 2, 40, and 3 items respectively. The product of these values is 10 x 5 x 2 x 40 x 3, or 12,000.

If you try to add one more field that contains three items, the product would be 12,000 x 3, or 36,000. Because this number exceeds the maximum product of the items, you receive the following error message:
Not enough memory to completely display PivotTable.
Note that worksheets in Microsoft Excel are limited to 256 columns. Because of this, even if you are successful in creating a PivotTable that contains a large number of column fields, you may not be able to display the entire expanded PivotTable.

Row Fields

The product of the number of items in all row fields in a PivotTable cannot exceed 2^31 (2 raised to the 31st power), or approximately 2.1 billion items. The same logic that applies to column fields also applies to row fields.

Maximum Number of Records

There is no fixed maximum number of records that you can use when you create a PivotTable.

In practice, creating a PivotTable from an external database that contains a very large number of records can strain the performance of the workstation on which Excel is running, and can take a very long time to complete.

If you are creating a PivotTable from a very large database, you may want to use server page fields in the PivotTable. See the following section for information about how to use server page fields in PivotTables.

Items Per Field

There is a limit of 8,000 items per field.

How Can I Avoid Running into These Limits?

One way to avoid running into these limits is to use page fields in PivotTables, especially if fields contain more than 40 unique items. Using page fields makes your PivotTable more memory-efficient and reduces the size (in terms of cells) of the PivotTable. This makes the PivotTable easier to read.

Another way to optimize the PivotTable is to use server page fields.

For additional information about how to use server page fields in Microsoft Excel, please see the following article in the Microsoft Knowledge Base:
Q157488 XL97: Using Server Page Fields in PivotTables


REFERENCES

For more information about PivotTables in Microsoft Excel, click Contents And Index on the Help menu, click the Index tab in Excel Help, type the following text

PivotTables, overview
and then double-click the selected text to go to the "PivotTables: Analyzing data interactively" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: 8.00 XL97 pivot tables table 


Keywords          : kbtool xlpivot 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: July 21, 1999