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 :
- 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.
Anusha Pai is a Software Engineer having a long experience in IT industry and having a passion to write.