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 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

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 instructions on PivotTable Styles and pivot table formatting see the Contextures Pivot Table Format page.

Watch the Pivot Table Format Copy Video

To see the steps for pasting the pivot table formatting and values, watch this short Excel tutorial video.

_____________

You may also like...

23 Responses

  1. Andrea says:

    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. Andrea, are you using Excel 2003? The formatting disappears when you Paste as Values in later versions.

  3. Andrea says:

    Indeed I do Debra. Thanks for pointing that out.

  4. Andrea, you’re lucky! They messed up this feature in Excel 2007, so it’s much harder to do now.

  5. John Franco says:

    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

  6. 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.

  7. man says:

    Extremely unfortunate. Could easily be patched but why do that when they can just leave it messed up?

  8. Yard says:

    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?

  9. Yard, I’ve posted some code that will copy the pivot table values and formatting to a new worksheet:

    Excel VBA Pivot Table Paste Format and Values

    • Siu Chen says:

      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?

  10. worker says:

    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.

  11. sajjad Haider says:

    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. :)

  12. Will says:

    Your site is amazing!

    I would like to use the code you posted here on multiple pivot tables in the same sheet. Any ideas?

    http://www.contextures.com/excel-vba-pivot-table-paste-format.html

    Thanks!

  13. BitofLuck says:

    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..

  14. Paul says:

    Hmm, just tried pasting from the Clipboard (Excel 2010), but didn’t get the formatting… any ideas?

  15. Susan says:

    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.

  16. S K Srivastava says:

    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.

  17. Jl_14 says:

    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!

  18. Ashok says:

    Cool..
    made my life easier!

  19. Trish says:

    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.

  20. Roman says:

    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?

  21. C Rembert says:

    Thank you! Thank you! Thank you!

  1. August 14, 2013

    […] Dalgleish says this is a limitation with post Excel 2007. Copy Pivot Table Format and Values | Contextures Blog The Contextured site includes some code for getting the job done however (I haven't tried it yet). […]

Leave a Reply to Roman Cancel reply

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