Unlinking A Pivot Table From Its Source Data
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, you 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.
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks