MORE INFORMATION
In Microsoft Excel, PivotTables are interactive tables that quickly
summarize, or cross-tabulate, large amounts of data. In 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 LIMITATIONS OF THE PIVOTTABLE FEATURE SHOULD I KNOW ABOUT?
The following limitations apply to PivotTables in the versions of Microsoft Excel listed at the beginning of this article.
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 on
the workstation on which 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 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 approximately 4,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.