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 that 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 pivot 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 workbook, you also may not want to share the raw data, if it contains sensitive or private information. So, how can you copy a pivot table without the data? Well, the simplest way is to 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 the 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 of 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 lose 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
