• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table (Removing Save data with table layout) in Excel 2007

D

Dheeraj

Member
Hi All,


I use Excel 2007 and in my report can have data of over 800000 rows and 30 columns. We have created around 10 to 15 pivots using this data. I know creating these many of pivots is a sin but it is required.


Since every Pivot table saves data with the table layout by default, file size was too large. I removed "Save data with table layout" option which helped me reducing the file size to an extent but now every time when I enter new data in the raw data sheet and refresh the pivots, it is taking long time to get refreshed i.e. I am delay in preparing my report and missing timeline.


Also, after completion and saving it, if I close the report and reopen (due to some requirement), excel doesn't let me select values from page field and asks me to refresh pivots once again which again takes time to refresh.


Plz help me on this?


Thank you,

Dheeraj
 
Are all the PivotTables based off of the original data (bad) or are they all based off of the original PivotTable (good)? I know this has often saved me up to 30 mb of memory, as XL is only having to store one Pivot Cache.
 
Hi Luke,


Yes, all PTs are based on the original data. Initially there were 2 to 3 pivots but as per the requests received from Operations Team on time to time to make changes in the report, number of pivots increased.


Since every pivot was saving the original data (which is really bulky) with its layout, I had to remove this option. I also saved workbooks in .xlsb format to further reduce file.


Thank you,

Dheeraj
 
I'd recommend then going to each PivotTable and changing the source data to be 1 PivotTable (I'll refer to this as Main Table). All the PIvotTable still will retain their ability to be sliced/filtered as needed, but you no longer have 10+ PivotCaches being stored in the file. Only the Main Table has generated a cache, and the others will not. See here:

http://office.microsoft.com/en-us/excel-help/about-pivottable-and-pivotchart-source-data-HP005199304.aspx


Under "Another Pivot Table" for more info.
 
You must log in or register to reply here.
Back
Top