Microsoft Excel users will sometimes get a message that says their computer is out of memory, enough system resources to
display completely, cannot complete this task with available
resources or Excel can not open workbook with available resources. These messages are typically rare and they happen inconsistently. Excel 2007 and 2010 users tend to see them more than Excel 2003 users. Microsoft re-wrote large portions of Excel when they released 2007. This re-write appears to have affected memory usage.
Microsoft Excel, including Excel 2007, can be run in PCs with as little as 512 mg of memory. However, this is probably pushing the Excel memory to the maximum and Excel has little resources to do work. Loading large spreadsheets, spreadsheets with graphics, or running other applications, like Outlook at the same time is very likely to cause Excel to be extremely slow and potentially crash.
The following shows how much memory is used by Excel 2003 in various situations:
The number one thing you can do: If you have any COM add-ins installed, un-install them unless they are absolutely required. COM add-ins are a special type of add-in written in machine language. They are often installed without explicit approval. COM add-ins are often reported as causing memory problems (our add-ins are not COM add-ins). Two frequently mentioned as problems are Blue-Tooth and Adobe COM addins. To un-install COM add-ins:
- If you are using Excel 97-2003, download our free Add-in Information Lister and use it to un-install the COM add-ins.
- If you are using Excel 2007, click the round Office Button, Excel Options, Add-ins, change the dropdown box to COM add-ins and click Go.
- If you are using Excel 2010, click File, Options, Add-ins, change the dropdown box to COM add-ins and click Go.
The easiest way to run out of memory and get the message "Excel can not complete the task with available resources." is to have 1) Multiple Excel sessions open and 2) other applications open. Run only one Excel session. Many Excel users will open new Excel sessions each time a new workbook is opened via double clicking on a workbook link. To see if you have multiple sessions open, press ALT-CTL-DELETE and check how any Excel applications are running. There should be just one running. Regarding other applications, it depends on what they are and how much memory they need before a problem happens. If a new Excel session opens each time you double click on a workbook, try unchecking the Excel Option "Ignore other applications" if it is checked on the Options General tab.
We have gotten reports that the 64 bit version of Office 2010 has memory problems if other Office program are open.We do not recommend using the 2010 64 bit version of Excel. Very few users will see any benefit from it.
Some cases of out of memory or resources are caused by doing a copy and paste that is not valid. Instead of advising that one can not do such an action, Excel says either out of memory or out of resources. It can happen if one is trying to paste a selection containing hidden cells or has merged cells. Try unhiding all rows and columns and then doing the copy and paste. Also try removing all formats first In Excel 97-2003 one would select Edit, Clear, Formats. In Excel 2007-2010 we're not certain how to do (except by using the Spreadsheet Assistant)
Excel may think your worksheets are larger than you do! This can greatly consume memory. Normally your scroll area controlled by the scroll bars is very small. However, sometimes Excel thinks there are cells well below your used range. One way is to check where Excel thinks the last cell is located. Do this by pressing CTRL+SHIFT+END. If it well below your used range, then select all "unused" columns in this range and delete them. Then select all unused rows in this range and delete them . Then close and re-open Excel.
Install the latest upgrades to your version of Office. Detailed steps are found on our page on how to repair Office. Do not shortcut the steps. Use the Temp File Deleter to first remove EXD files prior to doing a SP upgrade and prior to doing a repair is very important. Also doing a repair of Office after installing an SP upgrade is an essential step.
If you have all the upgrades in place, do a repair of Office if you start getting memory or resource problems. We have noticed problems tend to appear after a Microsoft Windows automatic update or critical patch is done. Most PCs are set to run such automatically and often the user does not know any change was made to his or her PC. We suspect the update is causing the problem. be sure to run the Temp File Deleter before repairing Office.
Delete temp files on your PC. Use the free Temp File Deleter to do so.
If you are using Google Desktop Search, un-install it. Google Desktop Search appears to be a memory hog and has been reported to interfere with Microsoft Excel. Specifically, it installs a COM add-in that monitors every action in Excel so that it can index it. This obviously ties up a lot of memory and slows down Excel tremendously. The next suggestion advises how to remove COM addins.
Un-install any VBA add-ins you do not need. VBA add-ins are normally very passive and do not cause memory problems. We have 4-8 loaded all the time. Although our testing indicates VBA add-ins are not a major memory user, you should only install as add-ins those you need all the time. To un-install un-needed VBA add-ins:
- If you are using Excel 97-2003, select Tools and Add-ins. A dialog with the available add-ins will appear. Uncheck the ones you do not need
- If you are using Excel 2007, click the round Office Button, Excel Options, Add-ins, change the dropdown box to Excel add-ins and click Go.
- If you are using Excel 2010, click File, Options, Add-ins, change the dropdown box to Excel add-ins and click Go.
There is a very small chance that you have an un-needed add-in or workbook in your XLSTART folder.
Such open automatically.Search for the folder XLSTART on your PC. It may be a hidden folder so you will have to set options to show hidden folders while searching. Some add-ins install themselves in this folder. Such addins are not listed in the above dialogs. Remove any you files in XLSTART folder that you do not need (with Excel closed). Typically the folder should be empty or contain only Personal.XLS.
See if you have an alternate startup folder and check its content, and remove anything you do not need (with Excel close). One program known to do this approach is called GoldMine, and its removal solved one user's memory problems. To find your alternate startup location:
- If you are using Excel 97-2003, select Tools, Options, and the General tab.
- If you are using Excel 2007, click the round Office Button, Excel Options, Advanced, and go to the General section.
- If you are using Excel 2010, click File, Options, Advanced, and go to the General section.
Delete your XLB file. It can become corrupt but cause no noticeable problems. If corrupt it can consume lots of memory. Excel will recreate, but button customization will be lost. This is a file where Excel stores its toolbar settings. To delete it, use the XLB File Deleter which is a free product we provide. There have been reports that doing this will solve problems. Any special toolbar customization will be lost, but hopefully easy to add back.
It is possible that your printer or its driver is causing the problem. Several years ago HP printers were causing a memory problem with Excel. We do not know if HP fixed the problem and it may still be around or surfacing again. Change your default printer if you have other printers available. Do not use a system printer as your default if you can avoid it. See if there is an update to your printer's driver. Another solution is to specify a different printer as your default, even if you do not have the one you are specifying. This means you will have to change your printer when you need to print, which is a headache. Today we use a Canon laser G3 as our printer of choice and do not have memory problems. Back when we had problems with HP printers we switch to a Brothers laser and the problem went away. We have no plans of ever using a HP printer again.....
Extensive printing was one of the causes of memory problems in Excel 97-2000. In some cases doing a lot of page setup changes either manually or via macros can cause problems. This was a definite problem back with Excel 97-2000. Use of recorded macros that change print settings often change many settings that do not need to be changed. Optimize such code by eliminating what does not need changing. We suspect this problem is solved with Excel 2003 as we did 100 page setup changes from a recorded macro and memory usage did not change.
Use of macros that do very extensive file creating, data manipulation, and graphing have been known to cause memory leak problems. Such macros are ones that typically run for 30 minutes or longer. Minimizing the number of add-ins installed (especially COM add-ins) and closing all other applications can help. Closing and re-opening Excel after such extensive macro or add-in work is the best way to fix memory problems such intense work causes.
Use of many large arrays in VBA can cause problems. For example, ReDim Array1(10000, 10000) will cause an out of memory problem. Use of too many Public declared variables in VBA can cause problems as these stay in memory even when the application is done running. Public arrays are a double sin.
If you are using Outlook 2007/10, there have been reports of memory and resource problems if it is running at the same time as Excel 2007/10. We do not use Outlook and thus can not confirm. Try keeping Outlook 2007/10 closed when using Excel macros or add-ins . A side benefit is you will focus on your Excel work.
Get a new video card with lots of video memory. Excel 2007/10's charting system was re-written and may be using far more graphic resources than past versions. We do not have any recommendations on which card to get. If your PC does not have a video card and you can install one, that will free up memory. Especially if you are using Windows XP and already have 4 meg of memory installed. Changing your resolution to a lower setting will also help. If you do have a video card, you consider disabling the on board video to insure that memory does not get allocated to it. Making changes to BIOS settings can have adverse effects on the way a computer works. Use caution when performing BIOS modifications.
Use of Excel workbooks created on a Mac machine can cause problems. They are supposed to be in interchangeable, but we have had Mac users send up workbooks that will crash our computers. Try not to exchange files between PC and Macs!
Close Excel once every hour if you are doing a lot of editing or creating lots of charts.
If you have Track Changes turned on in Excel, turn off Track Changes as it uses a fair amount of memory. The default is Off.
Turn off AutoRecovery, as this takes another hunk of Excel memory. However, have a backup if you do. To turn off AuoRecovery:
- Excel 2003: Tools, Options, Save tab. Uncheck AutoRecovery
- Excel 2007: Office button, Excel Options, Save. Uncheck Auto Recovery
- Excel 2010: File,Options, Save. Uncheck Auto Recovery
Always open Excel as your first application. This gives it first rights to memory (or so we have heard). If you close Excel, close other applications and then re-open Excel to allow it first memory rights. Always open Excel before opening Outlook.
If you workbooks had a lot of conditional formats this can cause problems. Minimize use of condition formats whenever possible. There are reports of a bug in Excel 2007 where just copying and pasting conditionally formatted cells duplicates the formats over and over. Too many conditional formats can also cause slow workbook opening and slow response in Excel. We do not know if this bug was fixed in 2010.
Minimize the number of charts in your open workbooks. Charts can cause a significant demand on memory. In Excel 97-2003, you can select Tools, Options, View, and click on the option under Objects to "Show placeholders" to help on memory.
If your workbooks have a lot of formulas, see if there is a way to minimize formulas. Users with workbooks with tons of formulas often report memory problems. For example, if you create a lot of Vlookup formulas, consider doing a copy, paste special values to convert the results to values and eliminate the lookup.
Avoid Array formulas (ones entered by pressing CTL-Enter)
If your workbooks have links to other workbooks, Excel must open and read those workbooks in order to evaluate your formulas. Look for ways to avoid workbook links.
If you have a lot of Windows applications appearing in your system tray, remove any system tray application that are not essential. Each application in the system tray is running and using resources all the time. We try to minimize the number we have. Their removal can often be very difficult and there is no one common way to remove them.
Press ALT-CTL-Delete, go to the Processes tab and click twice on CPU to sort by memory use in descending order. The total CPU use is shown at the bottom. A level below 50% should be a cause of concern. Monitor the results for awhile to see what is running and consuming resources. Then do a internet search on the application name. Some will be routine Windows programs. Some will be anti-virus programs. Others will be candidates for removal.
Problems in your application data folder for Excel can be the cause. The folder is typically "c:\documents and settings\%username%\application data\microsoft\excel". This is a hidden folder, so set your Explorer options to show hidden folders. After backing up, rename or delete this folder and its subfolders. Reboot the machine and open Excel. Excel will recreate the folder and needed contents.
Run the following two commands from the Run box to reset the Excel registry entries: You will need to get the full path to your excel file to do so:
- "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /unregserver
- "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /regserver
These commands remove most of the Excel registry entries and then resets them. However, they do leave some residual settings.
A more extensive way to clean the registry is to rename the Excel registry key and let Excel recreate it. It depends on the version of Excel. First, close Excel. Then do Run, Regedit and go to the Excel registry key. It will be
where %version_number% is 11 for Excel 2003, 12 for Excel 2007 and 14 for Excel 2010. Rename this to OldExcel (this will back it up). Then re-open Excel. Excel will rebuild the registry entry. You will need to manually install any needed add-ins.
If you find other solutions that work, please let us know.