How do I keep file sizes small when using multiple pivot tables?

I have a large spreadsheet of data (around 16mb) and I need to do lots of
pivot tables from that data. I have moved the pivot tables to a seperate
workbook but the file size is nearly 100MB and this makes it pretty unusable.
Is there any way to compress data in the pivot tables so you can still see
the data but keep the files small? Cheers for any help

Hi Jester,

What I recommend is that you set up one table, and base the rest of the
tables on that one table - this minimises the number of tables caching data.

Alternatively, you could send the data out, and automate the creation of the
tables with VBA using the On Open event.

Hope this helps

Warm regards

Sunil Jayakumar
"jester1072" <jester1072@discussions.microsoft.com> wrote in message
news:A5270BE2-EF2A-4630-AF00-874657611F20@microsoft.com...
>I have a large spreadsheet of data (around 16mb) and I need to do lots of
> pivot tables from that data. I have moved the pivot tables to a seperate
> workbook but the file size is nearly 100MB and this makes it pretty
> unusable.
> Is there any way to compress data in the pivot tables so you can still see
> the data but keep the files small? Cheers for any help


www.ayyoo.com/credit-cards.html

If you right click on a pivot table and select "Table Options", there is an
option "Save data with table layout".

If you uncheck this box for each pivot, your file size will reduce
dramatically (maybe 50%). When it is checked Excel stores an additional copy
of each pivot's source data "cache".

While this may seem like a no-brainer, there are tradeoffs (speed of
opening, closing, saving...) when doing this. 2nd half of the following
article details the pros & cons when it talks about Step 3 of the wizard:

How to optimize PivotTable performance in Excel 2000
http://support.microsoft.com/default...b;en-us;273583

While the article specifies XL2000, I think the information is valid for any
version that has the option available.

HTH,
--
George Nicholson

Remove 'Junk' from return address.



"jester1072" <jester1072@discussions.microsoft.com> wrote in message
news:A5270BE2-EF2A-4630-AF00-874657611F20@microsoft.com...
>I have a large spreadsheet of data (around 16mb) and I need to do lots of
> pivot tables from that data. I have moved the pivot tables to a seperate
> workbook but the file size is nearly 100MB and this makes it pretty
> unusable.
> Is there any way to compress data in the pivot tables so you can still see
> the data but keep the files small? Cheers for any help