Forum Discussion
Excel ran out of resources while attempting to calculate one or more formulas.
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?
3 Replies
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.
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.
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
