Microsoft KB Archive/291061

= Description of the limits of PivotTables in Excel 2002 =

PSS ID Number: 291061

Article Last Modified on 10/5/2004

-

The information in this article applies to:


 * Microsoft Excel 2002

-



This article was previously published under Q291061





For a Microsoft Excel 2003 version of this article, see 820742.

For a Microsoft Excel 2000 version of this article, see 211517.

For a Microsoft Excel 97 version of this article, see 157486.



For a Microsoft Excel for Macintosh version of this article, see 179331.



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



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?
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 and Row Field Limitations
The product of the number of items is limited to 16,382 actual axis intersections (intersections which contain an actual data point) for column fields, 65,534 actual axis intersections for row fields.

If you try to add a field that exceeds the maximum product of the items, you receive the following error message:

Excel cannot display this PivotTable report. The row area has more than 65,536 items, or the column area has more than 16,384 items. Note that numbers in error message are not precise.

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 32,500 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.

