«

»

Oct
03
2013

Making Changes to Copied Pivot Table/Charts without Affecting the Source

When working with complex excel reports you might want to make copies of pivot tables/charts with slight variations within a same workbook. When you make copies pivot table/charts (straightforward copy/paste) and edit the grouping/fields of the copied table you would notice the original/source table also changes inline with the copied table/chart.

Some blogs suggest the following steps:

  • make a copy of your pivot table
  • cut and paste the copied table into a new excel workbook
  • again cut and paste the pivot table from the new workbook back into original workbook

This way making changes to the cloned pivot table/chart will not affect the original table/chart.

However i have noticed this approach significantly increases the file size of the excel workbook because the entire pivot table’s source data replicated and increases the workbook file size. If your data source is large (e.g. 100,000 rows or more), this approach would result in huge file size increase for every copied pivot table/chart.

 

But based on some of the suggestions from excel forums with some of my trial and errors i have found the following alternative approach to tackle this problem:

Step 1 – Make your Copy of Pivot Table

Step 2 – Uncheck the ‘Save source data with file’ and ‘Enable show details’ from pivot table options

 

image

 

Step 3 – Create  a new named range of your existing data source (Select your existing source data range and give it another name to create a new named range)

 

image

 

Step 4 – Perform a ‘Refresh All’ and Change the data source of your copied/cloned pivot table into new named range. For example, your original pivot table uses Named Range – Inventory1 then your copied pivot table’s data source can be changed to Inventory2 (even though both name ranges refers to the same excel cell range)

 

image

 

image

 

 

Now if you make any changes to the copied pivot table (e.g. grouping/ungrouping or editing fields) then your original pivot table will not be impacted. Since both pivot tables are linked to same data source (through different named ranges), changes to source data will update both pivot tables. Also you are not packing the data into pivot tables so the file size of workbook would not increase either.

 

Here is a video demonstration for this:

 

Did you find this post useful? Feel free to share your feedback in the comments area.

You can find more interesting posts from us on the posts index section

If you are interested to be notified for the new posts from us, then you can make use of the subscribe option available on the right sidebar.

Help us to improve our search rating by recommending this post to Google using the button below

 

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *