XL: Optimizing PivotTable Performance

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, version 5.0
• Microsoft Excel 98 Macintosh Edition

SUMMARY

When you create a PivotTable, Microsoft Excel creates a hidden copy (cache) of the source data that is used to perform aggregations and other PivotTable functions. By using this cache, Microsoft Excel can quickly calculate PivotTables and, at the same time, maintain the integrity of the original data. This cache can affect memory use, speed, and file size depending on options that you select when you create your PivotTable.

MORE INFORMATION

The following information outlines the options in Steps 1 and 4 of the PivotTable Wizard and explains how they affect memory use, speed, and file size.

PivotTable Wizard Step 1 - Create Table From Data In

When the cache for a PivotTable is created, you may have both the original data and the cached data in memory, depending on the data source option you selected. If your data source is large or if you are creating several PivotTables from separate data sources, the multiple copies of the data in memory may deplete your computer's available memory, which affects Microsoft Excel's speed and performance. You can save memory if you use a closed original data source or a single data source when you create multiple PivotTables. The memory use implications for each of the data source options in the PivotTable Wizard Step 1 dialog box are described in further detail in this article.

Microsoft Excel List Or Database

or Multiple Consolidation Ranges

When you use Microsoft Excel List Or Database or Multiple Consolidation Ranges as your data source, if this data is in an open sheet, you will have two copies of the data in memory when the PivotTable is created. To conserve memory, close the workbook containing your original data and create the PivotTable in a separate workbook. This way, only the cached data will be in memory.

Another PivotTable

This option is available whenever you have another PivotTable in the same workbook. When you use this option, Microsoft Excel uses the same cache that is used by the other PivotTables, which limits the number of copies of the source data in memory. Use this option whenever you create multiple PivotTables from the same source data. Note that the PivotTables must all be in the same workbook to use a single cache.

External Data Source

When you use the External Data Source option, there will effectively be one copy of the source data in memory. Selecting this option in conjunction with the Get Data button in Step 2 of the PivotTable Wizard starts Microsoft Query. When you then return a query result to Microsoft Excel, the data is stored in memory (cached) rather than returned to a sheet. This constitutes one full copy of the source data. Although Microsoft Query remains open until you are finished with the PivotTable Wizard, only the records visible in the data grid of Microsoft Query are stored in memory. This is often a small percentage of the entire data source and it remains in memory only temporarily until the PivotTable Wizard is closed.

PivotTable Wizard Step 4 - Save Data With Table Layout

The Save Data With Table Layout option in Step 4 of the PivotTable Wizard affects the file size of your workbook as well as the time required to save it and refresh a PivotTable after reloading it. This option is selected by default; the benefits and tradeoffs for leaving it selected and clearing it are further described below.

Selected (On)

When the Save Data With Table Layout option is selected (on), the cached data used to create your PivotTable is saved when you save your workbook. This may increase the time required to save the workbook because more information is saved with it and the file size will be larger. However, when you reopen the workbook, the cached data is not loaded until you pivot, edit, or refresh an existing PivotTable or create a new one from the existing cache. This reserves memory until it is needed.

Cleared (Off)

When you clear this option (turn it off), the cached data is not saved with your workbook. As a result, the amount of time necessary for saving your file will decrease as will the file size. However, when you reopen the workbook, you will have a static PivotTable that you will need to update by choosing Refresh Data from the Data menu prior to pivoting or editing the PivotTable. This command will recreate a copy of the cached data. This process is slower than saving the cache with the workbook and having it load on demand.

Turning the option on means slower save time and larger file sizes; turning the option off means slower refresh time when you reopen the workbook and smaller file sizes. In either case, the actual time it takes to open the file will be the same.

     Action      Option On    Option Off
     -----------------------------------

     Saving      Longer       Shorter
     Opening     Same         Same
     Refreshing  Shorter      Longer
     File Size   Larger       Smaller