Ever came across a situation where you have to compare PivotTable data from same data source but from different environments, for e.g. to compare data between Test and Production environment? In this post I’ll explain a nifty trick that helps to clone an existing Excel PivotTable and change its data source without having to create the clone PivotTable from scratch.
I’m using AdventureWorks Cube for this illustration and this is my existing PivotTable pointing to Test Server (Figure 1)
To clone this PivotTable, navigate to PivotTable Tools Options→Select→Entire PivotTable (Figure 2).
Click a cell in the same worksheet or create a new worksheet and hit ENTER. The PivotTable will now be copied to it’s new location. Take note this is the only way to copy PivotTable. Selecting the Rows, CTRL+C and CTRL+V copies the data as text and not as PivotTable.
With the PivotTable cloned, proceed to change the Data Source of the clone. Click on any area on the newly copied PivotTable and change the data source by navigating to PivotTable Tools→Options→Change Data Source→Change Data Source (Figure 3). Select the data source (in this case, AdventureWorks Cube) pointing to a different environment (in this case, Production). If the data source does not exist, create a new one.
That’s it. The PivotTable clone is now ready for comparison analysis.
Benny Austin 
Benny, thanks for your article but PLEASE ADD THE “CTRL-C” STEP as Bob Phillips mentions, so that your readers don’t have to figure it out for themselves. You might be assuming they understand to do that, but your sentence “Selecting the Rows, CTRL+C and CTRL+V copies the data as text and not as PivotTable” confuses things.
Thank you Bob :)!
I am following exactly how it shows in these instructions… just trying duplicate a pivot table in an existing spreadsheet. I do everything as instructed, however, when I hit enter it does nothing. Why would this be happening?
Hi Benny,
I have a PivotTable formatted exactly as I’d like, and would now like to copy it and have it pointing to a new PowerPivot table listed in the PivotTable Field List box. The new table has the exact same fields as the original, only the SQL query criteria has changed. My frustration is that I’m finding no way to change the data source to the new table.
Any assistance provided here will be most appreciated!
David, unfortunately I don’t think that’s possible. Each cell has the metadata of the data source attached to it. So replacing a data source with another will not work even though the fields have same name.
Thanks for the reply Benny. That’s pretty much what I thought, but remained optimistic that just maybe somewhere in the background there’d be a way of editing the PivotTable’s table name metadata.
Hi Benny I want to copy a dynamic pivot table( where the data is stored in a diiferent workbook) to a new work book , how to I do that , where the copied pivot table updates when the original pivot changes
rosh, you could use PowerPivot with your spreadsheet as data source
Hi benny I have 2010 excel
rosh, PowerPivot add-in is available as free download and works with 2010 as well.
I have another Questions for u Benny , if I Have different pivot tables from different sources , can I have one slicers to control all the pivot tables
Is there anyway to create a copy of the pivot table in such a way that you can still add new columns and refer to certain cells, but when the original pivot table changes (for example the grand total value changes), the copy will also change?
The cloned pivot table is detached from the original pivot table and is not linked to it. However if both the pivot tables are from the same source then refreshing the spreadsheets should give you same totals on same value columns.
I forgot to mention that the data source is external and I used to be able to change the monthly dates in the query with no problem in 2003, can’t figure it out with 2010.
Any help?
Jim, if you changed the query then all pivot tables pointing to that connection will change. You might have to create two different connections if you want only the new pivot table to display different data.
Thanks Benny.
I’m still having problems with this. When I paste the new PT as you described, I cannot change the source data for the PT. When I do, BOTH tables change to the new source, not just the one that I want to change.
Any ideas?
Jim
Hi Benny,
I use the same trick usually, but as a matter of fact there’s a limitation I just found and have still not been able to avoid :
When you create a PT from and Excel Sheet (I mean data you entered or copied-pasted in a basic sheet, then select them and insert a PT) you cannot change afterward for an external datasource, neither with the wizard (option is disable) nor via a VBA script (raises an error)…
Quite annoying when you create a prototype with manually entered data before bothering writing the query.
I’ll post here if I finally find a way around…
Sylvain
Nice technique Benny.
Just one thing, you omitted to say that the PT should be copied, Ctrl-C, after the entiretable is selected.
Yup. This still needs to be corrected in your article, Benny. Please add “Ctrl+C” before pressing ENTER.