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?
The following limits apply to PivotTables.
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 your PivotTable. See
the following section for information about how to use server page fields in
PivotTables.
Maximum Number of Items per Pivot Field
There is a limit of 8,000 unique items per row field, column
field, or page field. If you try to drop a field into your PivotTable that
exceeds this limit, the field will not be added to the PivotTable, and you may
receive the following warning message:
A field in your
source data has more unique items than can be used in a PivotTable. Microsoft
Excel may not be able to create the PivotTable or may create the PivotTable
without the data from this 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 your PivotTable is to use server page
fields.
For additional information about how to use server page
fields in Excel, please see the following article in the Microsoft Knowledge
Base:
211515 Using server page fields in
PivotTables