Microsoft Office Tutorials and References
In Depth Information
Chapter 14: Using Pivot Tables
3. Click the Change Data Source button, as demonstrated in Figure 14-14.
4. Change the range selection to include any new rows or columns (see Figure 14-15).
5. Click OK.
Figure 14-14: Changing the data range that feeds your pivot table.
Figure 14-15: Select the new range that feeds your pivot table.
Pivot tables and worksheet bloat
It’s important to understand that pivot tables do come with space and memory implications for your
dashboards and reports. When you create a pivot table, Excel takes a snapshot of your source data
and stores it in a pivot cache. A pivot cache is essentially a memory container that holds this
snapshot of your data. Each pivot table that you create from a separate data source creates its own pivot
cache, which increases your workbook’s memory usage and file size. The increase in memory usage
and file size depends on the size of the original data source that Excel duplicates to create the pivot
Simple enough, right? Well, here’s the rub: You often need to create separate pivot tables from the
same data source in order to create two distinct analysis layers in your data model. If you create two
pivot tables from the data source, Excel automatically creates a new pivot cache even though one
may already exist for the same data source. This means that you’re bloating your worksheet with
redundant data each time you create a new pivot table using the same data source.
To work around this potential problem, you can use the copy and paste commands. That’s right;
simply copying a pivot table and pasting it somewhere else creates another pivot table, without
duplicating the pivot cache. This enables you to create multiple pivot tables that use the same
source data, with negligible increase in memory and file size.