Excel ran out of resources while attempting to calculate one or more formulas Fix

Many users have reported seeing the below error when they are using complex formulas in their worksheets.

Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be calculated

Potential Causes for this error are :

• Hyperthreading
• Formulas that have circular references

If you are seeing this error, don’t worry, we have compiled a list of fixes that will help you solve the Excel ran out of resources issue.

Fix 1: In formula include only cells from same rows

Try to include cells from the same row in which you are creating the formula.

Fix 2: Limit the number of processors to be used

Method 1: Using Excel

Step 1: Open the Excel File that is throwing the error.

Step 2: Click on the File from the top menu

 

 

 

Step 3: From the left-hand side, click on More

Step 4: Then choose Options

 

 

 

Step 5: In the Excel Options window, Click on the Advanced tab from the left-hand side.

Step 6: At the right-hand side, scroll down to locate the Formulas section.

Step 7: Tick on the Manual option

Step 8: Enter a number lesser than the number of processors in the system. For example, as we can see from the image, there are 4 processors in the system. So, we can try entering the values 1 and check if the issue resolves. If not setting 2 and check if the error resolves and finally enter 3 and try if the error resolves.

Step 9: Finally, click on the Ok button.

 

 

In case this doesn’t help, try the next fix listed below.

Method 2: From Task Manager

Step 1: Right-click on your Taskbar

Step 2: From the context menu, choose Task Manager

 

 

Step 3: Go to the Details tab

Step 4: Scroll down and locate EXCEL.EXE

Step 5: Right-click on EXCEL.EXE

Step 6: Choose Set Affinity

 

 

Step 7: Now, tick on the Processors you need to have

 

 

Step 8: Click on OK

Fix 3: Correct the Wrong Formulas

Correcting a wrong formula requires in-depth knowledge. And evidently, one solution cannot fit all cases.

Trace the Error & Check if it is ending up in an infinite loop

Step 1: If you are not sure where exactly the error is seen, click on Formulas from the top menu

Step 2: Click on the arrow mark next to Error Checking

Step 3: Select Trace Error

Step 4: You will see the problematic cell highlighted

 

 

Step 5: Now, check the logic of the formula and see if it is triggering an infinite loop.

Write Effective Formulas

Many users complain that they see errors even when the file doesn’t have much data. Note that, CALCULATION(A1:A100000) will take a significant amount of memory even if only A1:A10 has values. So, keep the range small.

Fix 4: Free up some Memory in Excel

Following two tips have helped a lot of users :

• Delete the necessary data or Sheets from your Excel Worksheet
• Change all the formulas into Values. Refer to Changing formulas to values

Fix 5: Run excel compatibility mode

Step 1: Open the Excel and click on File from the top menu

Step 2: Choose Info 

Step 3: Click on the Downward arrow in the Check for Issues tile

Step 4: Click on Check Accessibility

 

 

Step 5: Select the required version

Step 6: Finally, click on the Ok button.

 

 

That’s All.