Excel cell have issue, yet shows "invalid" data



  • Ran into an issue I've seen once or twice in the past 25+ years.

    A user has an Excel spreadsheet that uses tons of non complex formulas. When they looked at the result shown in a cell (m38), visually it appears normal and correct.

    The user ran into an issue when they tried to add this cell (m38) to the value in another cell, the result was 0.00(cells formatted as such).

    It turned out upon clicking directly on cell m38, that the cell had an error. Unfortunately I was unable to retrieve the actual error that Excel was giving, once the user saw it, they cleared it out, recreated the the formula for that cell, and all was fine. But the weird thing is - Excel previous to the user clicking in the cell, showed the expected value for the formula. Excel was acting like the value had been previously cached, then something that the formula relied upon had changed, and perhaps changed back, but the change had caused the underlying logic to fail.

    Even weirder, this situation survived the xlsx being saved, the file being closed, and then opened again several hours later. In case it's helpful to know, it's unlikely that Excel it self was closed during this time.

    Anyone seen this before?



  • There is a setting in excel to not update Formulas all the time



  • 0_1515590925239_EXCEL_2018-01-10_08-28-22.png

    Make sure the highlighted settings are set to automatic and then the formula will update automatically.



  • The system was set to automatic.


Log in to reply