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.
This is because Excel has its own memory manager and its own memory limits.

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 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).
I have not experimented with Excel on the Mac so don't know if the same limits apply.

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).

Excel 5 was limited to about 16 MB of memory for workbooks etc (heap space), and to a maximum of about 37120 rows containing information. This was documented in MSKB Q99345 , which is no longer available in the online MSKB.

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. This limit appears to be a limit on the working set memory used by the Excel process, which is the memory reported by Windows Task Manager.

Excel 2003 has a substantially increased memory capacity I have successfully used over 750MB of workbook memory on a 768MB RAM Windows XP system.
However many of the individual, specific memory limits listed below have not changed in Excel 2003.

Excel 2007 is limited to 2 Gigabytes of memory for the Excel process under Windows XP (Windows memory limit). This 2 Gigabyte limit is a limit on the Virtual Memory address space. Virtual memory used by a process is larger than the working set memory reported by Windows Task Manager, so the amount of useable memory under Excel 2007 is considerably less than twice that of Excel 2003.
Because Excel 2007 (Excel12) also requires more memory to store the indexes to the increased number of rows and columns you may not be able to load larger workbooks under Excel 2007 than was possible under Excel 2003.

Note that for Excel95 to Excel2002 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.

This limit is documented in MSKB 313275, which describes "heap space" as being memory used to:

• Track cells and formulas.
• Provide copy and paste functionality.
• Track pointers to objects.

You can find out how much "heap space" memory Excel is using for Excel 95 through Excel 2003 with:

• Excel’s worksheet function INFO("memused")
• Application.MemoryUsed from VBA.

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. You can download Process Explorer from Microsoft Technet.

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.

• Formulae, Pivot Tables and UNDO: between 64MB and 80MB
  • This has been increased to approx 160 MB in Excel2002 and approx 1Gigabyte in Excel2003.
• Maximum number of rows containing information: just over 1000000 (1 million) under Windows XP, unknown under Windows 98.
• Cells containing data: about 1GB memory under windows XP, unknown under Windows 98.
• Workbooks with both data and formulae can contain for instance 50MB of formulae and 150MB of data (provided under Windows XP that the number of rows containing information is less than about 1 million).
• Workbook file size is usually less than workbook memory, so a workbook file can be between 3MB and 60MB and still hit the 80MB memory limit.
• Opening 8 workbooks of 10MB each is the same as opening one workbook of 80MB.
• Opening a workbook may require more memory than saving it, so sometimes you cannot reopen a workbook you previously created!

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.
80MB sounds like a very large amount of formulae, but actually it represents somewhere between 40 thousand and 2 million formulae, which is a maximum of about 12% of a single worksheet.
As an extreme case I created a test workbook with only 40000 formulae whose file size is just over 3MB, but which exceeds the 80MB formula limit when saved and reopened. The workbook contains 200 worksheets, each containing 200 formulae, and the formulae link each sheet to every other sheet.

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).

1. Reboot your PC and start Excel in "Safe Mode", then try opening the workbook: click Run on the Start menu. Type the path to excel.exe, and add /s. Then press OK. Start by trying Excel /s and if that does not work you will need the full path, for example C:\Program Files\Microsoft Office\Office\Excel.exe /s Then open the workbook whilst holding the shift key down to stop any macros from executing.
2. Try using Excel 2002/Excel 2003 to open the workbook whilst holding the shift key down, .
3. Delete all the files in your temp directory, but make sure that you have completed any pending software installations first (you may need to reboot your PC). If you are not sure where your temp directory is you can access it using Start-->Run-->%temp%-->OK. Temporary files may accumulate in your \Windows\Temp directory (Win95/98/ME), or your \Documents and Settings\<user>\local settings\temp directory (Win2K/WinXP). Too many temporary files can cause problems, so clean them out from time to time.
4. Find out where your Excel toolbar file is located using Windows search (search for *.XLB), and rename it to something like Toolbar.Old. The .XLB file is used to store your customisations of the Excel toolbars, and sometimes it gets corrupted. If Excel cannot find the .XLB file it will automatically create a new one.
5. For more ideas see MSKB 280504 " How to Troubleshoot Startup Problems in Microsoft Excel"
   

