Strange calculation behavior

[mccdaddy]

Hi. I have Excel 2019. A couple of months ago I opened one of my sheets to update some personal accounting numbers. When I entered the new figures the related cells didn't update. I had never seen that before so I did a Google search for a solution to the problem. I found out for the first time about Formulas/ Calculation Options. Mine, for some reason, were set to Manual. I changed the option to Automatic and all was well. Then, today, the same thing happened. Every sheet I opened had the option changed to Manual. What could possibly be causing this peculiar behavior?

mccd

 

[RoryA]

The calculation state is determined by the first workbook opened in a given session of Excel. If a workbook was saved with manual calculation on, and it is the first workbook opened, it will set calculation to manual for that session (until you change it).

 

[Kerryx]

As what RoryA said but found this which may help
If you open an Excel file and set the calculation mode to Automatic, then open another Excel file that has been saved in Manual mode, it will NOT change to Automatic mode. Conversely, if you open an Excel file that has been saved in Manual mode, then open another Excel file saved in Automatic mode, it will CHANGE to Manual mode.

As a workaround, you can create a Personal.xlsb on: UserName\AppData\Roaming\microsoft\excel\Xlstart folder

When you saved the file "Personal.xlsb" in the Xlstart Folder, open an Excel file and press ALT+F11

On the left side, open the VBAProject(Personal.xlsb) and double click on ThisWorkbook and paste this code:

Private Sub Workbook_Open()
     Application.Calculation = xlCalculationAutomatic
 End Sub

MicrosoftCredit to :

 

[jkpieterse]

Do you have any Excel add-in installed? Sometimes the author of the add-in saved it in manual calc mode, potentially causing this behavior for his users.

 

[RoryA]

If you open an Excel file and set the calculation mode to Automatic, then open another Excel file that has been saved in Manual mode, it will NOT change to Automatic mode

It will, unless there is something wrong with your installation. (if it didn't, your Personal.xlsb option wouldn't achieve anything)

 

[mccdaddy]

Thank you all for the replies and workarounds. However, my real question is how does the calculation setting change by itself? I do have one add-on, ExtendOffice's Office Tab, but I've had that for years. Why would the setting all of a sudden change when I've never set it to Manual? And then, once I've corrected it, why would it change again, months later? I'm puzzled.

mccd

 

[RoryA]

As I said, if you opened a workbook first that was saved with calculation set to manual, it would set calculation to manual for that session of Excel.

 

[jkpieterse]

All it takes is one workbook set to manual getting opened first and some other workbook second. Save both and you're in for a disease that spreads itself. AFAIK, with recent builds of Excel the Calc state is no longer shaved upon save. But I haven't tested this yet