Query regarding file size

Hi Vish,

Sorry I couldn’t talk on the phone today.  Here are a couple of techniques that can help to reduce the file size – some of which you’ve probably already tried:

1.    Remove any unnecessary formatting or formulas.  Colours and formatting should only apply to the necessary range, not an entire row or column
2.    Make sure formulas are referencing only the range they need i.e. not selecting the entire row or column, as this will use more memory
3.    Avoid pivot tables (I guess you have) as these really chew up memory
4.    Remove external links to other files (use Edit Links to check if there are any external links, and then Break Links to paste them as values). 
5.    If you absolutely have to have external links, have the source file open
6.    Formulas such as INDEX, CHOOSE and OFFSET are generally considered to use less memory than LOOKUPS and SUMIF
7.    Think about using array formulas instead, as these can use less memory
8.    If you are still having problems, consider turning the calculation onto manual, then hit F9 only when you need to recalculate
9.    A last resort I have done in the past is to leave one cell at the top of the column with the live link, and paste all the other cells as values.  Copy the cell down and recalculate when you need to refresh.  Certainly not a preferred option though
10.    Last, but certainly not least, if you are using Excel 2003 (I think you are?)-  If you are able to upgrade to 2007, you will see a BIG difference in speed and file size.  See http://www.plumsolutions.com.au/articles/should-you-upgrade-excel-2007 for major differences between versions.

I hope this helps!

Submitted by danielle on Tue, 19/01/2010 - 11:35.

Hi Danielle,
 
Thanks for the information.
Have already tried some of the tips. the 2007 by far is the most effective one. It has reduced the file size to around 3 MB. But everyone does not have 2007 as yet.

1.       Remove any unnecessary formatting or formulas.  Colours and formatting should only apply to the necessary range, not an entire row or column  (already incorprated)
2.       Make sure formulas are referencing only the range they need i.e. not selecting the entire row or column, as this will use more memory  (already incorprated)
3.       Avoid pivot tables (I guess you have) as these really chew up memory  (not being used))
4.       Remove external links to other files (use Edit Links to check if there are any external links, and then Break Links to paste them as values).  (already incorprated)
5.       If you absolutely have to have external links, have the source file open  (Not applicable)
6.       Formulas such as INDEX, CHOOSE and OFFSET are generally considered to use less memory than LOOKUPS and SUMIF (this one will be a difficult one but will try to optimise. What are otur thoughts on sumproduct. Most of the formalas are sumproduct and vllokups)
7.       Think about using array formulas instead, as these can use less memory
8.       If you are still having problems, consider turning the calculation onto manual, then hit F9 only when you need to recalculate  (already incorprated)
9.       A last resort I have done in the past is to leave one cell at the top of the column with the live link, and paste all the other cells as values.  Copy the cell down and recalculate when you need to refresh.  Certainly not a preferred option though  (will be too much of a hassle)
10.   Last, but certainly not least, if you are using Excel 2003 (I think you are?)-  If you are able to upgrade to 2007, you will see a BIG difference in speed and file size.  See http://www.plumsolutions.com.au/articles/?p=31 for major differences between versions.  (has been the most effective so far but challanges in sharing with the rest of the team)

Submitted by Anonymous on Tue, 03/03/2009 - 11:36.

You've actually prompted me to write a blog article about this issue!  See:

http://www.plumsolutions.com.au/articles/excel-model-file-size-getting-o...

Submitted by danielle on Sun, 19/07/2009 - 11:38.