What Are the Limits of PivotTables?The following limits apply to PivotTables.
Maximum SizeThere is no fixed maximum size. The maximum size is usually limited only by the amount of available memory on your computer.
Column FieldsThe 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:
Row FieldsThe 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 RecordsThere 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 FieldThere 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:
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:
Help menu, type About PivotTable Reports: interactive data analysis in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
รหัสบทความ: 211517 - การตรวจสอบครั้งสุดท้าย: 19 เม.ย. 2005 - ฉบับแก้ไข: 1