Spreadsheet size (having check previous postings!)

[JB]

Hi,
I have a problem which seems common, a spreadsheet increasing size from
4-5mb to 30mb. I have tried all the solutions I have seen (deleting non
used cells all the way to recreating the spreadsheet, copying cells).
This makes me thing the problem may be some of the formulae.

The increase in size happened after a crash. Before I needed to
randomize some numbers. There was a row of 30 rand() cells, and a
matriz 2000x30 where each row (2000) added the corresponding random
number.

I was wondering if this could in any way be the cause of the size
increase or if any other formula could be responsible. The spreadsheet
does not use macros, user formulae or formatting.

Would appreciate any help!
Thanks
JB

 

[Alan]

I don't think its because of the RAND() cells. I just tried A1:A60,000 with
RAND() and it was only just over 3K.
Formulas will not do it either unless you literally have tens of thousands
of them and in particular volatile ones like SUMPRODUCT etc.
Have you tried finding the last used cell, say it was AB265, then selecting
and DELETING, not clear contents, rows 266:65536, then again selecting and
DELETING columns AC:IV
Regards,
Alan.

 

[CLR]

If the Workbook has more than one sheet, then take a copy of the file and
save it under a test name. Then one by one, delete the individual sheets
and save the file each time. Once you see a BIG jump in the saved filesize,
you will know which sheet is the culpret. Once there, if you have a large
number of formulas, say like in your 2000 X 30 matrix, you might consider
not leaving them there as actual formulas but filling the matrix with a
macro, and then doing Copy > PasteSpecial > Values so the file only saves
values insetad of so many formulas. Then the next time you want to update
the date, just run the macro to clear the existing data and insert the
new........

hth
Vaya con Dios,
Chuck, CABGx3

 

[JB]

Thank you very much. I will try to isolate the problem and post any
results

 

[JB]

Quick update, in case this can be useful to other users:

- identified 2 worksheets adding 10mb each to the spreadsheet
- one of the was intermediate calculations. I eliminated it and made
the full calculations in the main worksheet
- simplified second worksheet as much as possible. It is basically 2
matrices, first (40x2000) is a simple calculation selecting data,
second one (40x40) is a combination of sumif, index/match)

I deleted all nonused rows/columns and formats again. Size went down
from 30mb to 17mb, still much larger than before.

The spreadsheet does not have any charts or graphics, just numbers. I
find it odd that when I zip it, size goes down to 1.4mb. I have not
zipped spreadsheets before, but is it normal that the compression rate
is so high?

Thanks!
Miguel

 

[JB]

Quick update, in case this can be useful to other users:

- identified 2 worksheets adding 10mb each to the spreadsheet
- one of the was intermediate calculations. I eliminated it and made
the full calculations in the main worksheet
- simplified second worksheet as much as possible. It is basically 2
matrices, first (40x2000) is a simple calculation selecting data,
second one (40x40) is a combination of sumif, index/match)

I deleted all nonused rows/columns and formats again. Size went down
from 30mb to 17mb, still much larger than before.


The spreadsheet does not have any charts or graphics, just data and
formulae. I find it odd that when I zip it, size goes down to 1.4mb. I
was wondering if the high compression rate could be an indication of
some type of problem or corruption


Thanks!
JB

 

[Pete_UK]

You say you have tried this, but it is always useful to do CTRL-END on
a sheet - this will take you to where Excel thinks the last item of
data is on that sheet. If it is way beyond where your data actually is,
then highlight all the rows beyond your data and Edit | Delete (not
just deleting the contents) and highlight all the columns beyond your
data and Edit | Delete, and then SAVE the file - the memory savings
only come into effect after you save the file.

Another thing to check for is named ranges - these can add
significantly to the size of the workbook.

Hope this helps.

Pete