Forum Discussion
pivot tables copy
Hi,
I am creating a large DB and I need to create a subsequent sheet that will demonstrate multiple data crossings and grouping simultaneously.
I create a new pivot table, I cross data 1 with data 2 etc. Works fine.
Then, I want another pivot table so I can show different groups of data 1 etc. but as soon as I am grouping datas in my second pivot table, the first one follow up and changes too!
I want my different pivot tables to be independant. How can I do that???
Thanks!
Hi Billy,
When you creating the pivot table if you select "Add this Data to the data model" which option is ON by default, your source data tables(s) are linked with data model, and pivot table works on data model table, not on source one in Excel sheet. Adding more pivot tables you connect them also to the same data model.
If you add grouping in one of pivot table you actually add one more column to the data model table which is visible for all pivot tables. You may use it, may not. The only you can't create let say Group1 in first pivot table with one fields and have Group1 with another fields for the second pivot table. Group1 is defined in data model and all pivot tables take it from the model.
You may use different naming to avoid the conflict. Or don't add your pivot tables to data model when you create them.
3 Replies
Hey Billy,
There is a workaround that you can do to Group 2 Pivot Tables independently without using the Data Model.
Here is a video on how this can be done which I created for my Xtreme Pivot Table Online Course here:
https://1drv.ms/f/s!Aryb9aCBx1lqgp9Hz3UNWe3ZGyaZKg
I hope this is what you were after.
Cheers,
John Michaloudis
https://www.myexcelonline.com
https://www.myexcelonline.com/blog/
https://www.myexcelonline.com/podcast/
Hi Billy,
When you creating the pivot table if you select "Add this Data to the data model" which option is ON by default, your source data tables(s) are linked with data model, and pivot table works on data model table, not on source one in Excel sheet. Adding more pivot tables you connect them also to the same data model.
If you add grouping in one of pivot table you actually add one more column to the data model table which is visible for all pivot tables. You may use it, may not. The only you can't create let say Group1 in first pivot table with one fields and have Group1 with another fields for the second pivot table. Group1 is defined in data model and all pivot tables take it from the model.
You may use different naming to avoid the conflict. Or don't add your pivot tables to data model when you create them.
Thanks Sergei!
