SOLVED

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

Occasional Contributor

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

 

 

Hi Everyone,

 

I'm getting the above error message when opening or doing any sort of calculation on any file (even opening a blank file and typing in =2+2).

 

Steps I have taken:

1. Restart PC

2. Reinstall Excel/Office

3. Turned off multi-threading in Options / Advanced / Formulas

4. Gone down to 1 processor in Options / Advanced / Formulas

 

A support agent from Microsoft also spent about half an hour playing around remotely, seemed to do the tests above and a few others which I can't recall but wasn't able to resolve. They sent me here.

 

Files which have the issue on my laptop, I've emailed to another PC and they're working fine. Laptop I'm using has an i7-1165G7 @ 2.80GHz 1.69 GHz 8.00GB RAM

 

Any suggestions?

 

Many thanks

Luke

9 Replies

@LukeFM 

Since this happens in any workbook: do you have any add-ins installed? If so, try disabling all of them in File > Options > Add-ins. If the problem goes away, one of the add-ins must be the culprit. By enabling them one by one and testing after each step you should be able to find the culprit.

 

Something else to try:

Make sure Excel is not active.

Hold down the left Ctrl key while you start Excel. You should see a dialog asking whether you want to start Excel in Safe Mode. Answer Yes.

Does the problem persist?

(All your personal settings will be missing, but don't worry. They'll be back the next time you start Excel the normal way.)

@Hans Vogelaar thanks for the response, really appreciate it!

 

I believe all Add-ins are already disabled - all the boxes are unticked when I go to manage on the Add-ins screen.

 

Safe mode didn't work either

 

Any other ideas...?

 

 

@LukeFM 

In Windows Explorer / File Explorer, type the following in the address bar, then press Enter:

 

%appdata%\Microsoft\Excel

 

You should see a file named Excel15.xlb

Make sure that Excel is not active, and rename this file to Excel15.old

Then start Excel to see if the problem is gone.

If it remains, you can quit Excel and rename the file back to Excel15.xlb

I did that, no luck. Have now renamed it back to Excel15.xlb.

Thanks again for your help, if you have any other suggestions then I'm all ears!

@LukeFM 

I am really sorry, but I'm out of ideas...

No worries. Appreciate your efforts!

I think I have found something of a workaround - if I turn calculations to manual, I can at least work without the dialog box popping up every time I hit enter / leave a cell. Would I be correct in saying that I can do this, then occasionally turn it back on to automatic, and when I hit enter on a formula again the whole workbook will update as if nothing was wrong?

Btw I've already error-checked all the formulae within the workbook and there are no circular references.

@LukeFM 

You can try that, and see if the error message pops up when you enable calculations.

best response confirmed by LukeFM (Occasional Contributor)
Solution

@LukeFM 

Hi, 
This issue looks like the Bug for Excel 2016 version 2210 (Build 15726.20174).
So revert to Version 2209 (Build 15629.20208) and disable Office updates.

1. Start an Office application (such as Excel), and then select File > Account. select Update Options > Disable Updates.
2. Type cmd on the start menu, right click on the Command Prompt, choose Run as Administrator
3. Copy/paste these two commands one at a time and press enter
cd %programfiles%\Common Files\Microsoft Shared\ClickToRun
officec2rclient.exe /update user updatetoversion=16.0.15629.20208

FYI:

Update history for Office 2016 C2R and Office 2019 
How to revert to an earlier version of Office

 

--------------------------------------------------

*If you use MSI versions of Office, delete windows update program.

This seems to have fixed it

Thank you very much!!!