How do I “separate” Pivot Tables

During the creation of Office 2007, 2010 and 2013, a great emphasis was given to the file size. Of course you would like to make the file size as small as possible and for this purpose even the creation or better yet the behavior of PivotTables has changed since Excel 2003. The PivotTable has always created something called Data Cache based on the data provided. The need for indexing and fast creation of analysis has forced it to work in such a manner. In Excel 2003 each PivotTable had its own Data Cache but now the PivotTable that is created using the same Data Model or Data Source as another PivotTable also borrows that Pivot Tables Data Cache. Therefore, for each new PivotTable analysis that uses the “same” data, Excel saves hard dish space since it does not create its own Data Cache but rather uses the same one as previous PivotTables created on the same Data Model.

While this solution is obviously a great way to save space on the computer, this method also has two quite severe consequences for your PivotTables.

Refreshing one individual PivotTable consequently refreshes all PivotTables that are based on the same data, which can be a great thing but you can easily imagine some cases where this would not be such a good thing.

The grouping of records within a single field (for example, a Date field that you combine by months or quarters) now cannot be done on an individual PivotTable but immediately effects all the other PivotTables that are related to the same Data Model. If you used this field in another PivotTable, it reflects this grouping instantly. So you’ve lost the ability to group for example a Date field by months in one PivotTable and by Quarters in another.

There are even more ways how sharing a Data Cache effects our analysis but both listed above are reason enough for the need of a “separation” to arise. In fact, In this article we will discuss three different ways how to do this. First way is linked to the creation of the new PivotTable report and tells us how to create a PivotTable in such a way that it already has its own Data Cache and does not share one with the existing PivotTables. Afterwards I will give you two methods on how to “separate” PivotTables that have already been created…

Method 1 (creating a separate PivotTable report)

If you want to create a new PivotTable so that its Data Cache is separate from the other PivotTables you might have, then you must create it in a particular way, or better yet with a special command called PivotTable and PivotChart Wizard. This command has been part of Excel for what seems an eternity, however since by default it is not on any of the ribbons, you have to add it to the Quick Access Toolbar. To do this, you go to File (file)/Options (options), and then Quick Access Toolbar. Above choose Commands Not in the Ribbon

On the left side, find the PivotTable and PivotChart Wizard and with the Add button add the commands to the Quick Access Toolbar.

Afterwards we click in our data and run the command from the Quick Access Toolbar. This will sent us on a very familiar path

The first step is more or less self-explanatory, but the second one is very important, since it’s the step where we mark the area with the data for the the analysis.

As soon as you say Next, get the following (important!) notice

If you select Yes, then the PivotTable will be calculated on the same Data Cache as preexisting PivotTables and it will suffer from all the symptoms described above. If you select No, then you will create a new Data Cache for this PivotTable and therefor it will be separate from the preexisting PivotTables!

Method 2 (manual creation of a separate Data Cache for preexisting PivotTables)

The method is quite simple. Select the PivotTable that you would like to “branch off” and cut it from the workbook and paste it into a new one. Then you only have to copy it back. Sometimes this is enough, but sometimes you have to close the first workbook and save the new workbook, and then open it again and copy the PivotTable back into it.

Method 3 (“separation” of already created PivotTables with the help of VBA code)

The following VBA code does the trick for all the PivotTables in your Workbook.

Sub DataCache()

Dim PivTbl As PivotTable
Dim ws As Worksheet
Dim wsTemp As Worksheet
Dim pt As PivotTable

For Each ws In ActiveWorkbook.Worksheets

For Each PivTbl In ws.PivotTables

c = PivTbl.RowRange.Column
r = PivTbl.RowRange.Row
Cells(r, c).Select
Set wsTemp = Worksheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivTbl.SourceData).CreatePivotTable TableDestination:=wsTemp.Range(“A3”), TableName:=”PivotTableTemp”
PivTbl.CacheIndex = wsTemp.PivotTables(1).CacheIndex
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Set PivTbl = Nothing
Next PivTbl

Next ws

End Sub


Getting rid of old Row and Column Labels from the Pivot Table

Pivot tables still remains the go to solution in Excel for Data Analysis. This article will address the following issue with the Pivot Table command that enables you to still see the leftover entries that no longer exist in the data table. We will also talk about how to remove them…

Here is an Example of what I’m talking about. We have the following data table in Excel.

Notice that in the Month column, there are Four Months present, December, January, February and March. Now based on this data, we create a Pivot table where we calculate the Average number per Month.

Nothing out of the ordinary there. Now let’s change the data a bit. What we will do is delete the December data, as we no longer need it, and add the data for April. So now the data table we are working on looks like this

Now after refreshing the Pivot Table, we get something like this

Now that seems perfectly fine, but let us see what we get in the dropdown menu at Row Labels or Month…

And there it is, although our data no longer has any Rows of Data belonging to December, the December is still part of the Month field as you can see. The record was not deleted from the data model. Now this has some great uses as with the GetPivotData function etc. But for this article we will discuss how to get rid of the discarded data in two ways. Manually and using a VBA code or a Macro.

Getting rid of old Row and Column Labels from the Pivot Table manually

You place yourself in the PivotTable and either Right Click and select PivotTable Options or go to the Analyze (Excel 2013) or Options (Excel 2007 and 2010) Tab. In the PivotTable Options dialog box you place yourself on the Data tab.

The command we are looking for is Number of items to retain per field. The value is set to Automatic by Default and that means that Excel will decide how many items to retain. What we need to do is to change that to None and then Refresh the PivotTable.

After doing so, you can clearly see that December has disappeared from the Month field.

Getting rid of old Row and Column Labels from the PivotTable by VBA

If you have more Pivot Tables and you want to do this faster than the option above, you can use the following VBA code…

Sub DeleteOldPivotData()

Dim PivTbl As PivotTable

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

For Each PivTbl In ws.PivotTables

PivTbl.PivotCache.MissingItemsLimit = xlMissingItemsNone


Next PivTbl

Next ws

End Sub