J-Walk & Associates, Inc.

Posted: Unlinking A Pivot Table

I posted a new tip: Unlinking a Pivot Table From Its Source Data.

It's a way for Excel 2007/2010 users to convert a pivot table into a normal range, and retain the original pivot table style formatting. It's not exactly a life-changing tip, but it could be useful. And it uses the otherwise useless Office Clipboard.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Jon Peltier. Comment posted 29 November, 2009 12:56pm

   In 2003, if I paste special values on top of an existing pivot table, the pivot table is converted to a regular range and the formatting is not hosed.
   
   I don't need to Office Clipboard to make a copy of a pivot table as a formatted unpivot range. Copy the pivot table, select the top left cell, Paste-Special-Values followed immediately by Paste-Special-Formats. Does 2007 clear the clipboard after a paste special?
2. By John Walkenbach. Comment posted 29 November, 2009 1:16pm

   You're right, Jon. What gets hosed is the pivot table style formatting.
   
   I didn't even bother checking Excel 2003 (which doesn't have pivot table styles). So that tip is useful only for Excel 2007 and 2010. I also edited this blog post to clarify.
3. By sam. Comment posted 30 November, 2009 3:37am

   But What about column widths...
   
   I have a button on my Custom Toolbar - Paste Value_Format_Col_Width - attached to the macro below...
   
   Sub Paste_Value_Format_ColWidt()
   On Error GoTo Errmsg
   Selection.PasteSpecial Paste:=xlPasteValues
   Selection.PasteSpecial Paste:=xlPasteFormats
   Selection.PasteSpecial Paste:=xlPasteColumnWidths
   
   Exit Sub
   Errmsg:
   MsgBox "Nothing to paste"
   End Sub
4. By John Walkenbach. Comment posted 01 December, 2009 7:43pm

   To adjust the column widths, just drag across the column headers and then double-click one of the column borders. Takes about two seconds. Three if you're slow.
5. By John A. Comment posted 09 February, 2010 10:04am

   I've tried pasting special formats as described on a number of spreadsheets, and it never works for me. Is there some other setting I need to check within options?