That was actually very helpful and exactly the issue that I was trying to resolve - to stop all pivot tables changing at once. I particularly enjoyed the one cache - different tables work around by adding and removing the extra line. Thank you!
Excellent overview. Thank you! I plan to try some of the techniques to optimize a large model I run everyday. The resultant XLSM is currently around 17MB - likely due to number of PT.
Hi Mynda, just today, I was explaining this concept to my colleagues and then came across your video and I felt like, I should have watched it before! Nevertheless, your video has given me further clarity on Pivot Cache and I am surely benefitted. Thanks a lot! 🙂
Great stuff! It would be better if date grouping was independent even if the pivot tables share the same cache. I encountered a problem today because I wanted dates to be grouped in one pivot table and not grouped in another one. Since the source was Power Query, I had to create a duplicate date column to overcome this problem.
Very very good. Another fascinating video from Mynda - Thanks. I watch Mynda because I instruct an Excel course. Multiple caches is a technical obstacle in that course (but rather complex for the level of the course ). 1. Alt-D -P. I have never seen this Pivot table/chart wizard with the yellow box before. Indeed I can't find a way to get to it normally as insert - Pivot Table/Range takes me beyond this and to the old style wizard (and no yellow). Without alt-D-P the option to use the same base data never appears. Curious. 2. Multiple caches vs single cache? I like to separate the data investigation phase from the data presentation/visualisation phase. In investigation is it useful to be able to have full flexibility with each individual pivot table. I want to be able to filter or group it however I, as an analyst, want to. And it is on my pc so space is not an issue. So multiple caches. But if we create a dashboard that needs to be distributed widely. Not only space is an issue, but users will expect all slicers to work together on the dashboard. So Single Cache is best then. 3. Report connections is a tool that allows slicers to operate on multiple pivot tables/caches. In theory select the slicer and right click (or Slicer tab): Report Connections should allow you apply it to your choice of Pivot tables which should all be listed. This doesn't work reliably (and in once example using Office 2016 instantly closed down Excel with no file saved). 4. Fixing Report Connections. So the course I instruct goes from 3 investigative pivot tables and charts to a single dashboard (which makes sense only as part of a training course!). Report connections for each slicer show only one or two Pivot tables (which I guess means one or two caches). The Solution is to take one Pivot Table or chart, go to the data source (Change Data Source wizard - similar to your technique), delete the range or table name in the wizard, then select one cell in the range, Control-a to expand from one cell to the whole table which puts exactly the same range name / Table name in exactly the same place and click OK. Magically Report Connections with any slicer now shows all 3 Pivot tables. (Normally this needs be done once which is odd, but if not all tables appear perhaps it needs repeating with another Pivot table). There seems to be no rhyme nor reason as to how this solution works. My contribution to this is an hour of so googling - wish I could recall who the genius who solved this was.
Glad you liked it 🙏 1. Alt > D > P IS the old PivotTable wizard. It has been replaced with the one we have now, which is why you can't get to it any other way 😉 2. useful, but be careful with separate caches that you don't end up with two versions of the truth. 3. I've never experienced issues with Slicer report connections, but I have had 365 since Excel 2016 came out. 4. Editing the source will be changing the cache the PivotTable is using so that it's now sharing the cache and therefore the Slicers can now see the edited PivotTable.
@@MyOnlineTrainingHub Thanks very much for the response. 3. Report Connections fails on 365 too. And 2021 and 2019. I'm following directions from a course and it may be there could be a better way of creating the separate Pivot tables that avoids the issue. 4. Good point, though the pivot tables were all in existence before the slicers were created so no idea why they couldn't be found by the slicers. Still the nuances aren't really relevant to the level of my course. So long as I can get them to make it work. (At the end I tell them to learn more excel by watching you and Leila).
Great video. two Eye openers: if you have one cache (copying the PT) there is no difference between "Refresh" and "Refresh all".... And I also did not know you could remove the source data, together with the option not to drill down, this could be very handy.
Great vid! I've been doing a variant of your external method. I copy my data source to a temp external file and create the second PT from there. This creates a new cache. Then I amend data source to the original and delete the temp file. Seems to work.
Great video! On a daily basis, I refresh Power Query with new data. How do I keep the PivotTable I built and refresh it with new data without rebuilding the PivotTable?
@@MyOnlineTrainingHub Thanks! I need to have PQ-Table-PT to clean raw data; and the Table is for user. Added to Data Model, Disabled Background Refresh. Yet when Refresh twice PT did not refresh with new data ☹. Any help is appreciated
Nice, thanks! Mynda, all my slicers are working perfectly except one. This is using the column which has a lot of formulas so the First time it works fine but after that even when I clear all the filters, it works the opposite. When I select a few options in the slicer to get the value, it gives me the value of what was not selected. I have no problem with any other slicer. Is it because of formulas or some other reason? If I close the Workbook and reopen it then it works fine for the first time but after that, it does the same thing. Any advice? Thanks
I'd say it's a cache issue. The formulas are possibly updating after the PivotTable cache is updated. Slicers are primarily designed to work with Text, so if your formulas are returning numbers, then I can understand why there's a problem.
@@MyOnlineTrainingHub Thanks, Mynda! What would be the solution? One solution is that I don't use this field as a slicer. Any other solution, please? Thanks
Hard to say without seeing an example. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Yes! The Pivot Cache retains items that are no longer in the data set. You can remove them in the PivotTable Options > Data tab > under 'number of items to retain per field' choose 'None'.
Me: I know everything about pivot tables. Lynda: if you double-click on the Grand Total you get a full dump of the pivot cache. Me: wait, what?! I didn’t know that! ;-) Here’s a golden nugget in return: using PQ you can load tables into the PTC that are far larger than 2^20-1 rows (far larger). The resulting filesize will be big (bigger that when using the data model), but it can be done, and you still have grouping in the rows.
I'd like to get to know more technical insights - thanks for explaining the pivot cache!
My pleasure!
That was actually very helpful and exactly the issue that I was trying to resolve - to stop all pivot tables changing at once. I particularly enjoyed the one cache - different tables work around by adding and removing the extra line. Thank you!
Glad it helped!
Thanks Mynda, very helpful!
Cheers, Chris!
Thanks for putting that issue into structured pros (and video) Merinda. Over the years I have tried many workarounds without knowing the science!
Glad it was helpful 🙏
Excellent overview. Thank you! I plan to try some of the techniques to optimize a large model I run everyday. The resultant XLSM is currently around 17MB - likely due to number of PT.
Hope it helps!
Thanks always interesting - these days I find myself using array formulas and would love to see strategies/tips for managing sluggish files.
Glad you liked it 🙏
Hi Mynda, just today, I was explaining this concept to my colleagues and then came across your video and I felt like, I should have watched it before! Nevertheless, your video has given me further clarity on Pivot Cache and I am surely benefitted. Thanks a lot! 🙂
Glad to hear, Vijay 🙏
Great stuff! It would be better if date grouping was independent even if the pivot tables share the same cache. I encountered a problem today because I wanted dates to be grouped in one pivot table and not grouped in another one. Since the source was Power Query, I had to create a duplicate date column to overcome this problem.
Yeah, the date grouping can be a hassle.
I have always wondered how the size of my files were affected by Pivot Tables. Thanks for the comprehensive explanation. 😊
Happy to help!
life saver tutorial indeed *****
Glad it'll be useful 😊
its satisfying to learn that.
Great to hear!
Like, regards from Cali-Colombia
Glad to hear!
You are a stunner.
Great stuff... explanation is superb
Glad you found it useful 🙏
Just lovely
Glad you liked it 🙏
Wow!
Cheers, Sergio!
Very very good. Another fascinating video from Mynda - Thanks.
I watch Mynda because I instruct an Excel course. Multiple caches is a technical obstacle in that course (but rather complex for the level of the course ).
1. Alt-D -P. I have never seen this Pivot table/chart wizard with the yellow box before. Indeed I can't find a way to get to it normally as insert - Pivot Table/Range takes me beyond this and to the old style wizard (and no yellow).
Without alt-D-P the option to use the same base data never appears. Curious.
2. Multiple caches vs single cache?
I like to separate the data investigation phase from the data presentation/visualisation phase. In investigation is it useful to be able to have full flexibility with each individual pivot table. I want to be able to filter or group it however I, as an analyst, want to. And it is on my pc so space is not an issue. So multiple caches.
But if we create a dashboard that needs to be distributed widely. Not only space is an issue, but users will expect all slicers to work together on the dashboard. So Single Cache is best then.
3. Report connections is a tool that allows slicers to operate on multiple pivot tables/caches. In theory select the slicer and right click (or Slicer tab): Report Connections should allow you apply it to your choice of Pivot tables which should all be listed.
This doesn't work reliably (and in once example using Office 2016 instantly closed down Excel with no file saved).
4. Fixing Report Connections. So the course I instruct goes from 3 investigative pivot tables and charts to a single dashboard (which makes sense only as part of a training course!). Report connections for each slicer show only one or two Pivot tables (which I guess means one or two caches).
The Solution is to take one Pivot Table or chart, go to the data source (Change Data Source wizard - similar to your technique), delete the range or table name in the wizard, then select one cell in the range, Control-a to expand from one cell to the whole table which puts exactly the same range name / Table name in exactly the same place and click OK.
Magically Report Connections with any slicer now shows all 3 Pivot tables. (Normally this needs be done once which is odd, but if not all tables appear perhaps it needs repeating with another Pivot table).
There seems to be no rhyme nor reason as to how this solution works.
My contribution to this is an hour of so googling - wish I could recall who the genius who solved this was.
Glad you liked it 🙏
1. Alt > D > P IS the old PivotTable wizard. It has been replaced with the one we have now, which is why you can't get to it any other way 😉
2. useful, but be careful with separate caches that you don't end up with two versions of the truth.
3. I've never experienced issues with Slicer report connections, but I have had 365 since Excel 2016 came out.
4. Editing the source will be changing the cache the PivotTable is using so that it's now sharing the cache and therefore the Slicers can now see the edited PivotTable.
@@MyOnlineTrainingHub
Thanks very much for the response.
3. Report Connections fails on 365 too. And 2021 and 2019.
I'm following directions from a course and it may be there could be a better way of creating the separate Pivot tables that avoids the issue.
4. Good point, though the pivot tables were all in existence before the slicers were created so no idea why they couldn't be found by the slicers. Still the nuances aren't really relevant to the level of my course. So long as I can get them to make it work. (At the end I tell them to learn more excel by watching you and Leila).
Good stuff! :)
Glad you liked it!
Brilliant!
Thank you!
What can I say? Another great video. Keep up the good work.
Thanks so much 🙏
Great video. two Eye openers: if you have one cache (copying the PT) there is no difference between "Refresh" and "Refresh all".... And I also did not know you could remove the source data, together with the option not to drill down, this could be very handy.
Glad you liked it, Bart!
Great vid! I've been doing a variant of your external method. I copy my data source to a temp external file and create the second PT from there. This creates a new cache. Then I amend data source to the original and delete the temp file. Seems to work.
Thanks for sharing, Stuart 👍
You're lovely, Mynda.
Great video!
On a daily basis, I refresh Power Query with new data.
How do I keep the PivotTable I built and refresh it with new data without rebuilding the PivotTable?
See this tutorial on the fix for that: www.myonlinetraininghub.com/auto-refresh-pivottables
@@MyOnlineTrainingHub Thanks!
I need to have PQ-Table-PT to clean raw data; and the Table is for user. Added to Data Model, Disabled Background Refresh.
Yet when Refresh twice PT did not refresh with new data ☹. Any help is appreciated
Nice, thanks! Mynda, all my slicers are working perfectly except one. This is using the column which has a lot of formulas so the First time it works fine but after that even when I clear all the filters, it works the opposite. When I select a few options in the slicer to get the value, it gives me the value of what was not selected. I have no problem with any other slicer. Is it because of formulas or some other reason? If I close the Workbook and reopen it then it works fine for the first time but after that, it does the same thing. Any advice? Thanks
I'd say it's a cache issue. The formulas are possibly updating after the PivotTable cache is updated. Slicers are primarily designed to work with Text, so if your formulas are returning numbers, then I can understand why there's a problem.
@@MyOnlineTrainingHub Thanks, Mynda! What would be the solution? One solution is that I don't use this field as a slicer. Any other solution, please? Thanks
Hard to say without seeing an example. You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I have a question, what if we created a pivot table from an external data source, can we change it to a different source or a renamed source?
If you change the source usually you will have to build the PivotTable again.
Who is this strange lady in the white shirt?!😂
and who is that strange yellow stupid cartoon face called ugh ugh????!!!!
Yes, and no green colours at all 😅
She's mine hands off.
😁 green shirt is in the wash!
Oh dang.. gotta catch this one later.. or maybe in the car.. gotta run to LAX and get the Wife brb :-)
Drive safe!
so is this why main table column pull downs reflect old data from other tables even tho that data has been removed from them?
Yes! The Pivot Cache retains items that are no longer in the data set. You can remove them in the PivotTable Options > Data tab > under 'number of items to retain per field' choose 'None'.
Me: I know everything about pivot tables.
Lynda: if you double-click on the Grand Total you get a full dump of the pivot cache.
Me: wait, what?! I didn’t know that! ;-)
Here’s a golden nugget in return: using PQ you can load tables into the PTC that are far larger than 2^20-1 rows (far larger).
The resulting filesize will be big (bigger that when using the data model), but it can be done, and you still have grouping in the rows.
Yes, great point about the cache being able to store more than the Excel row limit 🙏
Xlookup
Here's my XLOOKUP tutorial: ruclips.net/video/2ViMm-wuM3U/видео.html
But.... but i need more cash😜🤪
😁