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

[cornie_anghotmailcom]

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

How to find the cells which contain error?

[#2]

@cornie_anghotmailcom 

Without opening your file

How to troubleshoot "available resources" errors in Excel

Follow the provided methods in this article to resolve the available resource error message in Excel.

If it is not solved with the link / information above, then here with Inquire error analysis

Turn on the Inquire add-in

Start the workbook analysis

[#3]

@cornie_anghotmailcom 

It takes years to open your file, on my more or less modern computers its stays on

consuming all memory.

What I'd suggest if you are able to open your own file

- make a copy of it

- delete sheets one by one, till Out of resource alert disappears. Doesn't matter if you have errors in calculations

- return back to previous step with error and try to find wrong array formula (most probably that's the reason)

- if your Excel version supports Inquiry it helps to localize the formula.

Right now I waited about 15 minutes and still on about 30% of calculations. Sorry, the task is quite time consuming.

If I have more time will try again.

[#4]

@cornie_anghotmailcom 

The style reduction tool (third-party) tells that this file has no less than 53016 slow calculations, which are for the most part whole-column references in formulas.

Excel seems to pre-allocate memory for those columns when calculating formulas, and since you are doing it with a XLSX file, it's 1 million rows that are pre-allocated for each of those whole-column references.

Converting this XLSX file back to a XLS file fixes the problem you are having, because then each column is made of 65536 rows, which means a lot less. Excel still pre-allocates a lot of unused rows but at least several orders of magnitude less.

Also, for the record, you can also remove those whole-column references to help Excel as well. For instance if the sheet being referred to with a column-wide reference such as E:E has say 500 actual rows of data, you could replace E:E in formulas with E1:E500. Or perhaps E1:E1000 if you'd like to let the data grow without having to update the formulas for some time.

Hope it helps.