Copy Pivot Table Format And Values - Contextures Blog

Skip to content

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.

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.

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

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.

   Reply

2. Andrea, are you using Excel 2003? The formatting disappears when you Paste as Values in later versions.

   Reply

3. Indeed I do Debra. Thanks for pointing that out.

   Reply

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

   Reply

5. Hi Debra,

   I have always done that this way…

   1)Copy the Pivot Table2)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

   Reply

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.

   Reply

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

   Reply

8. 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 = FalseActiveSheet.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?

   Reply

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

   Reply

   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?

      Reply

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

    Reply

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

    Reply

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

    Reply
13. Pingback: VBA: Pasting a Pivot keeping all Formats??

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

    Reply

    1. No, it doesn’t work in Excel 2019.

       Reply

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

    Reply

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

    Reply

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

    Reply

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

    Reply

19. Cool.. made my life easier!

    Reply

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

    Reply

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

    Reply

22. Thank you! Thank you! Thank you!

    Reply

23. Excellent

    Reply

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

    Reply

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

    Reply

Leave a Reply Cancel reply

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

Comment *

Name *

Email *

Website

Δ

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

Post navigation

Previous Previous post: Split Prize Money for Tied RANK in ExcelNext Next post: Highlight Winning Lottery Numbers in Excel

© 2024 Contextures Inc.

About Contextures

Excel Tutorials

Policies

Blog RSS Feed

Search for: Search

• Contextures Excel Tips
• Debra's Blog
• Pivot Table Blog
• Spreadsheet Day Blog

As an Amazon Associate I earn from qualifying purchases