Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jester1072
 
Posts: n/a
Default 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
  #2   Report Post  
Sunil Jayakumar
 
Posts: n/a
Default

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" wrote in message
...
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


  #3   Report Post  
George Nicholson
 
Posts: n/a
Default

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" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple people accessing an Excel file from a server at once. UABCSA Excel Discussion (Misc queries) 1 May 2nd 05 05:41 PM
multiple worksheets in html - one file Mikael Excel Discussion (Misc queries) 0 March 3rd 05 08:55 PM
Multiple excel users can open the same file and edit at the same . Jeff Howard Excel Discussion (Misc queries) 1 February 18th 05 01:19 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM
how do i sync multiple copies of the same excel file? graphicdoug Excel Discussion (Misc queries) 1 December 30th 04 05:44 PM


All times are GMT +1. The time now is 03:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"