If you have sluggish Excel models, it might not just be the file size that is slowing things down. Often it’s the memory being used and when things get really bad, you might even get a “Not enough Memory” or “Not enough system resources to display completely” error message. When you get this frustrating message, the only solution is to close Excel and restart it, so let’s look at some ways to improve the performance of your Excel models.
Typically you might consider upgrading your RAM or switching from a laptop to a desktop to counteract these problems, but these are unlikely to help as it’s quite possibly the way that Excel is being used which is holding you back – not your machine.
Understanding Excel Memory Allocation
If you get a memory error message, check the task manager for memory utilisation. If you have a RAM of more than 2GB you’ll know that your computer has ample memory – although Excel is complaining about none being available. The reason for this is that Excel has its own memory manager and memory limits, regardless of the memory capacity of your machine. In fact all the Excel versions after Excel 2003 were designed to use a maximum of 2GB memory. So while your computer may have 4GB or even 8GB RAM, Excel can only use 2GB of that. If it reaches this limit you get the error messages. So upgrading your computer’s memory probably won’t help! The 64-bit version of Excel has boosted this memory limit exponentially. Hence, 64-bit Excel versions can use the entire system memory giving you much better performance, but read this article about the 64-bit version of Excel before you jump straight in and upgrade.
Countering Memory Exhaustion Problems
Now that you know why you have these limitations, we’ll see how to minimise their occurrence. Check this article for some tips on reducing file size and here are some more tips to reduce the working memory used by your Excel file.
When you are building a model in Excel, there are a few ways of working which are not only good practice, they will also reduce the chances of encountering memory problems in your model.
- Plan your Excel solution designs properly. If you insert a new column in the spreadsheet, it forces Excel to recalculate all of the formulae affected by that insertion. Depending on the number of impacted formulae and their complexity you could run out of memory. A better planned design ensures you can avoid such changes.
- Work from left to right. Excel starts calculating from the top left-hand corner, then continues across and down the sheet. This means that input values should be to the left and above the formulas which are referencing them.
- Avoid creating formulae referencing the entire columns or rows. Some Excel users create a formula using $D or $AA to reference the entire column to simplify the calculations. While it was fine with older Excel versions the new versions have over a million rows per column – so entire row or column references causes Excel to calculate the formula a million times, and work much harder than it needs to.
- Keep things neat. Try to keep things in one single workbook, as external links can slow things down, as well as cause errors. Keeping input cell and their formulae on one page will also reduce memory, but from a financial modelling best practice perspective it’s a good idea to split inputs and assumptions from their calculations.
- Check your used range. Sometimes Excel “remembers” a used range, even though you have deleted the reference, and this can increase the file size and memory usage. To check this on each sheet, press Control + End. This will take you to the last used cell of the workbook, and if it’s far beyond what you need, then delete the rows and columns which are not being used. This can greatly reduce the file size.
- Open every large Excel file on a separate “instance”; i.e. open up a new session of Excel for each workbook. The memory limitation in Excel is for every instance and not for the entire application. Opening every file on a separate instance gives you dedicated 2GB memory rather than shared. Ideally, you’d only have one file open at a time, but if you’re running a large process, you might like to keep working in Excel on another file whilst waiting for the large file to finish its process.
- Besides memory restrictions Excel also imposes a maximum source cell limit of 32,760 cells for smart filling. If you are planning to use the smart fill feature, you should look at just using the last 2-3 rows or columns and drag rather than selecting all the content from the first row. This will help control the number of source cells and ensure you do not reach this limit.
- Check for unwanted shapes: If you are adding pictures from websites into the spreadsheet, you could end up importing a lot of shapes which are not clearly visible but are eating into your memory. You need to open the selection panel (Home ribbon > Find & Select > Selection Panel) and remove all the shapes that you do not want.
- Lastly, check the add-ins and ensure they are updated. Typically, the updated versions of third party are more efficient and it can help improve the Excel performance greatly.