Excel Unplugged

How do I “separate” Pivot Tables

How to separate pivot tables in Excel? In this article we will discuss three different ways how to do this.

Why does Excel do this

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. For this purpose the creation or better yet the behavior of Pivot Tables has changed since Excel 2003. The Pivot Table 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 Pivot Table had its own Data Cache, but now the Pivot Table that is created using the same Data Model or Data Source as another previously created Pivot Table also borrows that Pivot Tables Data Cache. Therefore, for each new PivotTable analysis that uses the “same” data, Excel saves hard disk space. It does not create its own Data Cache but rather uses the same one as previous Pivot Tables created on the basis of the same Data Model.

The problems it creates

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

1.
Refreshing one individual Pivot Table consequently refreshes all Pivot Tables that are based on the same data, which can be a great thing but you can easily think of some cases where this would not be such a good thing.

2.
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.

How to change it

Both points 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. It 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 Pivot Table 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. Better yet, create it 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/Options, and then Quick Access Toolbar. Above choose Commands Not in the Ribbon

How do I separate Pivot Tables

On the left side, find the Pivot Table and Pivot Chart 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

How do I separate Pivot Tables

The second one is very important, since it’s the step where we mark the area with the data for the the analysis.

How do I separate Pivot Tables

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

How do I separate Pivot Tables

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

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 the Pivot Table back to its original place. Sometimes this is enough. But sometimes, you have to close the first workbook and save the new workbook, close it, and then open both workbooks again and copy the PivotTable back into the original spot.

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
ws.Activate
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
wsTemp.Delete
Application.DisplayAlerts = True
Set PivTbl = Nothing
Next PivTbl

Next ws

End Sub

 

And eternal happiness is even closer…

Learn more

Check out our YouTube channel and subscribe for more amazing Excel tricks!

Follow us on LinkedIn.

Check out our brand new R Academy!

Comments 16

  1. María says:

    the macro has a End If without if, but thanks

    1. Nice catch,thank you!
      Fixed it 🙂

  2. Nikhitha says:

    i have 83 pivot tables in my workbook. i don’t know whether the reason is dat. if so, wat can i do? the program terminates with a message that “Excel cannot complete this task with available resources”. and run-time error 1004 is showing. can u help me?

    1. You could try closing every other window and trying again, but the real solution is using a 64 bit excel that is not limited by RAM consumption.

      1. Nikhitha says:

        Thank u :}

  3. Martin says:

    Could You pls add VBA code for Method 3? Thanks

    1. Hi Martin,
      Sorry for that, had an issue with a WP Plugin… The Visual Basic code is visible now. Thanks for the heads up.

  4. Russell Smith says:

    Late to the game but I would like to say finding this routine to separate Pivot Caches saved me hours of time. I would make a few additions though. First, I would use “Option Explicit” at the top of the routine. Second, I would dimension the variables r and c as Long. Third, I would add some error handling to the routine. This is because on my instance of Excel, if a Pivot Table does not have a field in the Rows group, it will cause an error when then routine tries to set variables r and c.

  5. Chris Geyer says:

    I was lucky enough to stumble into option 2 on my own, but in my scenario my data source needs refreshing. When refreshing the data sources for each pivot table – they go back to being linked to the same data cache…. Is there a way to make a pivot table that for eternity will keep its own data cache even if the source expands?

    1. Creating a Pivot Table with the Pivot Table And Pivot Chart Wizard option, will always keep pivots separated.

  6. Chris K says:

    Hi Gašper Kamenšek,

    I’m looking for a macro that does the opposite. I created a worksheet with dozens of pivot tables, but at the time of creation I didn’t know that to adjust them using slicers that I would have needed to click “Add this data to the Data Model”. Is there a way to add previously created pivot tables and charts to the existing data model?

    Thanks,
    Chris

    1. Sorry to disappoint Chris, but that is a no go 🙂

  7. Nanak says:

    HI Gasper,

    Thank you.
    I tried Method 3, as I have a template workbook with some customed Pivot Tables.
    As I run the macro, I stumbled upon the error message :
    ” Run time error 1004
    Unable to get the RowRange property of the PivotTable class”

    Any idea how to solve this?

    Thanks,
    Nanak

    1. It seems you don’t have any fields in the “Rows” section judging by the error msg…

  8. Dmitrii says:

    Great article, thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.