Reporting and Analysis
for GoldMine CRM and QuoteWerks quote management
Support Center > Knowledge base> Article: Pivot Table limitations

Pivot Table limitations

Article ID: 31691

Question
Slow updating or Pivot Tables accept only a few Row or Column Fields simultaneously.

Answer
A pivot table's "dynamic" update gets slower as you increase the amount of information you display.  There's a full explanation of why this is below.  
 
The main purpose of a pivot table to summarize large amounts of data in a small place, not display it all in detail. However, sometimes your report need is to create a many-page list, so here are some tips:
 
1. Keep fields that you are "filtering" on in the Page area of your pivot table. (Learn about the parts of a pivot table quickly, HERE.)
 
Example:  Inexperienced users sometimes assume they need to display every field they use as a criterion.  Suppose you are showing sales results for a particular sales rep.  You might think you need to put the ""Sales Rep"" field into the pivot table's row area and then de-select all other sales reps.  This is a lot of unnecessary work, but displays the sales rep's name with the data, like in a traditional report.  Much easier:  Leave the ""sales rep"" field in the page area and select the desired rep from the dropdown list next to it.
 
2. Display only the fields you need in the Row and Column areas.  Putting fields in display because you "might" need them will waste more time in refresh latency than moving them there WHEN you need them will.
 
Don't make the mistake of trying to display everything all at once!  Microsoft designed the Pivot Table as a summary and analytical tool. The purpose and power of dynamic tables is to display only what you need, when you need it, at the top of the page by selecting options, filtering, and thoughtfully organizing your data. 
 
3. Don't try to make a single report be all things to many people.  It's much easier (though counterintuitive) to make many versions of the same report -- even in the same workbook, or in many workbooks, each of which tells one brief story for a particular use.  It's easy to create and destroy workbooks as you need them.  It's hard to cater to many people with one workbook, especially over time.
 
Don't know how?  Get training!  MasterMine can summarize and display almost any amount of data, if you are smart about using the functionality.  (For easy online training, see our Training Webpage.)
 
 
A pivot table can be made to simulate a regular "line listing" with columns easily dragged to any order you want, but what's the point?  You can always drill down (double-click) on the grand total of a simple pivot table and get such a line listing instantly!
 
 

Practical limits explained:

There are practical but not very specific limits on the number of unique items within fields which can be simultaneously displayed in rows and columns. As long as the data are in the page area, the limitation is primarily a function of RAM. However, once they are brought down into the pivot table, you begin to create an "n-dimensional cube", and you run into pre-defined limits. 

Older versions of Excel (2003 and prior) had severe limits on how many items a pivot table field could hold, and how many "intersections" (column times row items) a pivot table could handle in its "cube".  This limit has grown with more powerful machines and greater memory limits, but at some point math overpowers technology, the power of powers overcomes available memory and processing power. 

It's easiest to understand in the context of older Excel, because the numbers are more comprehensible.  The same kind of limits still exist, but they are much higher and you can still overwhelm them with too much data:

Row Fields:
For Office XP and higher: 
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.  In practice, this vastly increases the number of fields you can add to your row or column area.

Note: 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.

For Office 2000: 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.

Assuming you wanted to build a report whose first several columns were personal data, like names and phone numbers, and that each name and number was unique in the report, you could include the following before running into the 2.1 billion limit:

3 columns averaging 1,285 unique items each,
4 columns averaging 215 unique items each,
5 columns averaging 73 unique items each, or
6 columns averaging 35 unique items each, etc.

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.

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.  For Office 2000 or earlier, Excel reserves only about 100 MB of RAM for a pivot table.  Striking this limit may result in an ""Out of Memory"" error.  If this occurs, try un-including all non-essential fields from the query to reduce its size in memory.

Maximum Items Per Field: A pivot table cannot display a field containing more than 32,500 (8,000 for Office 2000 or lower) unique field items. If you encounter this limit, you may use the pivot table wizard and MSQuery to alter the background query criteria to include fewer records.

Again, note that these specific limitations affect Office versions XP and earlier.  The limits in current Office are much higher.


related articles

Article Details
Views: 5122 Created on: Nov 15, 2016

Please let us know what you think of this article.
Poor
Outstanding