Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to optimize PivotTable performance in Excel 2000
Article ID: 273583 - View products that this article applies to.
This article was previously published under Q273583
This step-by-step article describes the options in steps 1 and 3 of the PivotTable Wizard, and explains how they affect memory usage, speed, and file size.
When you create a PivotTable, Microsoft Excel creates a hidden copy (cache) of the source data that it uses to perform aggregations and other PivotTable functions. By using this cache, Excel can quickly calculate PivotTables, while maintaining the integrity of the original data.
This cache can affect memory use, speed, and file size, depending on the options that you select when you create your PivotTable.
The memory usage implications for each of the data source options in step 1 of the PivotTable Wizard are described in more detail in the following sections.
When you then return a query result to Microsoft Excel, one full copy of the source data is stored in memory (cached) rather than returned to a sheet. Although Microsoft Query remains running 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 whole data source, and it remains in memory only temporarily until you quit the PivotTable Wizard.
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.
The check box is selected by default. The benefits and tradeoffs for leaving it selected versus clearing it are described in the following sections.
However, when you reopen the workbook, Excel does not load the cached data until you pivot, edit, or refresh an existing PivotTable, or until you create a new one from the existing cache. By doing so, Excel preserves memory until it is needed.
However, when you reopen the workbook, you must update the static PivotTable by clicking Refresh Data on the Data menu, before you pivot or edit the PivotTable. The Refresh Data command re-creates a copy of the cached data. This process is slower than saving the cache with the workbook and having it load on demand.
Action Selected Not selected ------------------------------------- Saving Longer Shorter Opening Same Same Refreshing Shorter Longer File Size Larger Smaller