Auto Calculate

Question

Seems like a bug!

Bottom left corner of an open spreadsheet there's a "Calculate" button. When hovered over the popup says Excel is not set to calculate automatically & gives the option to do it manually.

However, In settings Excel IS set to auto calculate, & has been all along.

Has Microsoft got a fix for this?

Answer 1

TCMann wrote previously:

Bottom left corner of an open spreadsheet there's a "Calculate" button. When hovered over the popup says Excel is not set to calculate automatically & gives the option to do it manually.

The hover pop-up message is misleading.

I get the same pop-up in Excel 2010 (under the conditions described below), even though Automatic is set.

The problem is:  your screenshot shows that Iterative Calculation is enabled.

And when Max Iterations is reached, recalculations are aborted, and the workbook is left in Calculation mode.

This is easy to duplicate.  In a new workbook, set Automatic Calculation and Iterative Calculation, and set Max Iterations to 4.

Then in A1, enter the formula =IF(A1="", 1, "").  Obviously, the formula will ping-pong between the null string and 1 ad nauseum.

Note that the Calculation indicator remains in the lower-left status bar.  And the pop-up reads as you described, namely:

Remedy:  Deselect Iterative Calculation mode, and eliminate the circular calculations.

If the circular calculations are on purpose, change your design to avoid them.

If you have trouble with the redesign, post a __new__ thread to ask for help.  Be sure to include a link to an uploaded example Excel file that demonstrates the problem.

IMHO, purposeful circular calculations are "evil".  Often, they are unreliable.  But more importantly, setting Iterative Calculation mode risks overlooking unintentional circular references that go undetected.

Answer 2

Hi TCMann!

I'm Jen, an independent advisor and a Microsoft user like you. I'd be happy to help you out with this issue.

Does this issue occur only to a specific file or to all files with formulas in general?

Meanwhile, you may try repairing your office installation and see if it helps resolve the issue. You may follow the instructions here.

https://support.microsoft.com/en-us/office/repa...

Let me know if this works. If you have any questions, please let me know and I'd be glad to assist you further.

Thank you and Best Regards!

Jen :)

Answer 3

Hi Jen.

Firstly it seems to be just 1 file. I don't have many workbooks but this is the 1 I use almost daily. I can't say when it first occurred but I only noticed it today.

I've tried both the quick repair & the online version. No difference.

What puzzles me is the message; it says Excel is not set to calculate automatically, implying the program not the file itself.

Also, when opened todays date & time are both displayed correctly, i.e. up to date, & when figures are changed totals recalculate as expected, so some calculations must be happening automatically.

It's a weird message.

Answer 4

May I ask, when do you get this message - "Excel is not set to calculate automatically" ? When opening the excel file or whenever you make any changes in the workbook?

Answer 5

Hi again Jen.

Might I refer you back to my original post?

When opening the file the "Calculate" button is immediately visible, it's when the mouse is hovered over that the message appears, kind of like a help tip.

I haven't tried it but if in Settings/Formulas "workbook calculation" is set to Manual then I imagine the "Calculate" button would appear on every file opened. Seems logical.

In my case it only appears on one file, even though Workbook Calculation is set to Automatic (a setting which should be common across Excel).

That's what makes me think it's a bug of some kind.