When you copy and paste a pivot table, a hidden data cache still remains. This video shows a simple way to copy a pivot table without the source data, while preserving all formatting.
In this video, we'll look at how to copy and paste a pivot table without the underlying data.
When you create a pivot table with source data in a worksheet, Excel quietly creates a hidden data cache that travels along with the pivot table.
If you copy and paste the pivot table into a new worksheet, remember the data will come along with the pivot table in the hidden cache.
Let me show you how this works. If I copy this pivot table, then paste into a new workbook. I can still use the pivot table, thanks to the cache. And, if I double click a grand summary, Excel will extract the data from the cache into a new sheet, using the drill-down feature.
Not only does the hidden data increase the size of the worksheet, you also may not want to share the raw data, if it contains sensitive or private information.
So, how can do you copy a pivot table without the data?
Well, the simplest way is copy and paste using paste special.
First, select and copy the entire pivot table. You can use Control + A to select the whole table.
Next, in a new worksheet, use Paste Special, then Values. This will strip away all formatting and leave you with just the data.
As you can see, this doesn't look that great, but don't worry, we'll fix that next.
With the pivot table still on the clipboard, use paste special again. This time, choose Formats. This will bring in all the cell and number formatting.
Finally, if you like, you can use Paste Special one more time to bring in the original column widths.
Pasting column widths is one of those quirky features that makes paste special so useful. And once you know about it, you'll find it can be quite handy.
And that's it, if I hide the grid lines now, we now have a simple replica of the original pivot table without any data.
Remember that when you use this approach to copy a pivot table, you'll loose the interactivity of the original pivot table. However, you'll gain a very small file size, and any sensitive information in the original source data is completely gone.
Each pivot table has a pivot cache, which is a true copy of the source data. In today's video tip, we look at how the pivot cache works, and how it allows you to remove the source data from your workbook and still have a functional pivot table...
First just wanted to say what a fantastic website and resource you have created. I only stumbled on it a few months ago but recommend it to anyone I come across with an interest in Excel (and who wouldn't love Excel!) -DH