Forum Discussion
Why are My Pivot Table Settings Linked?
Hi there. So I am creating a report and have a group of pivot tables that rely on custom date grouping to look at week-over-week changes.
When I create a new pivot table with the same data source - and to clarify, I am making the new tables from scratch by navigating to the raw data and inserting a new pivot table into a new sheet - the setting appear to be linked to all the prior tables that use the same data source (these settings include the date grouping and calculated fields).
So the issue is when I ungroup the date in the new table, which is necessary to see the trends over time, all of my prior tables also adjust and become unusable.
I realize a workaround would be to manually copy the data source so there are two versions of it held in two separate sheets, but this report will be quite heavy and that would involve unnecessary additional labor
Any idea how to unlink the settings across pivot tables that use the same data source? Thanks!
10 Replies
IMHO, the easiest way to avoid such effect is to add data to data model creating PivotTable. Even if you don't use data model directly.
Hi SergeiBaklan - interesting, I will look into that as I have never used data models in Excel before!
At a quick glance, I am wondering if the file location would be an issue. It looks to me like data models rely on external data sources, and due to how this report will be shared, accessed and manipulated, it is vital that the data is locally stored within the file.
Would that create an issue for data models or would you recommend I continue looking into this option and learn data models?
Thanks!
Oops, I see you are on Mac. Not sure how and if all mentioned here functionality (data model, PowerPivot Wizard) is supported for Excel on Mac.
Hi
Excels is trying to be nice to you and save you some Cash memory by using the same source list.
If you have a Date column and then you group Say in Years and Months, then you tr to create a second one with a different grouping >> by Right Click and Group) say Years and Quarters this time, then your first Pivot Table Changes as well... That's the Problem.
Now, Here is the Solution:
After creating your First Pivot Table with the Grouping option you like,
You need to refresh the Source Data stored in Cash>> The only way to do that is to use the OLD Pivot Table Wizard (do not use the regular Techniques from the Insert Tab or he shortcut ALT, N, V)
The Only way to open the Old Pivot Table wizard is by using the shortcut ALT, D, P (consecutive not simultaneous).
The Old wizard consists of 3 steps: In step #2 you get a message box asking you if you wish to use a fresh source of the Data >> and definitely you do >> That will enable you to create a different grouping level.
Hope that helps
Nabil Mourad
nabilmourad thanks for the detailed response! This seems to be exactly what I'm looking for (particularly since the other option of manually resetting the cache by adding one extra row will not work for me, as this is a weekly report updated with additional data meaning all the pivots have a source of 'RAW DATA'!$A:$S so that I can simply add data and refresh week over week).
However, that shortcut is not working for me. Neither is the shortcut https://support.office.com/en-us/article/consolidate-multiple-data-sources-in-a-pivottable-8f476919-40b3-4133-9870-26f4d9f21ad6 which is COMMAND + ALT + P. To clarify, I am on a Mac so the alt key for me is Option.
I have tried applying these shortcuts with the data selected, with a blank cell selected, and with an existing pivot table selected - none worked. Any idea what I am missing?
To clarify, this is Office 365. I am seeing from https://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/consolidated-pivot-tables-excel-2016-mac/eaff9e34-5533-4a2a-8223-43380e0db70c that others have had issues locating the wizard in the newest Excel.
Thanks in advance for the help!
Hi
Please note that the shortcuts using ALT are consecutive shortcuts. Unlike the CTRL shortcuts which are simultaneous shortcuts
By default Excel uses the same pivot cache. You need to create a new pivot cache.
Hi Detlef_Lewin - thanks for the response. What I'm looking for is how to not share caches for the same data source in 365.
I'd prefer not to do the trick of adding one extra row of data to manually create separate caches, because this is a weekly report that will have manually updated data - the idea is all pivots have a data source of columns A:S and all pivots get refreshed once.
All other fixes, such as opening the pivot table wizard or creating a new table rather than copy and pasting, do not seem to work in 365.
