Home // Blog & Excel Tips // Copy pivot table without underlying data

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Copy pivot table without underlying data

Comments Closed
Uncategorised

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)
copy-pivot-table-without-underlying-data
  • 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.