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: 131072 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 because
- Most OCX controls are only available in 32-bit
- Many third party addins are only available in 32-bit
But the need for larger usable memory has also been increasing:
- Excel models seem to get larger every year
- Each successive Excel version uses more memory than the previous version
- PowerPivot and other BI tools need a lot of memory
So earlier this year the Excel team announced and made available a change to 32-bit Excel 2013 and 2016:
Large Address Aware (LAA) capability change for Excel
If you are using a 64-bit version of Windows this change doubles available virtual memory for 32-bit Excel 2013 and 2016 to 4 Gigabytes.
If you are using a 32-bit version of Windows then this change can increase virtual memory for Excel 2013 and and 2016 to 3 Gigabytes, BUT:
- With 32-bit Windows you need to enable the /3GB boot switch
- This switch halves the amount of memory (from 2GB to 1 GB) available to 32-bit Windows.
This LAA change was introduced in updates in May and June 2016:
- For Excel 2013 you need to be using Build 15.0.4833 or later.
- For Excel 2016 Office 365 you need to be using Build 16.0.6868.2060 or later
- For Excel 2016 MSI you need to be using Build 16.0.4394.1000 or later
For more details on the LAA change see this Knowledge Base article
Excel Memory Checking Tool
Finding out how much virtual memory Excel is actually using, and what the current maximum limit is for your installation, is surprisingly difficult.
- Task Manager only shows working set memory, which is not the same thing as virtual memory.
- Process Explorer can show virtual memory used, but you have to add an additional column.
And I have not found a readily available tool that tells you what Excel’s maximum usable memory is. So I decided to create one using Windows API calls and VBA.
Here are a few examples of the output:
Excel 2013 32-bit with 64-bit Windows: 4GB
Excel 2016 32-bit with 32-bit Windows without the /3GB boot switch: 2GB
Excel 2016 64-bit with 64-bit Windows: 131072 GB
You can download the ShowMemory2 tool from here or here
Let me know of any problems!
Do you have an alternate ShowMemory2 download link?
(because I can not use onedrive for security reasons)
Here is an alternate link
Download link does not work !?
Could you retry?
Or use this alternate link
Would you mind explaining the columns you look at when using Process Explorer ?
Process Explorer->Select Columns->Process Memory->Virtual Size
But it does not show the maximum available, which you need to check to see if LAA is active or not.
I am wondering if you have tried this tool (http://www.ntcore.com/4gb_patch.php) for Excel 2010 and what the results are for it? it allows Excel 2010 to make use of 4GB of memory when running on a Win64 system
Do you have an ShowMemory2 alternate link? This is because i can not use onedrive for security reasons(js, browser dom storage, etc)
I want create a small cpu/memory usage taskpane based on your code in my NetOffice project.
(A perfect usefull example what i find, credits was given – of course, a private mail to public[at]sebastian.web.de would also helpful)
(a private note here as a german: people are not her governments, this vote for trump means not all americans are stupid. what i bevlieve is: a lot of people there vote for trump now shocked he realy win…)
ooops sorry !!! i gave you an answer in mail but no response in my mail account
now i see you answer me here and i didnt notice.
(please delete my answer’s – sorry*)