Copy Pivot Table Format and Values

Copy Pivot Table Format and Values

To keep your data details confidential, you might want to send someone a copy of a pivot table, without the link back to its source data. It’s easy to copy a pivot table, and paste it as values,but it is difficult to copy pivot table format and values.

No Formatting

After you copy a pivot table, if you try to paste the values and source formatting, you’ll be disappointed by the results. The values are pasted, but not the PivotTable Style formatting.

pivotformatcopy02

Use the Clipboard

Fortunately, John Walkenbach discovered that you can paste from the Office Clipboard, instead of using the Paste Values command, and the PivotTable Style formatting is pasted too.

pivotformatcopy04

The result is something that looks like the original pivot table, without the link to the source data.

pivotformatcopy05

For more information on PivotTable Styles and pivot table formatting see the Contextures Pivot Table Format page.

Copy Pivot Table Format and Values Video

To see the steps to copy pivot table format and values, watch this short Excel tutorial video.

_____________

27 thoughts on “Copy Pivot Table Format and Values”

  1. You can get the same result by Paste All, and immediately thereafter Paste as Values. By pasting the first time, you get a copy of the pivot table – and by pasting the second time (Values only) – the pivot link to the source data is removed.

  2. Hi Debra,

    I have always done that this way…

    1)Copy the Pivot Table
    2)Paste values (using Paste Special)
    3)Then get back to Paste Special and choose Paste format

    The result is the same

    I hope it helps

  3. Thanks John, but that doesn’t work in Excel 2007 or later, to copy the PivotTable Styles.

    In earlier versions, the PivotTable Styles don’t exist, and you can copy the AutoFormat formatting, as you described.

  4. That’s a great tip and I’ve used it a few times.

    However, doing it with VBA doesn’t seem to work. If you record yourself doing it, you get:


    Application.CutCopyMode = False
    ActiveSheet.Paste

    but when you try to run that code again, it goes bang on the .Paste line (I guess because it sees the Excel clipboard as empty, following the first line??).

    This remains a royal PITB when automating report publishing from Pivot Tables.

    Anyone have any ideas?

    1. Hi Debra,
      Thank you so much but i have multiple worksheets within the workbook can you help me create a code which will paste all sheets instead of me doing it one by one?

  5. Copy Pivot Table into Word – will result in a table in Word with same formatted look as Excel Pivot Table
    Then select Word table, copy and paste in new Excel worksheet – result – values only with formatting.

  6. I have a question – what about pasting pivot table in Word with same formating -sign with subcategories — like I have a list of departments who engaged 7 vendors – and there are 18 dept with each having different vendors engaged as found by Pivot table . how I can paste the source formatting into the target (Word document Table with
    -Department
    vendor1
    vendor2
    vendor3 that my boss exactly knows that a every department have some vendors in front of it in word. 🙂

  7. Hi, Just add another field to the end of the pivot table and select the entire pivot table, except the last column (just added), click copy, and just click paste into another excel spreadsheet. It copies the numbers and format without any links to the old spreadsheet.
    So in essence, you’re not copying the entire table. Just stumbled on this solution by luck now.
    Hope it works for you..

  8. Easiest way for me, was to add a field into the filter (whether you use it or not) and copy the table without your filter. It will paste the values and the formatting.

  9. What Susan is telling is correct. That method works. In fact it works even if you just leave the top row at the time of copying. I have been doing it for quite some time.

  10. Hi,
    Does anyone know how you put back in the collapsible buttons in the cells after you paste the pivot table? I’m working in 2010 version and my pivot table has pasted over nicely but I wanted to incorporate the buttons into the cells so that the person I am sending it to can minimise the data as and when they want.
    Thanks!

  11. In Excel 2010, I have found a method to just paste the data and format without the link back to the pivot data source.
    If you try to copy the whole pivot table, it doesn’t seem to work and the link to the data source is copied as well as the formatting.
    To only paste values and formatting, put your cursor on the first cell of the “Row Labels” row of the pivot table. Click on that cell and then scroll down to your grand total figure and Shift+click that cell to define your data to be copied. The area you want to copy should now be highlighted. Invoke the copy feature or use Ctrl+C. Move to the new worksheet or area where you want to paste the data and click on the cell where you want it pasted. “Paste” or Ctrl+V (regular paste icon)to your new worksheet or area. The table data and the formatting are pasted without the link to the data source.

  12. When I am copying and pasting the existing excel table into the words doc, the table gest distorted with the last column missing.
    Is there is a function to insert existing excel table into the words document?

  13. This worked for me a few time, but now has stopped working.

    I am assuming it has something to do with setting. Can anyone tell me what might be the issue?

  14. what i found is if i just copy the data and disregard the top 3 rows (headings) that it will copy and i can paste the actual data and then i can go back and copy the 3 rows of heading and paste them – that works

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

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