SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

Improving Excel Memory and File Performance

26 June, 2014

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.

Comments

What a pleasure it is to read about excel by an author who speaks "easy to read" narrative! Right after solving my memory problem I'm going to look at your book an other materials with regard to Excel. THANK YOU. Richard

Submitted by Anonymous on Fri, 20/02/2015 - 02:10.

Thanks for your kind comments Richard, I'm glad you enjoyed the article!

Submitted by danielle on Fri, 20/02/2015 - 09:07.

Agree with Richard. Nice article Danielle and thanks for your help. Nick

Submitted by Anonymous on Tue, 10/03/2015 - 10:43.

Yes, I agree, this article is direct and simple and a good problem solving guide to Excel memory loss.

Submitted by Anonymous on Fri, 17/04/2015 - 12:12.

was wondering why we were having memory issues on such fast systems - turns out we are running 32 bit on 64 bit machines! time to unlock some of that speed and memory with the 64 bit version! Thanks for the tips and your other article regarding 64-Bit Excel.

Submitted by Anonymous on Wed, 24/06/2015 - 01:42.

The article was very helpful. Thank you. But can you help with other referencing functions which use less space.

Submitted by Anonymous on Tue, 08/09/2015 - 19:43.

Nice informative article.

Submitted by Anonymous on Tue, 16/02/2016 - 19:29.

very good article..will help me tremendously in my data crunching.

Submitted by Anonymous on Thu, 05/01/2017 - 07:01.

Thank you, informative article

Submitted by Anonymous on Mon, 13/02/2017 - 10:49.

Read this from Microsoft... "Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column." Seems like the old practice of not selecting entire columns (i.e. A:A) is not so much an issue with these functions. source: https://msdn.microsoft.com/en-us/library/office/mt709003.aspx

Submitted by Anonymous on Wed, 24/05/2017 - 06:06.

Yes, I believe Excel 2016 is much more efficient with its calculations. Many companies haven't upgraded yet though and it's still not a very good practice in any case.

Submitted by danielle on Thu, 08/06/2017 - 12:08.

Post new comment

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.