How do I reduce an Excel with Pivot Tables file size?

[Marie]

I have an Excel file with four pivot tables that are fed from an Access
table. The Excel file is now 160+ megs. Zipped it's over 30 megs. I've
tried all the recommendations I've seen in the postings regarding deleting
unused cells, copying the pages into new files, etc., and nothing has helped.
Any recommendations? I'm using Excel 2003.

 

[Jim Thomlinson]

Data comes from the Database into the pivot cache and then it is displayed in
the table. The pivot cache is probably huge. There are 2 things that you can
do.

One is if your tables all use exactly the same data then all of the tables
can be built off of the same cache (sometimes whe you are building a table
you will be asked if you wish to base thone table off of another which is
basically the cache).

The other thing you can do is to not store the data in the cache. Under
Table Options there is a check mark for Save Data with Table Layout. If you
uncheck that it will not save the cache. The down side is each time you open
this file you will need to refresh the tables as there is no data stored.

 

[Marie]

Thanks for your suggestions. I reduced the Access table that feeds the pivot
table by over 3/4, but it didn't reduce the size of the cache, apparently.
When I don't save the data behind the pivot tables, it reduces the file by
50%, but other users have to be able to get into the pivot table when it is
distributed to them, and they can't refresh data, so this solution is
unrealistic in my case. I've tried cleaning the file, with no change to file
size. If you think of anything else I can do, I would appreciate your
comments. Thanks again.

 

[Jim Thomlinson]

Since you need to store the data with the table then the only way to reduce
the workbook size is to decrease the cache.

Is your data extracted from a table or a query. If you set it up as a query
then you can potentially reduce the number of fields and records uploaded
into the cache. Do each of your tables have their own cache or do they all
function off of one cache? If you are using multiple caches perhaps you could
reformat your data such that all tables use just one cache (which could help
you to avoid duplication between caches).