Excel Memory Limits
Out of Memory, Memory Limits, Memory Leaks, Excel will not start.
If you work with large workbooks, external data or charts sooner
or later you will get one 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.
The amount of memory a workbook is using has no effect on Excel calculation speed, which in fact depends mainly on the number of formulae and the number of cells calculated/referenced by each formulae.
Note that Excel 2003, Excel 2007 and Excel 2010 will allow you to make effective use of much more RAM than previous versions.
Excel's memory manager in Excel 95, 97, 2000, 2002 and 2003 does
not make use of all available RAM or virtual memory, regardless of
the Windows operating system being used (Win95/Win98/Win ME or Win
NT4/Win 2000/Win XP/Vista).
So buying more RAM or increasing your swap file size probably won't help! (see Memory Needed for recommendations on RAM and swap file).
You may also encounter "Memory Leaks", where certain tasks such as printing or inserting graphics causes the memory Excel uses to increase, so that you eventually run out of memory and have to close Excel.
Excel has a number of memory limits which apply to different items. These limits operate relatively independently of each other. They mainly apply at the Application level, and so are cumulative across all the open workbooks, although you can open two instances of Excel and each instance will have these limits independently of the other.
This page contains a number of references like "see MSKB 313275 " which refer to articles in the Microsoft Knowledge Base (MSKB).
You can access MSKB articles using a URL of http://support.microsoft.com/?kbid=n where n is the identification number of the article (313275 in the example above).
Memory Limits by Excel Version
Excel 95, Excel 97 and Excel 2000 are officially limited to 64MB of formula memory for workbooks (heap space).
Excel 2002 is officially limited to 128MB of formula memory for workbooks (heap space).
Excel 2003 is officially limited to 1 Gigabyte
(GB) of memory.
Excel 2007 is limited to 2 Gigabytes of
memory for the Excel process under Windows XP/Vista (Windows memory
Many of the individual memory limits listed below have been removed in Excel 2007.
Excel 2010 is available in 2 versions: 32-bit
(2 Gigabytes of virtual memory) and 64-bit (8-Terabytes (which is
8000 Gigabytes of virtual memory)).
VBA Memory Limits
32-bit versions of Excel seem to have a memory limit of about 500MB
for VBA (arrays, code etc).
Some Excel processes require large contiguous blocks of memory. Sometimes the total amount of free memory may be large enough but is broken into many small areas separated by used memory. In this case Excel may not be able to obtain the required memory.
John White of XL Bridge was testing their GL Bridge product to extract large amounts of data from Oracle to an Excel 2007 Pivot Table. He found that for a single pivot table Excel 2007 would run out of memory at about 1.4 Gigabytes of virtual memory, far short of the published 2 Gigabyte limit. However creating multiple Pivot Caches enabled him to get memory used to about 2.08 Gigabytes of virtual memory.
Microsoft diagnosed this problem as being caused by memory fragmentation.
What is Heap Space?
Note that for Excel 95 to Excel 2002 this limit excludes the memory used by the Excel program itself and the Operating system. In Windows NT/2000/XP you can use Windows Task Manager to see the amount of memory used by the Excel process, which includes both the Excel program and the memory used for workbooks.
The Heap Space limit is documented in MSKB 313275, which describes "heap space" as being memory used to:
Measuring how much memory Excel is using.
You can find out how much "heap space" memory Excel is using for Excel 95 through Excel 2003 with:
Track cells sounds as though it refers to cells containing data, but this is misleading. This limit excludes cells containing data. In practice you can usually use between 64MB and 80MB of formula memory with Excel 95, 97 and 2000, and about 160MB with Excel 2002.
Because the Excel 2003 memory manager appears to have a different kind of memory limit (working space rather than heap space) you get a more reliable estimate of how much of the 1GB Excel is using from Windows Task manager than from Application.MemoryUsed.
For Excel 2007 the limit has changed again, and you need a more specialised tool to track useage of virtual memory, and Application.Memoryused no longer works.You can download Process Explorer from Microsoft Technet.
Individual Limits and Problems
The following list of limits and problems is far from complete.
See also Memory Leaks, and Measuring Memory.
Workbook memory: the memory used by Excel to store information about open workbooks.
Because the limits are more or less independent a workbook containing
both data and formulae could for instance contain 50MB of formulae
and 150MB of data.
You can download a test workbook containing a VBA macro demonstrating this: MakeInterlinkedSheets.zip (11KB)
Workbook or Excel will not open, and gives "Out of Memory" message.
If your workbook gives you an "Out of Memory" message and refuses to open, or you cannot open Excel even with a blank workbook, try these options:
A: Open the workbook using Excel 2003 (or 2002 if it was created in 97 or 2000), shrink it so that it uses less memory and save it, or permanently upgrade to Excel 2003.
B: Try the following steps. (They sometimes work but not always).
Links, Graphics, Zoom, Charts, Fonts, Printing and Memory Leaks
For additional information search the Microsoft Knowledge Base for things like not enough memory or "Out of memory" or "not display completely" or "insufficient resources".
Reducing Memory Used
You can reduce the amount of Excel memory used by: