Unlinking A Pivot Table From Its Source Data

Category: General / Formatting | [Item URL]

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:

1. Select the pivot table cells and press Ctrl+C to copy the range.
2. Display the Paste Special dialog box. Pressing Alt+ES is my favorite method, and it works for all versions.
3. 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, you need to perform two additional steps:

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

By the way, this is actually the first time I've ever done something useful with the Office Clipboard. It's actually fairly useless in Excel because it doesn't hold formulas -- just the values returned by formulas.