XL97: Limits of PivotTables in Microsoft Excel 97Last reviewed: January 15, 1998Article ID: Q157486 |
The information in this article applies to:
SUMMARYThis article discusses some of the limitations of PivotTables in Microsoft Excel 97.
MORE INFORMATIONIn Microsoft Excel, PivotTables are interactive tables that quickly summarize, or cross-tabulate, large amounts of data. Within a PivotTable, you can rotate its 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:
Maximum Size ------------ In PivotTables in Microsoft Excel, there is no fixed maximum size. The maximum size is usually limited only by the amount of available memory in your computer. Column Fields ------------- The product of the number of items in all column fields within a PivotTable cannot exceed 32768. For example, suppose 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 12000. If you tried to add one more field that contained 3 items, the product would be 12000 x 3, or 36000. Since this exceeds the maximum product, you would receive the 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 within a PivotTable cannot exceed 2^31 (2 raised to the 31st power), or approximately 2.1 billion. 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 can be used when creating a PivotTable. In practice, creating a PivotTable from an external database that contains a very large number of records can be a great strain on the workstation where Microsoft 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 your PivotTable. See the next section for information on how to use server page fields in PivotTables. How Can I Keep from 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 cells) of the PivotTable, making it easier to read. Another way to optimize your PivotTable is to use server page fields. For additional information on how to use server page fields in Microsoft Excel, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q157488 TITLE : XL97: Using Server Page Fields in PivotTables REFERENCESFor more information about PivotTables in Microsoft Excel, click the Index tab in Microsoft Excel Help, type the following text
PivotTables, overviewclick Display, click "PivotTables: Analyzing data interactively", and then click Display.
|
Additional query words: 8.00 XL97 pivot tables table
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |