One of the key features of Pivot Tables is to summarize the information quickly. Another great advantage is its ability to look at percentages – percent of total, percent of grand total, percent of row total, running total in etc.
Yet another fantastic feature is the ability to group data in Excel – either by existing columns, or by creating your own custom logic. Data can be grouped by Text Columns or even Date Columns. And the result is instantly summarized. Amazing stuff!
All is good so far… The problem crops up when you have created some grouping, and then decide to build another pivot table to get another view of the same data, while keeping the original pivot table in place.
The second pivot table automatically groups the data based on the first pivot’s grouping. And if you change the grouping on the second pivot, the first pivot table changes too.. Bo hoo hoo 🙁
This can be frustrating and sometimes difficult to troubleshoot or fix.
Why does a Pivot Table share its Grouping with another Pivot Table?
That’s because both the pivot tables are sharing the same pivot cache. To understand better, when Excel creates a pivot table, it makes a copy of the entire source data, and creates a temporary pivot cache in the memory. This duplicated cache is now stored with the Excel file, doubling its size. If your source data was huge, the excel file soon soars in size too.
To save hard drive space and memory, when the second pivot table was created, it used the same cache as the first pivot table. This makes sense, but then since the cache is shared, change in the cache for one pivot table affects the other pivot table too.
The solution is to have a separate pivot cache for the second pivot table.
How to Create a Separate Cache for the Second Pivot Table?
Although this method will use up more memory, it is a good solution, works well, and hardly takes any time to implement.
Select the source data, go to the Formulas tab, and click on Define Name button.
Key in a unique name in the popup. Let’s say you call this DataSet1. This creates a Unique Named Range.
Now click on the Define Name button once more, and create another name for the same data set. Let’s call this DataSet2.
Create Pivot Tables with Unique Data Sets
Now create the first pivot table based on the first Data set (DataSet1). Group on whatever fields you want. Once you are happy with the result, do the same thing for the second pivot table.
Just remember to use the second data set for the second pivot table (DataSet2). Group the data on a different field.
Want to Improve Your Excel Skills?
Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.
To Get Most out of Excel, Learn the Pivot Table techniques in our
Pivot Table Masterclass Training!
You will notice that the grouping of the second table stays, and it does not alter the grouping order of the first pivot table. That’s because we are using different defined names.
Thus, Excel creates two different pivot caches, and even though both refer to the same data set, it is transparent to Excel, and is of no consequence.
Now you can enjoy the benefits of two different views, one with one set of grouping, and another with another set of grouping.
Although this method inflates the size of the file, it is a quick and dirty method, that works well. Try it out… and let me know how it goes…
Cheers,Vinai Prakash,
Founder of ExcelChamp.Net
Additional Resources:
If you found this tip useful, you may want to subscribe to the ExcelChamp Weekly Excel Tips Newsletter.
And check out these cool Excel Tips too…
- How to find duplicates in Excel
- How to Show Values and Percentages in Excel Pivot Tables
- Removing Grid Lines from a Section of Cells in Excel
Cheers – Vinai
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
