SUMMARY
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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
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.
back to the top
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
back to the top