Creating a basic pivot table
- By Bill Jelen
- 2/17/2025
- Format your source data before creating a pivot table
- How to create a basic pivot table
- Understanding the Analyze Data, Copilot, and Recommended PivotTable features
- Using slicers to filter your report
- Keeping up with changes in the data source
- Sharing the pivot cache or creating a new cache
- Saving time with PivotTable tools
- Next steps
Sharing the pivot cache or creating a new cache
You quite often need to analyze the same data set in multiple ways. In most cases, this process requires you to create separate pivot tables from the same data source. Keep in mind that every time you create a pivot table, you are storing a snapshot of the entire data set in a pivot cache. Every pivot cache that is created increases your memory usage and file size. For this reason, you should consider sharing your pivot cache.
NOTE
In situations where you need to create multiple pivot tables from the same data source, you can use the same pivot cache to feed multiple pivot tables. By using the same pivot cache for multiple pivot tables, you gain a certain level of efficiency when it comes to memory usage and file size.
In legacy versions of Excel, when you created a pivot table using a data set that was already being used in another pivot table, Excel actually gave you the option of using the same pivot cache. However, Excel today does not give you such an option.
Instead, each time you create a new pivot table in Excel, Excel automatically shares the pivot cache. Most of the time, this is beneficial: You can link as many pivot tables as you want to the same pivot cache with a negligible increase in memory and file size.
On the flip side, when you group one pivot table by month and year, all the pivot tables are grouped similarly. If you want one pivot table by month and another pivot table by week, you have to force a separate pivot cache. You can force Excel to create a separate pivot cache by taking the following steps:
Select one cell in your original data set.
Press and release Alt+D, and then press P to launch the PivotTable Wizard.
Click the Next button to get past the first screen of the wizard.
On the second screen, select the range for your pivot table and click the Next button.
Excel displays a wordy message saying that you can use less memory if you click Yes. Instead, click No.
On the next screen, click the Finish button.
At this point, you have a blank pivot table that pulls from its own pivot cache.
TIP
If you already have an existing pivot table, you can use an alternative method for creating a separate pivot cache: Copy and paste the existing table to a new workbook and then copy and paste the pivot table back to a new sheet in the original workbook.
Side effects of sharing a pivot cache
It’s important to note that there are a few side effects to sharing a pivot cache. For example, suppose you have two pivot tables using the same pivot cache. Certain actions affect both pivot tables, each of which is discussed further in Chapter 5, “Performing calculations in pivot tables”:
Refreshing your data—You cannot refresh one pivot table and not the other. Refreshing affects both tables.
Adding a calculated field—If you create a calculated field in one pivot table, your newly created calculated field shows up in the PivotTable Fields list of the other pivot table.
Adding a calculated item—If you create a calculated item in one pivot table, it shows in the other as well.
Grouping or ungrouping fields—Any grouping or ungrouping you perform affects both pivot tables. For instance, suppose you group a date field in one pivot table to show months. The same date field in the other pivot table is also grouped to show months.
Although none of these side effects are critical flaws in the concept of sharing a pivot cache, it is important to keep them in mind when determining whether using a pivot table as your data source is the best option for your situation.