Excel Model File Size Getting out of Hand?
19 July, 2009
Once you start getting a fair amount of data in your financial model, it’s pretty easy to end up with an enormous great big hairy Excel file which takes ages to calculate, especially if you are using an older version of Excel. If you find your model getting out of control, here are a few tried and tested tips you can try to keep that file size down.
If you are using Excel 2003, by far the quickest and most effective way of reducing file size will be to upgrade to 2007 if you are able to. Because Excel 2007 .xlsx files are a completely new and much more compact file type, this is going to make a MASSIVE difference to your file size. See this blog article from the archives detailing the major differences between the versions of Excel.
If you’re still having problems, here are a few other things you can try:
- Remove any unnecessary formatting or formulas. Ensure colours and other formatting apply only to the necessary range, not an entire row or column. Importantly, go to the outer edges of your workspace and delete all (apparently empty) rows&columns from there to the end of the Excel sheet. There may be formatting you are unaware of.
- Make sure formulae reference only the range they need (i.e. not selecting the entire row or column).
- Pivot tables are pretty heavy on memory so try saving them as values, if you can live without the functionality.
- 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).
- If you must have external links, then have the source file open while working with the file as this can make the model work faster.
- Formulae such as INDEX, CHOOSE and OFFSET are generally considered to use less memory than LOOKUPS and SUMIF.
- Try using array formulas, as these can use less memory.
- Turn the calculation onto manual, then hit F9 only when you need to recalculate.
- For clarity of workspace, making charts in a separate sheet is advised over placing them in the same sheet as their data. However, they do take up more memory this way. If you have a lot of charts in your workbook, this could make a difference.
- A last resort one method 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.
- You may be able to increase your computer’s RAM. Consult your IT department/consultant (if you have one), or a relevant website for how-to.
- How new is your computer? Analysts working with large/intensive spreadsheets should have their machines upgraded regularly. Maybe you can put a case to your company (with reference to the amount of your time that is wasted waiting for processing, and re-doing/investigating when the calculation does not complete) to obtain a machine with a faster processor.
Have you got any tricks that have worked for you? Share them with us by adding your comments.
Hi Danielle,
These are all issues that readers of your site should take note of, as a suggestion here are a couple more.
1) Remove (or check the size of) any logos / images that have been used. A BMP can easily add 10Mb to a file size.
2) Avoid any manual formatting (any financial modeller should avoid this this anyway...if they are working for profit!) - use Styles but ensure that unused ones are removed as they seem to chew up space.
3) Use Navigator Utilities (no link to us) to check out unused Range Names (don't rely on Excel for this).
I used to use "FastExcel" to clean models up but later developed proprietary tools after it introduced more issues than it solved but they may have fixed that up by now. It used to shrink file the size of 'bad models' by up to 30%.....
Finally - when I started modelling there was a 1Mb file size constraint placed on my output due to email being in its infancy. This forced quite a lot of discipline that I don't see too much of these days - however I would say that the file size doesn't matter too much as long as the file zips down to 3-5Mb.
Keep up the great work!
Nick
Submitted by Nick Crawley (not verified) on Tue, 25/08/2009 - 18:11.
If you do any inserting and deleting, the size of the worksheet is larger than it needs to be. Microsoft hasn't seemed to have addressed this phenomenon yet, at least through Excel 2007. To see if your worksheet has this problem, press F5. Click Special (Alt S). Choose the radio button next to Last cell (Alt s). Click OK (press Enter). If that cell is outside of the boundaries where you have information, you can delete the excess cells around the perimeter, re-save the worksheet by closing it (close worksheet, click Yes), and enjoy the smaller size upon re-opening (another quirk that Microsoft hasn't fixed yet). Delete any unused tabs as well!
Submitted by Brian Doennebrink (not verified) on Fri, 04/09/2009 - 02:18.
One way to reduce size of Excel files is to keep short the number of characters used in labelling tabs / sheets as well as file and folder names, especially where several files are interlinked. As the number of cells containing external links multiplies so too the file size expands in direct proportion. Even more so as extra characters are added to sheet, file or folder descriptors.
Submitted by Drac on Wed, 23/09/2009 - 10:55.
All great suggestions - thanks for your input, guys!
Submitted by danielle on Thu, 19/11/2009 - 20:31.
Post new comment