Change a Pivot Table back to a spreadsheet

Every month I have to update numerous pivot tables which I then copy and paste into a new document to email to various people.

Is there anyway I can "unlink" a Pivot table from its data, thus turning it back into an ordinary spreadsheet for emailing.

Thanks.

Answer
Answer

The following information was obtained from the following site:

http://spreadsheetpage.com/index.php/tip/C26/

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.

 

 

44 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Instead of copy and paste,
do copy and paste special as values (then do another paste special as "Formats" if desired)
This effectively kills the pivots in your new books for your emailing purposes 

20 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated January 18, 2021 Views 87,025 Applies to: