A recent request from a client asked how to ‘copy pivot table without underlying data’, as they wanted to send the pivot table report to another person, but not allow them to drill down into the underlying data. They wanted the benefit of the pivot table formatting but to only show the values without any of the functionality or drill down options. Effectively they wanted to unlink the pivot table.
The recent versions of Excel don’t do this very well. If you copy and paste a pivot table, it keeps all the data, formatting and pivot table functionality. If you copy and paste over this as values, it loses its formatting making it difficult to read.
As per John Walkenbach’s trick, you can use an anomaly in the Office Clipboard.
When you normally copy an item, you probably paste it via the right click, CTRL + V or some of the Paste Special Options. In this case, rather use the Office Clipboard. So the steps are:
- Copy the pivot table
- Activate the Office Clipboard by clicking on the expand option on the Home Tab under Clipboard (see below)
- Now click on the cell where you want to paste the unlinked pivot table and choose the last item copied into the clipboard (which should appear on the left of your screen)
- You will see that the pivot table is pasted with the format but with no functionality, a true copy and paste special with numbers and format.