In this blog, you will learn how to group data in pivot table with 3 simple methods. Read more to understand how you can easily do it.
A Common Pivot Table Problem
The basic concept which led to today’s Pivot Tables, was invented by the American engineer Pito Salas in 1987. Since they were introduced into Excel 1993, Pivot Tables became very popular among Excel users… Knowledge of Pivot Tables is a common requirement in Job Specifications.
In this article I tackle a common problem that happens when grouping data in more than one Pivot Table. While Excel tries to save me memory, it messes up my Pivot Tables.
Let’s understand the problem then check three possible solutions.
You can Download the Exercise File and follow along by clicking Here.
Group data in pivot table tutorial:
- We create our first pivot Table and group the Date column.
- We then create our second Pivot Table, Group differently and the problem pops up.
- We then explore the reason of the problem and introduce the “Pivot Caches”.
- The first solution will be changing the Source Data… But it has a disadvantage
- The second solution is by using an Old Tool not available on the Excel Interface.
- The best option is to send your list to the Data Model.
Creating the First Pivot Table.
Our worksheet shows a list of transactions for 2017- 2019.I want to find the Sum of Sales for each Quarter in each year. So, I’ll be creating a Pivot Table in a New worksheet.
To create a Pivot Table, I select any single cell in the list and Click on the Insert Tab of the Ribbon then click on Pivot Table (to the left side). The Create Pivot Table wizard appears, and it recognizes my source list. I’ll accept the defaults to create the Pivot Table in a New Worksheet. Note that we have 351 rows.
A new worksheet is created and it Shows a Pivot Table Placeholder, where the Pivot Table will be created. We call it the Pivot Cache. It stores the entire source data in it.
Now I start building my Pivot Table by dragging the Date to the Rows Area. If you are using Excel 2016 or later, the Date will be automatically split into its components (Year/ Quarter/ Month). I will keep the Year and Quarter and Drag the Months out. Then, I drag the Sales and drop it into the Values area.
The Pivot Table emerges and to see the Quarters, we right click any year >> Select “Expand Collapse” >> and from the Submenu select >> “Expand Entire Field.
The first Pivot Table is Created without any issues.
Now let’s go back to the Source list and repeat the steps to create our second Pivot Table and trigger the problem. This Time we’ll select existing worksheet to create the second pivot Table few rows below the previous one.
Drag the fields as we did in the first Pivot Table but because this time, I want to show the Sum of Sales for each month, I Right Click the Date column in the Pivot Table and Select: “Group…”
In the Group dialog box, select Month only then hit OK.
Now, although I get what I want but, my first Pivot Table is messed up. Both of them are grouped by Month. If you try to regroup any of the in a different way, the other one follows automatically.
So, my questions are:
- Why did that happen?
- How can I group my Pivot Tables differently?
Why did that happen?
As I mentioned the Pivot Table Cache stores the entire source list in memory. So, when you create a second Pivot Table using the same source list, Excel attempts to save you some resources by using the same exact pivot Cache. So, actually the second and subsequent pivot tables are using the same exact grouping because they share the Pivot Cache.
You can easily find out how many pivot caches you have in the workbook by switching to the Visual Basic Editor (ALT + F11) and displaying the Immediate Window (CTRL + G).
In the Immediate Window, write:
?ActiveWorkBook.PivotCahes.Count
then hit Enter
You can now confirm that both Pivot Tables are using One Single Pivot Cache. That explains the problem.
How can I group my Pivot Tables differently?
To fix the problem we’ll create a New Pivot Cache for the Second Pivot Table (Keeping the current Pivot Cache for the first one).
How to create a new Pivot Cache?
First Method: Change Source Data
Select any cell in the second pivot Table
On the Analyze Tab click on “Change Source Data”
In the Change PivotTable Source Data dialog box, manually add one extra row by changing the last row from 351 to 352.That will automatically force excel to create a second separate pivot Cache.
If you check the Immediate window (as we did before) you’ll see that now the count is 2.
You need to group the date for the second Pivot Table >> Right Click the Date >> Group… >> Month
Now I have the data the way I want. But, because we added an empty row in the source, we need to apply a filter and get rid of this row by unchecking “Blank”
Second Method: Use Old Pivot Table Wizard
After creating the first Pivot Table with the grouping we want. We’ll go to the source list and create a second Pivot Table using the Old Pivot Table Wizard. This one does not exist on the Ribbon. It can only be triggered using the Shortcut; ALT, D, P (consecutive not simultaneous key strokes).
This wizard looks totally different, and consists of 3 steps. Accept the default for steps 1 and 2 then a message box will pop up asking you if you want to save memory >> Select NO to create a new Pivot Cache.
In Step # 3 select the destination (few rows below the first Pivot Table) then hit Finish. Now the new Pivot Table is Totally independent and you can drag the fields and group the way you like without affecting the first Pivot Table.
Third Method: Using Power Pivot Data Model
For this method, I’ll open a fresh version of the same exercise file, having only the source data (without any Pivot Tables).
Convert the Source List into a Table; Select any cell in the list and use the shortcut CTRL + T followed by hitting Enter.
On the Table Design Tab, to the left side, name the Table MySource >> Enter
Now we need to add the PowerPivot Tab to the Ribbon:
File >> Options >> Add-ins >> From the Drop list in the lower left corner select >> COM Add-ins >> Click GO >> Check the box for Power Pivot >> OK.
On the Power Pivot Tab Click >> Add to Data Model >> The Power Pivot window opens on top of Excel
The data in the Data Model (Power Pivot) is made available to use in Excel.
On the Home Tab click on the down arrow for Pivot Table and select>> Pivot Table >> New Worksheet.
Back to Excel,
- Expand the Table in the Pivot Table Fields List, by clicking on the small triangle to the left side of the table name “MySource”.
- Drag the Date to the Rows >> Right Click and Group by Year & Quarter
- Expand the Entire Field.
- Drag the Sales to the Values
- First Pivot Table is now Created.
- Click on Power Pivot Tab then click on Manage. You are back to the Power Pivot Window
- Repeat the steps to create the second Pivot Table in the Existing Worksheet >> Select the destination few rows below the previous table.
- Drag the Fields but this time we’ll group by Month.
The second Pivot Table is created and it is totally independent from the first one.
Congratulations!!
You learned 3 methods to solve the annoying Pivot Table Cache problem. Select whichever technique you like. However, I have a preference to the last technique.
You can watch the Video Tutorial here below
and watch many more on my YouTube channel : YouTube.com/OfficeInstructor
