I have a Modano model that has grown a rather large memory footprint. Now, I have upraded my computer to 64bit and upgraded Office to 2016 so I am fine, but this is a workbook I share with my coworkers and I am getting increasing tired off their moans and groans every time their Excel sessions runs out of memory when they save and I am somehow the one to blame(!). Large company with outsourced Helpdesk means getting them onto 2016 Office is going to be a painfully slow process so in the meantime I thought I would try to optimise the model as much as I could.
Does anyone have any experience with optimising 'heavy' workbooks, and if so what should I look to change to get maximum effect? Are there any specific formulas I should get rid off (I'm thinking Sumif/sumproduct but from a quick google I also noticed that Index,Match and offsets are other memory hungry formulas)? I have some conditional formatting, should I get rid of this?
Any help will be much appreciated!
Cheers
T
Hi Tarjei,
From my experience, there are 2 common types of performance issues experienced in Excel:
For the first type of issue, if you're building workbooks large enough to hit memory limits, you've got a justifiable case for upgrading to 64-bit hardware and software. You may still hit Modano memory limits in extreme cases even when using the 64-bit version of Windows and Office, but in most cases this is the point at which I've always considered simpifying the model scope, or switching to a database-style program instead of Excel.
For the second type of issue, the main side effect is very slow calculation speed, which can drive me crazy. I've most commonly seen this resulting from the use of a huge number of lookup functions, often converting data from one periodicity to another. This can be tough to avoid, but one approach might be to separate your analysis into separate workbooks if you can't get around using these functions, so that you can open them independently when 'using' the model rather than 'developing' it.
It says something about Moore's Law that, other than the large file allowances of the 64-bit versions of Excel, the calculation speed of Excel hasn't dramatically changed for well over a decade, from my experience. Very frustrating stuff.
Having said that, one thing I regularly tell even large clients is that by the time you're having issues with Excel performance, you've probably got bigger issues with the scope of your model...