Excel 2016 not updating automatically even though automatic set in calculation mode

Question

I set calculation mode to automatic but when I enter "=Now()" inside a cell I do not see the time updating even when formatting is changed to show seconds. That worked in prior Excel versions. Is there anything fundamental that changed?

Solution

The assumption is not correct, it never updated automatically, in no Excel version.

It is not really possible, as it would mean that Excel would continuously update, using 100% of the CPU and disallowing any useful user interaction.

It does update when something else (calculation relevant) is changed on the sheet, and a recalculation is triggered that way. Changing a formatting is not an event that needs a recalculation, so that is not enough; but for example typing anything in any cell will trigger a recalculation.

Comments

[Matt]

I clearly stated I use Excel 2016, I even included a screen shot showing I set my calculation mode to "Automatic", downvoted.

[forgetaboutme]

sorry for that, I edited my answer, it is the same for Excel 2016.

[Matt]

You may misunderstand my whole question. The "Now()" function returns the current date and time. My problem is that even in Automatic calculation mode the cell does not automatically update which it should. Even setting the cell format to General does not change that. When I press F9 it does update the cell value but I need automatic updating not manual updating.

[Matt]

Yes it updates when I push F9, and in all previous versions it updated automatically when setting Workbook Calculation to "Automatic". Mine does not.

[forgetaboutme]

If you press F9, it should update. Otherwise, check out this link

[Matt]

I need automatic updating, not manual updating.

[Matt]

You are saying there was never a way to have Excel recalculate automatically on cells' changed values without user intervention? I disagree. I did have it in Excel 2013 and it worked perfectly well. And yes, it did use up lots of resources but it did exist. Lots of things apparently changed in Excel 2016. Same with RTD server functionality, Microsoft had to post several fixes for that alone.

[Aganju]

Feel free to disagree. i have explained why it is not possible to have it that way; but you are free to believe it anyway.

[Matt]

Then why did my RTD server push data on a timer callback before but now nothing updates? And by the way I never even hinted at wanting to update upon changing a formatting. I need updating on changing values

[Aganju]

the post says: "...even when formatting is changed to show seconds...". When a value is changed, the display of a cell with NOW() does change (assuming the seconds or whatever changes is visible). If that is what you refer to, it was not clear at all. This works in Excel 2010 and 2013 (just verified); I don't have 2016, can't check.

[Matt]

I only wrote this to avoid some people suggesting that I just don't see the change because Now() by default only displays the hour and minute portion of time. I simply wanted to indicate that I see the second portion as well. My problem remains that without a user manually pressing F9 or the like no automatic updates occur, neither on timers, nor through a RTD server whose updates are invoked through a timer callback and "UpdateValue". Nothing in my spreadsheet changes at all.

[Matt]

I think I found the culprit. I marked the UDF I am using as volatile [ExcelFunction(IsVolatile = true)] and now the timer callback via RTD automatically updates the worksheet without any manual user intervention.