How to Clone an Excel PivotTable?

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)

Figure 1 - Analysis Services Cube PivotTable

To clone this PivotTable, navigate to PivotTable Tools Options→Select→Entire PivotTable (Figure 2).

Figure 2 - Select Entire PivotTable

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.

Figure 3 - Change Data Source

That’s it. The PivotTable clone is now ready for comparison analysis.

Benny Austin