Sometimes when we carry out an operation in Excel, for example, running VBA and inserting columns, Excel stops working and returns a warning message indicating there isn’t enough memory to complete the action, as the one below:
The error of “not enough memory” usually happens when our workbook is large or contains many features, such as charts, VLOOKUP formulas, and PivotTables.
The issue of Excel not having enough memory to complete an action can be caused by several reasons.
This tutorial enumerates ten potential causes of the “There isn’t enough memory to complete this action” problem and explains possible solutions.
Cause #1: Insufficient System Resources
Microsoft recommends 2 GB RAM for a 32-bit version of Excel and 4 GB for a 64-bit version.
However, for optimal performance, Microsoft recommends 4 GB of RAM for a 32-bit version of Excel and 8 GB of RAM for a 64-bit version.
Consequently, if our computer’s hardware is inadequate or outdated, it may not have enough memory to run Excel optimally.
How to Fix?
Upgrade the computer’s physical RAM or replace the computer with one that has adequate system resources to run Excel optimally.
Cause #2: Too Many Applications are Running Simultaneously
Suppose we open too many workbooks and applications, and they are running simultaneously.
In that case, they hog the computer’s memory resulting in Excel not having sufficient memory to complete what we want it to do.
How to Fix?
Close all unnecessary workbooks and applications and try the operation you were doing again before the error occurred.
Closing applications removes them from memory, making more memory resources available to Excel.
Also read: Excel Not Responding – How To Fix It!
Cause #3: Large File Size
If we are working with a large Excel file that has many worksheets and features, it may require more memory than what is available on our computer.
How to Fix?
Reduce the file size by deleting any unused or unnecessary worksheets and data. Additionally, we can split the workbook into smaller workbooks.
We can split a workbook into different workbooks by using the steps below:
- Select the tabs of the worksheets we want to move to a new workbook, right-click the selection and choose Move or Copy on the shortcut menu that appears:
- On the Move or Copy dialog box, open the To book drop-down, select (new book), and click OK.
We have moved the selected worksheets to the new workbook.
We can repeat steps 1 and 2 if we want to push other worksheets to other new workbooks.
Also read: Can’t Push Objects Off the Sheet in Excel – How to Fix!
Cause #4: Insufficient Virtual Memory
Virtual memory is the area on the hard drive used as additional memory when the computer’s RAM is full.
If virtual memory is incorrectly set up or runs too low, it can trigger the “not enough memory to complete this action” error.
How to Fix?
Increase the virtual memory using the steps below:
- Click the Start button and choose Settings on the pop-up menu.
- On the Settings window, select the System option.
- Click the About option on the left sidebar of the extended Settings window.
- Click on the Advanced system settings option on the right panel of the Settings window.
- On the Advanced tab of the System Properties feature, click Settings on the Performance group, and click OK.
- On the Advanced tab of the Performance Options feature, click the Change button on the Virtual memory group and click OK.
- On the Virtual Memory feature, uncheck the Automatically manage paging file size for all drives, select the Custom size option, and type in the required values in the Initial size (MB) box and the Maximum size (MB) box (the rule of thumb is that the initial size be one and half times the available RAM and the maximum size be three times the available RAM). Finally, click the Set button and click OK.
- Restart the computer for the changes to take effect.
- Open the Excel file you were working on and try the operation you were doing again before the error occurred.
Also read: Copy-Paste is Not Working in Excel – How to Fix?
Cause #5: Corrupt Excel Installation
A corrupt Excel installation can sometimes cause the “Not enough memory to complete this action” error.
How to Fix?
Repair the Excel installation or uninstall and reinstall Excel.
Cause #6: Outdated or Conflicting Add-ins
Excel add-ins such as Analysis ToolPak and Stock Connector add new features or capabilities to Excel.
However, if the add-ins are outdated, they can cause the “Not enough memory” error.
How to Fix?
Get updated add-ins or remove or disable add-ins that are not essential.
You can also try disabling the add-ins one-by-one, and check after which add-in disablement the error prompt goes away. This will help you identify the add-in that is causing the problem.
Also, try re-enabling the add-in after disabling it once. If re-enabling the add-ins continues to show the “There isn’t enough memory to complete this action” message prompt, you can be sure that this is the problem with this add-in, and it will continue as long as this add-in is enabled.
Also read: How to Create and Use an Excel Add-in
Cause #7: Memory Leaks
Software bugs or programming errors can cause Excel to leak memory leading to the “not enough memory” error.
How to Fix?
Restart Excel or the computer to clear the memory leak.
Cause #8: Limitation of a 32-bit Version of Excel
A 32-bit version of Excel can only utilize a maximum of 4 GB of RAM because the maximum memory addressable by a 32-bit operating system is limited to 2^32 bytes (4 GB).
Consequently, the sizes of workbooks in a 32-bit version must be smaller than 4 GB of RAM. Otherwise, we get the “not enough memory” error.
How to Fix?
Upgrade to a 64-bit version of Excel that runs on a 64-bit system which can address significantly more memory and provide better performance for memory-intensive applications like Excel.
How to Upgrade to a 64-bit version of Excel
If we have a 32-bit version of Excel, we can use the following steps to upgrade to the 64-bit version:
- Check if our computer is running a 64-bit version of Windows by doing the following:
- Click Start and open Settings.
- Select the System option on the Settings window.
- Click About on the left sidebar of the Settings window.
- Check the System type under Device specifications.
- If you have a 64-bit version of Windows, we can download and install the 64-bit version of Excel from the Microsoft website. After the installation, you may need to activate your copy of Excel by supplying a valid license key.
Cause #9: Automatic Calculation Mode
By default, Excel is set to automatic calculation mode, automatically updating calculations whenever we change the worksheet’s data.
However, working with large or complex spreadsheets in automatic calculation mode can slow the performance of Excel and even result in the “not enough memory” error.
How to Fix?
Set Excel to manual calculation mode so Excel only updates calculations in the worksheet once we command it by pressing F9 or clicking the Calculate Now button in the Calculation group on the Formulas tab.
Setting Excel to manual calculation mode speeds up the performance of Excel by reducing the amount of calculation done in the background.
To set Excel to manual calculation mode, we use the following steps:
- Click the File tab to open the Excel Backstage window.
- Click Options on the left sidebar of the Backstage window.
- On the Excel Options dialog box, select Formulas on the left sidebar, select the Manual option under the Calculations options on the right, and click OK.
Once we have set Excel to manual calculation, we can update calculations in our worksheet by pressing F9 or clicking the Calculate Now button in the Calculation group on the Formulas tab.
Cause #10: Not Updating Windows and Excel
Not updating Windows and Excel can result in various potential issues, such as performance problems, compatibility issues, and “not enough memory” errors.
How to Fix?
Regularly update Windows and Excel.
Windows, by default, automatically updates, but sometimes you may need to check for and install updates manually by doing the following:
- Click the Start button and open Settings.
- Click Windows Update on the left sidebar of the Settings window and click the Check for updates button on the right.
Regarding newer versions of Excel, updates are automatically downloaded and installed.
However, sometimes you may need to manually check for and install updates by doing the following:
- Click the File tab to open the Excel Backstage window.
- Click Account on the left sidebar of the Backstage window.
- Under Product Information on the right, open the Update Options drop-down and choose Update Now.
This tutorial explained ten possible causes of the “not enough memory to complete this action” issue in Excel and described ten solutions to the error.
We hope you found the tutorial helpful.
Other Excel articles you may also like:
