WHY PIVOT TABLE IS TAKING HOURS TO LOAD

[labeeb_amani]

I have an excel with 25 pivot tables loaded from Power BI dataset using single connection . All pivot table contain 700 rows and maximum of  15 columns,   And also having extra calculated columns from the pivot table values. While adding extra pivot tables(from same connection) to this existing workbook , I am facing incremental slowness (from 10 minutes to 20 m,30 m ,1 hr etc )  in new pivot tables loading when applying filters or dragging fields . Currently  any of the pivot table is not even refreshing after hours and hours!!.  

My workbook structure:

• 30-40 pivot tables with calculated columns
• 4 Main sheets (700 rows and 150 columns)  with values are loaded from different pivot tables using DAX formulas (INDEX, MATCH etc) 
  
  
• 1 slicer connected to all pivot tables
• 1 slicer connected to 5 pivot tables
• Different buttons used with macros (for hide and show columns, refresh all pivot tables using button etc)
  
  Looking for the root cause of this slowness.please help!
  
  
  

[#2]

I had something like this a long time ago and it was to do with each individual pivot table having a separate cache. Try un-checking the 'Save Source Data' for each pivot table, in the 'Data' tab of 'Pivot Table Options'

[#3]

@ Charla74 Thanks a lot for your superb advice. I had this lagging and dragging since months and luckily i ran into this post and just unchecked the tick box and now my sheet works like a charm Thanks again for such a good suggestion. Keep it up.

[#4]

@sperveizhotmailcom 

Awesome!  Glad I could help.