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.
PivotTable Wizard Step 1: Where Is the Data That You Want to Analyze?
When Excel creates the cache for a PivotTable, both the original data and the cached data may be kept in memory, depending on the data source option that you select. If your data source is large or if you are creating several PivotTables from separate data sources, these multiple copies of data may use up your computer's available memory, which will adversely affect the performance of Excel. To save memory, you can use a closed original data source or a single data source when you create multiple PivotTables.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.
Microsoft Excel List or Database, or Multiple Consolidation Ranges
When you select Microsoft Excel list or database or Multiple consolidation ranges as your data source, if this data is in an open sheet, two copies of the data are kept in memory when Excel creates the PivotTable. To conserve memory, close the workbook that contains your original data, and create the PivotTable in a separate workbook. This way, only the cached data is kept in memory.External Data Source
When you select the External data source option, only one copy of the source data is kept in memory. If you select this option and then click the Get Data button in step 2 of the PivotTable Wizard, Microsoft Query starts.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.
Another PivotTable or PivotChart
The Another PivotTable or PivotChart option is available whenever you have another PivotTable in the same workbook. When you use this option, both PivotTables use the same cache, 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.
PivotTable Wizard Step 3 - Save Data With Table Layout
The Save data with table layout check box in step 3 of the PivotTable Wizard affects the file size of your workbook. It also affects the time that is required to save, reload, and refresh your workbook.The check box is selected by default. The benefits and tradeoffs for leaving it selected versus clearing it are described in the following sections.
Selected (On)
If the Save data with table layout check box is selected, when you save your workbook, Excel saves the cached data that is used to create your PivotTable. As a result, the time that is required to save the workbook increases because more information is saved with it and the file size is larger.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.
Cleared (Off)
If the Save data with table layout check box is not selected, Excel does not save the cached data with your workbook. As a result, the time that is required to save the workbook is reduced. The file size is also reduced.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.
Summary of Actions
Selecting the check box means slower save time and larger file sizes; clearing the check box 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 Selected Not selected ------------------------------------- Saving Longer Shorter Opening Same Same Refreshing Shorter Longer File Size Larger Smaller