Massive 300MB Spreadsheet why?

[cbzdmh]

Hi,
I've been given a 300MB spreadsheet and asked why is it so large?

Where do I start?

Any help would be appreciated.

 

[Azerty99]

Try format, sheet, if Unhide is an option, you have hidden sheets.

Another is to press end home - the cursor wil go to the bottom right - may reveal hidden sections.

 

[Weaver]

Hidden sheets, excessive use of formulas, formatting in non used cells, uncompressed images would be a good place to start.

I've found if you delete all the rows to the bottom of your used data and all the columns to the right, this often reduces sheet size, especially on older sheets that have had a lot of edits/insertions/deletions etc.

 

[cbzdmh]

There are 32 sheets, that's going to be an issue. (but i have many workbooks with 10+ sheets that aren't 100MB)

There is 1 very large data sheet 43 * 6919 that i think is then used to create many pivot tables.

Isn't there something in Excel where it makes a pivot from the same data or recreates from a new data source? Could this cause the growth?

 

[Kaps_mr2]

Yes there is and it could be the cause of your problems - they have recreated the data several times over - rather than using the same source.

Look at the Pivot table wizard. Thanks

Regards

Kaps

 

[cbzdmh]

Is there anyway to see if two pivot tables are using the same data source. I know the data comes from the same table but how do i know for sure if they have created a seperate "cache" <-- is this correct.

 

[sirrahmichelle]

I would suggest running Excel Diet. It's helped me cut a lot of my documents' sizes in half.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

Be sure to try it on a "copy" of your spreadsheet and check the functionality prior to saving and rolling with the new document.

 

[someothersoldiers]

Hi Guys, I discovered this Excel Diet searching elsewhere and was wondering how reliable it is? I have tried it on files of 35Mb which it has flattened to 300kb, it seems to have kept all the formulae that I wanted but I was just wondering whether anyone else had any experience good or bad? It seems quite the panacea for my purposes but is it too good to be true?

I also seem to have no problem running in 2007 but when I try in 2003 i am getting a syntax error and the macro won't run, I have changed nothing just am running from a colleagues pc which has not yet upgraded to 2003. Any idea anyone?

thanks

Noel