Excel Memory Checking Tool
29 November 2016
I nearly forgot to mention this one...
Most of us sooner or later get hit by one or more of the dreaded messages “Out of Memory” or “Excel cannot complete this task with available resources” or “Not enough System Resources / Memory to Display Completely”, regardless of how much RAM or how big a swap-file you have.
Excel’s usable memory has been increasing steadily with each version:
- Excel 2003: 1 Gigabyte of working set memory
- Excel 2007: 2 Gigabytes of virtual memory
- Excel 2010, Excel 2013 and Excel 2016 32-bit: 2 Gigabytes of virtual memory
- Excel 2010, Excel 2013 and Excel 2016 64-bit: 131,072 Gigabytes of virtual memory
Although the introduction of the 64-bit versions of Excel in theory removed any real limitation many people were not able to switch to 64-bit Excel due to conflicts with existing software or out-of-date IT organisational policies.
In May / June updates, Excel ramped upits ability to access memory with what Microsoft referred to as a "Large Address Aware (LAA) Capability Change for Excel". This meant that:
- if you were using a 64-bit version of Windows this change would double the available virtual memory for 32-bit Excel 2013 from 2 Gigabytes to 4 Gigabytes
- if you were using a 32-bit version of Windows this would increase the available virtual memory for 32-bit Excel 2013 to 3 Gigabytes, although this was subject to technical caveats,
For more details on the LAA change see this Knowledge
Fellow Excel MVP Charles Williams has developed an Excel Memory Checking Tool to ascertain precisely how much virtual memory Excel is actually using and what the current maximum limit is for your installation. This is surprisingly difficult to deduce otherwise. You can check out his artcle on the subject and download his article here.