Duplicating Pivot Tables



I have worked with Pivot Tables for several years; but am only recently using
them in Excel 2007.

In previous versions, once I had created a Pivot Table, I could select it,
copy it, then paste it to another location and modify it. This only works
with some success in Excel 2007.

It appears that all of the Pivot Tables I have created from the initial
Pivot Table remain linked together, and changing aspects of one of them will
cause them all to change.

For example, I am using the "grouping" feature on the data in the Row
Labels. It contains percentages from -100% to +100%. I am grouping in 10%
bands (e.g. from -1 to 1 by .1). I then copy the table, paste it in another
location (same worksheet or different, but still the same workbook). I need
to group everything between -10% and +10% by 1% bands (e.g. from -0.1 to 0.1
by 0.01).

However, doing this latter grouping also changes the grouping on the
original pivot table.

Similarly, if I clear the data, or change the data range on any of the Pivot
Tables, they all change.

How can I "unlink" copies of Pivot Tables so I can modify them each


Roger Govier


Create a second Table with the same range as your first source range, using
Insert tab>Table.
Note the name given to the table, e.g. Table 2, or give it a new name
yourself. Table Tools>Design>Table Name.
Place cursor in any cell of second Pivot Table>Pivot Table
Tools>Options>Data>Change Source>set it to your new table.

Now, any changes made will be independent of each other, even though it is
the same set of data that is being viewed.

Herbert Seidenberg

Excel 2007
If you want to make multiple, common source, PTs independent,
and you don't want to duplicate your source data,
convert the source (say Table1) to a range
and give the former table range, including headers,
multiple defined names, such as ArrayA, ArrayB, etc.
Assign these names to the different PTs as data sources.
This will unlink the PTs for grouping purposes.
Recreate Table1. This will restore its dynamic feature.
If ArrayA was assigned a range other than the exact range of Table1,
it will stay the same size, otherwise it will dynamically change
with the size of Table1.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question