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 we will 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.

For example, if we use AdventureWorks Cube for this illustration and this is my existing PivotTable pointing to test server:

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.

IMPORTANT: 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.

Just copy PivotTable as final report (no data linked)

You may have a situation in which you need to send someone a pivot table summary report, but you don't want to include the original data. In other words, you want to "unlink" the pivot table from its data source. Here's a nicely formatted pivot table in Excel 2010:

Excel doesn't have a command to unlink a pivot table, but it does have a flexible Paste Special command. Using that command, with the Value option, should do the job:

• Select the pivot table cells and press Ctrl+C to copy the range.
• Display the Paste Special dialog box. Pressing Alt+ES is my favorite method, and it works for all versions.
• In the Paste Special dialog box, choose the Values option, and click OK.

The pivot table is unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone:

To get the formatting back, we need to perform two additional steps:

• Display the Office Clipboard. In Excel 2007 and 2010, click the dialog box launcher icon in the bottom right corner of the Home - Clipboard group.

• With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.
• Now the pivot table is unlinked from its data source, yet retains all of its original formatting.