New Contributor

I keep getting this message when
working on one of my worksheets
( excel ran out of resources while attempting
to calculate one or more formula,
as a result, this formula cannot be evaluated )
Excel cannot calculate a formula.
There is a circular reference in an open workbook
but the refernce that cause it cannotbe listed for you.
Try editing the last formula you entered
or removing it with the undo command

6 Replies


You probably use many whole column matrix formulas


Little details.


Your specified matrix formula could be reduced to: {= MAX ((Table2! O: O = B3) * Table2! F: F)}

Test this with = MAX ((Table2! O1: O40000 = B3) * Table2! F1: F40000)


Instead of including the whole column in the formula, add the range.

Instead of (as above) Table2! O: O

Test (as above) Table2! O1: O40000


Here is an optimization for the calculation time:

if the data source (table1 $ A: $ E) contains a lot of data, then

Table1 $ A: Sort $ E according to column A in ascending order on the whole formula.


Additional Infos: How to troubleshoot "available resources" errors in Excel


I would be happy to know if I could help.



I know I don't know anything (Socrates)


* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.


Thank you Nikolino for your reply, I really do not understand your reply. All I would like to know is how to stop this popping up on my spread sheet, 

I was facing a similar problem where there were a few array formulas down about 150.
The reason is most likely that when the computer tries to process the large amounts of data, it uses multithreaded calculations. When multi-threaded calculation is turned to off, the computer doesn't throw the 'Excel ran out of resources...' error.
Thus to turn off multi-threaded calculation, follow the steps below:
• Navigate to the 'File' tab in your Excel sheet and select “Options”.
• On the right side select “Advanced” and scroll down to the heading “Formulas”.
• You will see a check box that says Enable multi-threaded calculation. Untick it, then select 'OK' and recalculate your formulas.

I hope this helps!

Other than this, if you face any kind of .xls or .xlsx file corruption in Excel you can check out this reference []

best response confirmed by Maurice_Patton (New Contributor)
That is great , It has solved my problem thank you for your help
With the existing Aray formulas in your file, instead of searching through the entire column, you could restrict them to a more restricted area with the formula.
This means that it does not have to calculate all cells in the column (s).
The amount of data is decreasing.


Thank you I have fixed the problem