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!

Thanks Mynda, very helpful!

I'd like to get to know more technical insights - thanks for explaining the pivot cache!

Thanks for putting that issue into structured pros (and video) Merinda. Over the years I have tried many workarounds without knowing the science!

Great stuff... explanation is superb

life saver tutorial indeed *****

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.

Brilliant!

You are a stunner.

Just lovely

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! 🙂

its satisfying to learn that.

Like, regards from Cali-Colombia

Good stuff! :)

Wow!

Thanks always interesting - these days I find myself using array formulas and would love to see strategies/tips for managing sluggish files.

I have always wondered how the size of my files were affected by Pivot Tables. Thanks for the comprehensive explanation. 😊

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.

What can I say? Another great video. Keep up the good work.

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. 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.

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.

You're lovely, Mynda.

Oh dang.. gotta catch this one later.. or maybe in the car.. gotta run to LAX and get the Wife brb :-)

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?

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

so is this why main table column pull downs reflect old data from other tables even tho that data has been removed from them?

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?

Who is this strange lady in the white shirt?!😂

Xlookup

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.

But.... but i need more cash😜🤪