Exceljet

Quick, clean, and to the point

This video is part of Core Pivot, video training for Excel Pivot Tables

How to copy a pivot table without the data

Tags 
Summary 
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.
Video Transcript 

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.

Author 
Dave Bruns

Related shortcuts

This is the most easy to understand demo of VLOOKUP I've ever come across. Thanks! - Tawhid
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course