08-09-2019 12:05 PM - edited 08-09-2019 12:10 PM
08-09-2019 12:05 PM - edited 08-09-2019 12:10 PM
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!
08-10-2019 03:31 AM
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
08-12-2019 01:24 PM
@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 from this article 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 this forum that others have had issues locating the wizard in the newest Excel.
Thanks in advance for the help!
08-12-2019 01:44 PM
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.
08-12-2019 01:48 PM
Hi @Sergei Baklan - 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?
08-12-2019 02:44 PM
You may load into data model and work with internal data (tables, ranges) only. Data model will be saved within Excel file (as well as PivotTable cache if you din't uncheck this option), nothing special required to share the file.
As a minimum and as far as you don't use calculated fields you may know nothing about the data model to work with Pivot Tables based on it, except the option "Add data to data model" creating the Pivot table.
Attached is a small sample with 4 PivotTables (same source table), first two are cached, second two are on data model. You may play with them to see the difference.
08-13-2019 07:10 AM
@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.