Links, Graphics, Zoom, Charts, Fonts, Printing and Memory Leaks

• External links: see MSKB articles 167079 - 16375 unique cells per Worksheet in a closed workbook. This article actually slightly overstates the restriction: the limit is 16375 unique rows per worksheet rather than cells, so you can link to several columns in the same 16375 rows of a worksheet in a closed workbook.
• External Links: see MSKB 178086 and 214342 Unable to save External Link Values
• Graphic objects seem to cause problems frequently.
• Using Zoom ( ie Zoom NOT at 100%) sometimes uses a lot of memory.
• Using Zoom with Controls MSKB 183503
• Opening a workbook containing charts - see MSKB 213683 and 172948
• Adding charts - see MSKB 168650
• Copying a worksheet containing charts - see MSKB 264986
• Number of different fonts exceeds 255 (point sizes count as fonts) - see MSKB 255622
• Automatic Font Scaling in Charts - see MSKB 292263
• Printing and Memory Leaks

Miscellaneous

• Capability limits of Excel97 - see MSKB Q296053
• Disk is Full message - see MSKB Q21425
• Large autofills (limit is 32760 source cells) - see MSKB Q313275
• Repeatedly calculating custom functions - see MSKB 265023 and MSKB 136761
• Pivot Table limits - see MSKB Q104308 and Q162476 for XL97 and Q211517 and Q129160 for XL2000 and Q291061 for Excel2002
• Auto formatting an entire worksheet - see MSKB 211478 and 159863
• Resetting the Used Range in a macro - see MSKB Q244435
• Array Formula limits in XL2000 and Excel2002: max 65472 array formulas on a sheet that refer to other sheets - see MSKB 166342
• Array Limits for various Excel versions: - see MSKB Q177991
• Too many modules and forms open in the Visual Basic editor (close them!).
• Using a macro that was recorded to open a Text file - see MSKB Q134826
• Repeatedly Hyper linking between your Web Browser and Microsoft office - see MSKB Q157763 or Q199337 or Q297891
• Creating multiple Maps - see MSKB Q214380
• Office Clipboard maximum is 4MB or 8MB- see MSKB Q221461 and MSKB Q290373
• Unable to start Excel2000 - see MSKB Q305498
• Too many manual page breaks, limit is 1026 - see MSKB 284916
• Names nested more than 20 deep - see MSKB Q292471
• Rotated Text in Embedded Workbook using NT4 - see MSKB Q169718
• Pasting Symbols in Charts with Win95/98 - see MSKB Q247578
• Incorrect use of Cells property - see MSKB Q173182 or Q213682 or Q291067
• Saving a workbook from the VBE in dual 97 7 5.0/95 format - see MSKB 297024
• Repeatedly opening and closing workbooks containing controls - see MSKB Q238570 and 248180
• Not enough stack space to run macro - see MSKB 111867
• Office ClipBoard is full - see MSKB 221461

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:

• Checking that the Excel's last cell on each sheet is in the right place: Ctrl-End or Edit-->Goto-->Special-->Last cell. When its not where you want, for instance because of additional formatting or recently deleted cells, you can reset the last cell by deleting all columns to the right and rows below the correct last cell, and then saving the workbook. Note that since this process will alter any formulae that refer to the deleted cells you should make a backup copy first!
• Converting formulae to values (Paste Special Values).
• Switching off Change Tracking for Shared workbooks.
• Reducing the number of formulae referencing other worksheets. Formulae which refer to cells or ranges on other worksheets use significantly more memory for dependency trees than formulae which refer to cells or ranges on their own worksheet. The best way to do this is by moving the blocks of cells which are being referenced onto the sheet that refers to them.
• Using smaller ranges in formulae such as INDEX and VLOOKUP.
• Avoiding references to ranges containing unused or blank cells on other worksheets.
• Removing zero-sized objects.
• Using multi-cell array formulae.
• Storing repetitively used formulae in defined Names and using the Names in place of the formulae.
• Shortening the formula.
• Move the Pivot Tables to a separate Workbook.
• Reducing the number of fonts.
• Simplifying formatting.
• Reducing the number of graphic or embedded objects.
• Closing open Modules and Forms in the VBE.