Copy Pivot Table Values and Formats

If you create a pivot table in Excel 2007 and later versions, formatting is automatically applied by the PivotTable Styles feature.

PivotStyles01

This feature makes it quick and easy to format your pivot tables, and helps you stay consistent with the Theme settings in your Excel file.

Problems with Format Copying

Occasionally, you might want to copy a pivot table as values, without the underlying data. Unfortunately, if you copy an Excel 2007 or later pivot table, and paste it with the Paste Special command, the PivotStyle formatting is not pasted.

In the screenshot below, the values from the pivot table were pasted into columns D:E, but pasting the formats had no effect.

pivotcopyformat

Pivot Table Format Paste Workarounds

Fortunately, there are workarounds for the problem of pasting the pivot table formatting:

  1. Use the Office Clipboard, to paste the Pivot Table formatting
  2. Copy and paste the pivot table in a couple of steps, as shown below
  3. Programmatically copy the pivot table in a couple of steps

pivotbodycopy02 

___________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.