Pivot table caused 50 MB excel file

[Jignesh Thar]

I used more than 15 pivot tables in an excel file. It also have quite a lot
of formulas. Though there is hardly much of data, file size has increased to
50 MB.
In my quest to find out what caused this huge size, I deleted all the sheets
one by one and observed that workbook with all the sheets deleted was 7 MB!!
I am sure this is very common for excel to get heavy in size but any idea
how to shrink/compress it back to normal?
1. Is it caching, which needs to be cleared?
2. I have already deleted all the not populated rows and columns to tell
excel that data does not exist there!! what else can I do here?

 

[Debra Dalgleish]

Are all the pivot tables based on the same data? If so, there's sample
code here to change all the pivot tables to use the same cache:

http://www.contextures.com/xlPivot11.html

 

[Jignesh Thar]

Thanks, I would give this a try. But, it still does not explain 7.5 MB empty
file, right?

 

[Debra Dalgleish]

The workbook must have at least one sheet left in it. If you press
Ctrl+End, what cell is selected?
Are there any shapes, names, code, etc., in the workbook?

 

[Jignesh Thar]

I could not find any solution to this huge file and I rather moved data to
new file!!

 

[Karen]

Hi, I'm having the same problem. I have gone to the website, but there is no
instructions on how to change the pivot cache. Do you have a step by step
instructions on how to go about it?

I have a master data worksheet which is updated every hour, and several
pivots in the file. Several other files are also linked to the master data
worksheet.

I'm trying to make the file smaller. It is now 29,000KB.

Thanks,
Karen