Unlinking A Pivot Table From Its Source Data

 Jul 27, 2015

You may have a situation in which you need to send someone a pivot table summary report, but you don't want to include the original data. In other words, you want to “unlink” the pivot table from its data source in Excel 2010.

Here's a nicely formatted pivot table in Excel:


Excel doesn't have a command to unlink a pivot table, but it does have a flexible Paste Special command. Using that command, with the Value option, should do the job:

  1. Select the pivot table cells and press Ctrl+C to copy the range.
  2. Display the Paste Special dialog box. Pressing Alt+ES is my favorite method, and it works for all versions.
  3. In the Paste Special dialog box, choose the Values option, and click OK.

The pivot table is unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone:


To get the formatting back, you need to perform two additional steps:

  1. Display the Office Clipboard. In Excel 2007 and 2010, click the dialog box launcher icon in the bottom right corner of the Home – Clipboard group.
  2. With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.

Now the pivot table is unlinked from its data source, yet retains all of its original formatting.

By the way, this is actually the first time I've ever done something useful with the Office Clipboard. It's actually fairly useless in Excel because it doesn't hold formulas — just the values returned by formulas.

How do your Excel skills stack up?   

Test Now  

About the Author:

Alice Antonsen  

Alice is one of New Horizons’ most experienced Desktop Applications trainers. She is qualified with a Diploma of Business (Computing) and a Diploma of Information Technology. Since joining New Horizons in 2006, Alice has achieved the prestigious status of a certified Microsoft Office Master and for the past 4 years, has been placed in the Top 25 Desktop Applications trainers for New Horizons Worldwide. Throughout her career, she has gained a wealth of knowledge and experience in providing training to individuals and groups of varying skill levels. Alice delivers each training session with great care and consideration ensuring each one is tailored to the learning needs of her students.

Read full bio