Description of the limits of PivotTable reports in Excel (820742)

MORE INFORMATION

PivotTable reports are interactive tables that quickly summarize, or cross-tabulate, large amounts of data. In a PivotTable report, 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 PivotTable Reports?

The following limits apply to PivotTable reports.

Maximum Size

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

Column and Row Field Limits

Column and row fields are typically limited only by the available memory on your computer.

Maximum Number of Records

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

Column Fields

The product of the number of items in all column fields in a PivotTable cannot exceed 32,768. For example, if 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: Note that these numbers are only an example. Excel will only store actual data intersections in memory so that you can display fields whose products may appear larger than 32,768. Also 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 whole 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.

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

If you are creating a PivotTable report from a very large database, you may want to use server page fields in your PivotTable report.

Maximum Number of Items for Each Pivot Field

There is a limit of 32,500 unique items for each row field, column field, or page field. If you try to drop a field that exceeds this limit into your PivotTable report, the field is not added to the PivotTable report, and you may receive the following warning message:

Actual (vs. Theoretical) Intersection Limits

Excel 2002 implements actual intersection limits. These limits better use allocated memory. In Excel 2000 and earlier versions, Excel allocates a 32-bit key for every potential data intersection. For example, if you have 3 PivotFields on the row axis and they contain 50, 30, and 20 unique items respectively, Excel 2000 allocates 50*30*20 theoretical unique intersections among those items. Most of the time, the vast majority of these theoretical intersections do not really exist because there is no data point that corresponds to these intersections. Therefore, Excel 2000 PivotTables can be inefficient in memory usage. Excel 2002, by implementing actual intersection limits, allocate memory only to the intersections that actually contain data to make more efficient use of memory.

How Can These Limits Be Avoided?

To avoid these limits, you can use page fields in PivotTable reports, especially if fields contain more than 40 unique items. Page fields make your PivotTable report more memory-efficient and reduce the size of the PivotTable report (in terms of cells). This makes the PivotTable report easier to read. Another way to optimize your PivotTable report is to use server page fields.