Forum Discussion
Why are My Pivot Table Settings Linked?
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
nabilmourad I'm doing consecutive. Are you using Office 365 and able to access Pivot Wizard on Mac?
All the literature I can find - including petitions to bring it back - say that Pivot Wizard was removed completely on Mac beginning with Excel 2016, and this seems to be the case.
I have gone through ribbon command editions, done the shortcut you provided, done the shortcut provided by Microsoft's own literature on it, and nothing is working - seems this feature was removed.
