Pivot Tables are undoubtedly one of the best features of Microsoft Excel, sharing them however can become quite tricky once your file size becomes larger so it is not always possible to send the file without either zipping it, sending via a transfer program.
You may not want to always share your source data as maybe confidential. To share the summary data you will need to somehow unlink your Pivot from its data source. Unfortunately Excel does not have an unlink feature, but it is possible to achieve the same result with a few more extra steps.
So, if you have a Pivot that you have formatted and are happy with both presentation and level of analysis, we can use the really flexible Paste Special command along with the Value command.
1. Firstly select the pivot table cells and hit CTRL+C to copy the range or you can use the Ribbon command by selecting any cell in your Table and then
- On the Pivot Table Tab- selection Options
- In the Actions group – Click Select
- Choose the Entire Pivot
If this option is not available you will need to turn on the Enable Selection Option.
- Select a cell in the Table
- On the Pivot Table Tab- selection Options
- In the Actions group -Click Select
- Check if Enable is ON or Off ensure it is ON
2. Once the table is selected then display the Paste Special dialog box. Pressing Alt+ES is a shortcut, and it works for all versions of Excel
3. In the Paste Special dialog box, choose the Values option, and click OK.
The pivot table is now unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone and we need to get this back- which we can with two more additional steps.
4. 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
5. 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 after the last action you performed.
Now the pivot table is unlinked from its data source, yet retains all of its original formatting. You can now quite easily share the summary data that has the look and feel of the original data with none of the associated problems with data sources and file size.