8

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data.

Since I have to use multiple pivot tables, all with nearly the same data input but with slight differences from each other, I was thinking to create just one of them, then copy & paste it and subsequently modify only the data set that I need to change.

But when I try to do that (by selecting and copying the table cells), the paste operation gives me just the content of the source table, not the structure; so I will have a series of cells filled with the formatting and values from the source one, but that is not a pivot table itself, thus it won't update and basically is of no use.

I hope it is all clear. How can I do it?

5 Answers 5

3

A copy will update if created with Paste Link.

Edit But as @Sekhemty points out, such a copy does not provide all the (in this case required) facilities of a pivot table. So:

  1. If literally using Copy, copy the entire sheet.
    or
  2. Right click the existing pivot table, select Edit Layout and click More. In Result to select another location, same sheet or not.
2
  • 1
    But I need a completely new table, not a link, because I need to modify it with different data sets; I just want to use an existent one as a template, so to speak.
    – Sekhemty
    May 23, 2013 at 17:11
  • Or combine both steps to get the copied table back on the same sheet (copy the entire sheet and then move the copies back to the original sheet).
    – kad81
    Jul 29, 2014 at 3:41
6
  1. Select the Pivot Table
  2. On the Analyze Ribbon - Click on Select-> Entire Pivot Table
  3. Right Click Copy in the pivot table area
  4. Go to the location where you want to place the copy of the pivot table and right click and select Paste Special ->Keep Source Column Widths (W)

This should give you what you need. Hope this helps

3
  1. From Pivot Table 1, select any cell, then click CTRL-Shift-* (selects entire pivot table)
  2. Click CTRL-C (copy)
  3. Click mouse into new cell on same or different worksheet - Press ENTER

This will create a copy of Pivot Table 1 as Pivot Table 2 that you can than Change Data Source but otherwise all of your fields and formatting are preserved.

1
  • This works great for pivot tables with one layer, but if you have multiple sub-rows in the original pivot table then those don't get copied.
    – Shawn
    Sep 8, 2016 at 23:10
0

Right-click the worksheet tab at the bottom of the window, select Move or Copy, specify where you want to place the new worksheet, and make sure that "Create a copy" is checked. This will work to duplicate the PivotTable with the current layout; note that the tables will have the same name, however (if you interact with them via scripting, etc.). It also works best if you have only one PivotTable per worksheet.

Note: I see you tagged your post for LibreOffice. My directions are for Excel though they may be applicable to the former as well.

1
  • Altough working, this solution duplicate an entire worksheet, not just a single pivot table. I have a layout with more tables per worksheet, for ease of view and printing purposes
    – Sekhemty
    May 23, 2013 at 21:51
0

I just "upgraded" to the new version of Excel and am so frustrated because I can no longer copy a pivot table in its entirely (formatting) to a new spreadsheet. As you indicated above, it does not copy the layers. I discovered a workaround, if you copy and paste into Word first, then copy and paste the table from Word into the new excel document, you retain the pivot table in its entirety. Hope this helps.

2
  • 1
    Thank you for the tip, anyway my question was not about MS Excel but LibreOffice Calc.
    – Sekhemty
    Feb 15, 2017 at 1:42
  • Please read the question again carefully. Your answer does not answer the original question.
    – DavidPostill
    Feb 20, 2017 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.