Forum Discussion

LukeFM's avatar
LukeFM
Copper Contributor
Nov 05, 2022

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.

 

 

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

  • 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.

  • 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.)

    • LukeFM's avatar
      LukeFM
      Copper Contributor

      HansVogelaar 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

  • makapu's avatar
    makapu
    Brass Contributor

    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.

    • LukeFM's avatar
      LukeFM
      Copper Contributor
      This seems to have fixed it 🙂

      Thank you very much!!!
    • JonathanCurro's avatar
      JonathanCurro
      Copper Contributor

      makapuShould anyone be chasing a solution not requiring uninstallation of Excel updates, the following worked for me:

       

      Click File - Info - Check for Issues - Check Compatibility. Then the this error information was displayed:

      I clicked Find which took me straight to a bogus formula which was =+D54:CBC351424 in case anyone is interested. I deleted that cell's contents and was immediately able to work in the spreadsheet once more.

       

      Hope this helps anyone searching for a solution as I was this morning.

      • CBaney's avatar
        CBaney
        Copper Contributor
        That is what my problem was. Thankful for this posting so I could get on with my work!
    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor

      makapu  wrote:  ``looks like the Bug for Excel 2016 version 2210 (Build 15726.20174).
      So revert to Version 2209 (Build 15629.20208)``

       

      nic-fleetwood  wrote (Dec 3):  ``Reverting Excel absolutely worked. [....] FYI, my build was a later build than the one mentioned before reversion``

       

       

      This is good to know. 

       

      According to the cited update history, version ``2209 (Build 15629.20208)`` was released on 11 Oct 2022.

       

      Any idea if this also affects Office 365 Excel on or about the same date?  Excel 2019?  Mac versions?

       

      We have seen this complaint often in other forums, and not just for Excel 2016, IIRC  We waste a lot of time trying to suss out details about the workbook, like excessive whole-column references in SUMPRODUCT formulas, etc.

       

      makapu's response is a keeper.

      • makapu's avatar
        makapu
        Brass Contributor

        This problem may occur in the updated version of Excel. In this case, I suggest that you can click feedback in the Excel application to directly report this problem to the relevant Excel teams, which can quickly make the comments attract the attention of developers.

        You can open Excel and click File>Feedback>click I have a suggestion and write this query to feedback it to the Excel team.

         

        How do I give feedback on Microsoft 365? - Microsoft Support

         

         

  • nic-fleetwood's avatar
    nic-fleetwood
    Copper Contributor

    LukeFM Reverting Excel absolutely worked. Pernille-Eskebo please fix as I have lots of macros which place complex formulas, and this messes me up big time. Also, FYI, my build was a later build than the one mentioned before reversion.

  • Mike_Rowdy's avatar
    Mike_Rowdy
    Copper Contributor

    LukeFM Try looking for a crazy looking formula that was made by mistake. For instance, this formula by itself in a workbook will cause it to run out of resources. I had this in someone's Excel workbook that I had to look at today. I would just save some copies and delete sections until you find it. 

     

    =+G152:RG141152:S152:QG152

  • hillbillyfairy's avatar
    hillbillyfairy
    Copper Contributor

    LukeFM This is happening to me too! I have NO FORMULAS in my worksheet, it's simply a list of patients who need their Well Child exam. Unless dates are considered formulas??? I have restarted the computer, copied the worksheet to a new file, used search... go to special to find formulas that I didn't know about (it found none). Now, this is a very important worksheet! We want our child patients to get their well child exams! Can you help? I have an HP laptop (17-by3xxx is what I find when I attempt to find out what it's called when I choose "About" from the settings menu) with Windows 10 installed (build 19045.3208) and I use Excel 365 and I just checked that I have the latest version. HELP!

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      hillbillyfairy 

      Formula could be like =G152:RG141152:S152:QG152 as in one of previous post. I'd check all cells which start from =

      Ctrl+F is the simplest way.

      • hillbillyfairy's avatar
        hillbillyfairy
        Copper Contributor

        SergeiBaklan I searched for formulas using the "go to special..." reports NO FORMULAS. For some reason today it seems fine. Which makes no sense at all, because after doing a reboot yesterday and only opening Excel+file it acted up. Now I'm sitting here wondering what's going on, and when it's going to pop up again.

  • Ali221905's avatar
    Ali221905
    Copper Contributor

    @This has happened to me twice now.

    I'm just putting the answer here for anyone searching for answers in the future.

    There will be an error in one of the cells that are in the data range, it can take a while to  find it as it's not always very visible.

    Our error was that someone inputting must have knocked some keys and in one of the cells they had accidentally entered '=+AX:VW', it came up as 0 which was why it took me a bit to find it.

    Usually I just highlight all the cells in the range and just press delete but in this case I needed the data so it took me a bit to fine it.

    Going to the toolbar and clicking on 'Formulas' and the 'Error Checking' function is helpful as well.

  • shababx's avatar
    shababx
    Copper Contributor

    LukeFM Upload the excel file to your Google Sheet, then download it! that should solve the problem, worked for me 🙂

    Hope it helps

  • jlauycy's avatar
    jlauycy
    Copper Contributor

    LukeFM In my case the currency formula caused the problem, I tried to change the currency to another currency instead of my country's currency, and then the problem was gone. My formula is: 

     

     

    STOCKHISTORY(AG1,$AE$1,$AE$2,1,1,0,2)

     

     

     I used a cell AG1 to reference my currency. Hope this helps. The problem exists even though there is no precedent or dependent cell. I think maybe I changed my Windows 11 language for non-Unicode programs setting, it was using another country setting, I changed it to my country setting yesterday. I changed it back to the original one but still got the problem.

  • ryancello7's avatar
    ryancello7
    Copper Contributor
    For me, it was the FRED Excel Add-in. I unchecked the add-in, saved the workbook, closed it out. The opened the workbook again. Issue resolved for me. Hope this helps others.

Resources