MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Tips for minimizing file size?


Posted by Jen on June 19, 2001 12:49 PM

I have a 45 tab spreadsheet that is a whopping 21 mb. I have no external links, but plenty of links within the ss. Any tips on minimizing the file size? The only function I use in the whole thing are some "IF" statements. It is a budget - pretty simple calcs, just a lot of them. HELP!


Posted by Damon Ostrander on June 19, 2001 1:26 PM

Jen,

Based on the little information you have provided, I can only guess that the reason the file size is so big is that you have a lot of cells that have formulas in them, and these formulas are redundant. For example a column of thousands of cells that contain a formula that has been copied (extended) down. Thes means that all of these cells not only contain the value that the formula yields, but also the formula itself. Since each formula is stored in string form, every 8 characters (bytes) of formula is equivalent in size to the memory required to store the result. Thus a workbook that contains 24-character formulas could be as much as 4 times the size of the same workbook with just the values stored. There are two ways to reduce these memory requirements:

1) copy and paste the cells back in place as "Values only". This of course would eliminate the calculating functionality of your workbook, but might be satisfactory if you are only trying to save or distribute results.

2) switch the calculations of large amounts of data (e.g., very large ranges of repetitive calculations) to VBA. In cases like these, a few lines of VBA code can often accomplish what otherwise requires tens of thousands of cell formulas.

The problem could obviously be any number of other thing, for example embedded pictures, but probably is the problem described above if you are not doing anything unusual.

I hope this helps.

Damon