Excel doesn't calculate automatically even if it is set to automatic mode

Question

Hello,

As the title says, Excel doesn't calculate automatically even if it is set to automatic mode. To be more precise, it happens when I am dragging down a formula or clicking on the down right corner of a cell to spread the formula to all lines. It does not happen if I am working on a single cell.

What is even weirder is that even if in the Formulas tab, the Calculation Options is set to "Automatic", when I go in the Options -> Formulas, it is set to Manual. I can click on Automatic but it doesn't change anything as it will revert back to Manual as soon as I close the Excel Options.

Even if I miraculously manage to have a file set on Automatic (by copying all the sheets to a new file that I managed to put on Automatic), as soon as I start working on the sheets, it reverts back to Manual in the Excel Options.

If I type something in any empty cell and press enter, then everything calculates as it should, and F9 works as well.

My Excel is up to date (Microsoft® Excel® for Microsoft 365 MSO (Version 2410 Build 16.0.18129.20158) 64-bit)

There are no Add-in activated

I don't seem to have a "personal.xlsb"

That's driving me crazy, help!

Answer 1

Are you selected mutiple sheets? If you selected mutiple sheets, the caclulation option will be set to manually automatically.

If not, how can I reproduce your issue?

Answer 2

I am indeed working on multiple sheets at a time. Is there no way to bypass that?
I have been doing the same kind of work for 5-6 years now and only noticed this some months ago. Is that "new"? (I don't know maybe a year max?)

If not, oh well... maybe Excel is not driving me crazy because I am already, not noticing that for years.

Answer 3

This is because more and more functions are added to Excel. If you are working on mutiple sheet, some calculation may cause bugs. So Microsoft stop it.

You can still edit it when you select mutiple sheets and run calculation when you select only one.