If you encounter the error message, “Excel ran out of resources while attempting to calculate one or more formulas,” there could be several reasons behind it. Fortunately, there are some potential solutions you can try to resolve the issue.
The likely causes and solutions for this error will be discussed in this post. Try the suggested fixes to resolve the issue and improve your Excel performance.
Error overview: “Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.”
Some error messages you get when working with Microsoft Excel are memory-related. The error addressed in this article is one of them.
When you get this error message, Excel tells you it has run out of processing power or capacity to complete the requested task. As powerful a tool as Excel might be, much of its work depends on the capacity of your computer.
Because of this, when the workload requires more processing capacity than your computer provides, you will get the error message, “Excel ran out of resources while attempting to calculate one or more formulas.” This error usually occurs when your workbook contains complex formulas, large datasets, or other resource-intensive features such as macros, pivot tables, or charts.
In the next section, we will take a look at ways in which you can resolve this error.
How to Fix the “Excel Ran Out of Resources While Attempting to Calculate” Error
Here are some ways in which you can fix the error message:
1. Exit other running applications
The effect of having multiple workbooks open on system memory and performance is similar to having other applications running, especially when they’re also resource intensive. As such, it is important to close any unnecessary applications or programs running on your computer to increase the amount of memory available for Excel to work optimally and efficiently without throwing the “Excel ran out of resources” error message at you.
In addition to closing any unnecessary applications or programs, you can also try disabling any background processes or services that are not required for your work in Excel. This can further free up system resources and improve the performance of Excel.
To close background processes or services that are unnecessary, use the Task Manager by following these steps:
Step 1. Right-click on your taskbar and select Task Manager from the options.
Step 2. Select any app from the Apps or Background processes section and click the End task button in the bottom right corner.
You can repeat this step until you have closed all unnecessary apps running in the background and have freed up more memory for Excel to work with.
2. Simplify complex formulas
Although Excel is perfectly capable of processing complex formulas, it can be resource-intensive, which can cause the program to run out of memory. When the system runs out of memory, you get the “Excel ran out of resources while attempting to calculate” error message.
Suppose the source of the error message is due to the number of complex formulas in your workbook. Consider breaking down the complex formulas into smaller, simpler parts or using helper cells to perform intermediate calculations.
A technique that you can use for simplifying complex formulas is to use helper cells. By performing intermediate calculations in separate cells, you can reduce the complexity of the overall formula and make it easier to read and understand. In addition to making formulas more understandable, this approach can make your formulas easier to update, modify, and troubleshoot.
By dividing a complex formula into several smaller ones, you can simplify the calculation and make it easier to follow. When your workbook contains fewer complex formulas, it reduces the chances that you will get the error message.
If you must use a complex calculation method, you should consider using Excel’s built-in functions. Excel offers a wide range of functions that can perform complex calculations with minimal effort. Using these functions instead of complex formulas simplifies your calculations and reduces the risk of errors or memory issues.
3. Upgrade Excel to the 64-bit version
A 64-bit version of Excel can handle larger datasets and consume more memory than a 32-bit version of Excel, which can help prevent the “Excel ran out of resources” error. If you currently use a 32-bit version of Excel and work with large datasets, upgrading to a 64-bit version of Excel can be a good option.
The main benefit of a 64-bit version of Excel is that it can address more memory than a 32-bit version. A 32-bit version of Excel can only address up to 2 GB of memory, while a 64-bit version of Excel can address up to 8 TB of memory. This means that a 64-bit version of Excel can handle larger datasets and perform more complex calculations without running out of resources.
However, not all systems support the 64-bit version of Excel. To check if your computer can support a 64-bit version of Excel, follow these steps:
Step 1. Go to your computer’s settings and click on System.
Step 2. Scroll down to the bottom and click About. Check the System type.
If your system runs a 64-bit version of Windows, you can install the 64-bit version of Excel.
When you work with large datasets and frequently encounter the “Excel ran out of resources while attempting to calculate” error, upgrading to a 64-bit version of Excel will be a good option.
4. Save and close other workbooks
The more workbooks you open, the more memory you consume. And if the open workbooks have large datasets, they will consume a significant number of resources. To optimize your use of Excel and reduce the number of resources it consumes, it is recommended that you save your workbook regularly and close all other open workbooks.
When you open multiple workbooks, the amount of data Excel needs to process increases significantly. This can result in the “Excel ran out of resources while attempting to calculate” error message. And that’s not all. You can experience slow performance and sometimes crashes due to having multiple Excel workbooks open.
By closing all other open workbooks, you can reduce the amount of data Excel needs to handle at any given time. It also frees up system resources and can improve the overall performance of Excel.
5. Reduce the number of datasets in the workbook
Another reason you may receive the “Excel ran out of resources while attempting to calculate” error message is the use of large datasets in Excel. Working with large datasets usually demands more resources which can cause the program to run out of memory.
To optimize your workbook and prevent these issues, reduce the amount of data in your workbook by filtering or sorting your data or deleting any unnecessary rows or columns.
When you filter or sort your dataset, you get to focus on the most relevant information and reduce the amount of data that Excel needs to process. By filtering your data, you can display only the rows that meet specific criteria, making it easier to work with a smaller dataset. The same thing applies to sorting your data, where you can rearrange it to display only the most relevant information, thereby reducing the overall amount of data that needs to be processed.
Another way you can reduce the datasets in a workbook is to delete any unnecessary rows or columns. By doing this, you remove any redundant or irrelevant data and reduce the overall size of your workbook. You can also remove blank rows or columns while you’re at it.
Using any of these methods can help you reduce the amount of data in your workbook and the amount of memory needed by Excel to process it. In the end, this should prevent the “Excel ran out of resources while attempting to calculate” error message from popping up on your screen.
6. Specify the number of processors Excel should use for processing data
This may seem counterintuitive, but reducing the number of processors used by Excel can be a potential solution to solving the “Excel ran out of resources while attempting to calculate” error message.
By default, Excel is set to use all available processors. For the most part, this helps Excel handle large datasets as efficiently as possible. However, as the workbook becomes more complex, utilizing all available processors can cause the “Excel ran out of resources while attempting to calculate” error.
When you limit the number of processors Excel uses, you reduce the number of system resources that Excel consumes. This can help prevent the error message, system crashes, and freezing caused by the system running out of resources.
To limit the number of processors Excel uses, follow these steps:
Step 1. Click on the File ribbon and select Options.
Step 2. Click on Advanced.
Step 3. Scroll down to the Formulas section and select the Manual radio button. Enter 2 in the textbox.
Step 4. Click OK to save the changes.
Alternatively, you can do this with the Task Manager. After opening the Task Manager by right-clicking on the taskbar, follow these steps:
Step 1. Go to the Details bar and scroll up until you find EXCEL.EXE. Right-click on it and select the Set affinity option.
Step 2. Uncheck the processors you don’t want Excel to use and click OK.
By reducing the amount of resources Excel uses, you can ensure that the system has enough resources to support Excel and other programs.
7. Use pivot tables and charts only if necessary
Pivot tables and charts are also resource intensive, especially when they contain lots of data or are highly complex. And while sometimes there’s little you can do to resolve issues relating to large datasets and complex formulas, you have a bit of leverage with pivot tables and charts.
If you have pivot tables or charts displaying lots of data that is also complex, you can get the “Excel ran out of resources while attempting to calculate” error message. As such, preventing this error message will require you to use pivot tables and charts sparingly or use simpler charts and tables instead.
You can optimize pivot tables and charts by reducing the amount of data included in the table or chart. Also, you can filter or summarize your data so it contains only the most relevant information and reduces the amount of data that needs processing.
8. Increase system memory
You may need to increase your computer’s memory or processing power to prevent Excel from running out of resources. This can be done by adding more RAM or upgrading your processor.
Adding more RAM will help your computer handle more extensive datasets and perform more complex calculations without slowing down. RAM is used to store data currently being used by the computer, so adding more RAM can allow the computer to store more data and access it more quickly.
To add more RAM, you must check your computer’s specifications to see what type of RAM it uses and how much it can support, then purchase and install additional RAM.
9. Fix incorrect formulas
Incorrect formulas can be a source of the “Excel ran out of resources while attempting to calculate” error. Wrong formulas that, for instance, refer to many cells or ranges, use complex or nested functions, include circular references, or cause iterative calculations are usually resource intensive and cause Excel to use more memory and processing power than necessary.
To prevent incorrect formulas from causing the “Excel ran out of resources while attempting to calculate” error, it’s vital to ensure that your formulas are optimized and efficient.
Fixing incorrect formulas can be daunting, especially when they are complex. However, you can use the error-checking feature in Excel to ease the process.
Follow these steps to trace errors in a formula:
Step 1. Select the cell showing the error code.
Step 2. Go to the Formulas ribbon. Click on Evaluate Formula.
Step 3. In the Evaluate Formula window, click on Evaluate. This will take you through a step-by-step process of the formula execution which you can use to identify where the error lies.
Alternatively, you can use the Trace Error option.
This will use arrows to show you the cells that depend on and precede the formula so you can easily trace where the error occurred.
To remove the arrows when you’re done, click on Remove Arrows.
Using either one of these methods, you can quickly identify and correct faulty formulas to solve the “Excel ran out of resources while attempting to calculate” error.
FAQs
Why are none of my formulas working in Excel?
Your formulas may not be working for several reasons, including incorrect syntax, data type issues, issues with add-ins, missing data references, or corrupt workbooks or installations.
Why does Excel keep saying there’s a problem with this formula?
You are getting this error message because of a syntax error, missing cell or range references, circular references, or compatibility issues. To fix this, you should review the formula using the Evaluate Formula or Trace Error options in the Formulas ribbon.
