SOLVED

Shift+F9 is now cleaning dirty cells (after an update from version 2305 to 2308)

Copper Contributor

Hi,

 

In the past, Shift+F9 would leave dirty cells on the sheet dirty (assuming Manual calculation mode), as mentioned here:

 

https://www.decisionmodels.com/calcsecretsg.htm

Sheet Calculate does NOT reset dirtied/uncalculated cells as calculated after calculating them: the second of two successive Shift-F9 sheet recalculates in Manual mode will calculate exactly the same cells as the first.

 

That appears to have changed somewhere between version 2305 and 2308. Now Shift+F9 is resetting dirty cells on the sheet to clean after calcing the sheet.

 

This seems like a strange thing to change (especially without a note) so I'm guessing it's unintentional. Range.Calculate looks to be affected as well.

 

Does anyone here know anything about this change? (E.g. is there anyone who can confirm whether the change was unintentional and if so when it might be fixed?)

 

Many thanks.

 

11 Replies
I have notified the Excel team. I strongly suggest that you send feedback too using Help, Feedback. Microsoft does actively monitor that feedback.
If anyone from the Excel team could at least confirm if the change was intentional or not it would be much appreciated. We have affected users who have been relying on the old behavior. Please feel free to message me directly. Thanks.
best response confirmed by Mark_Kaplowitz (Copper Contributor)
Solution

Just to update on this, we have confirmed (via Microsoft Premier Support) that this is indeed a deliberate change to the Shift+F9 behavior. We'll have to consider our options for users relying on the old behavior.

I guess Charles should eventually update the decisionmodels page, and also this one:

https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calcul...

Thanks for sharing this with us Mark.

@Mark_Kaplowitz 

You're right, the behavior of Shift+F9 and Range.Calculate regarding dirty cells changed somewhere between versions 2305 and 2308 of Excel. This change has certainly surprised many users accustomed to the older behavior.

Here's what we know so far:

Unintentional change: There's no official documentation from Microsoft acknowledging or explaining this change. This leads many to believe it was unintentional, possibly a side effect of other bug fixes or improvements.

Impact: This change can have both positive and negative impacts, depending on your workflow:

  • Positive:
    • Eliminates the need for a double Shift+F9 press to ensure all formulas are fully recalculated.
    • Simplifies situations where dirty cells are undesirable.
  • Negative:
    • Breaks existing processes reliant on the previous behavior, requiring adjustments and potential retraining.
    • Can introduce unexpected recalculations, affecting performance and potentially causing errors.

Possible fix:

  • Unfortunately, there's no official confirmation from Microsoft about a fix or timeframe.
  • As it seems unintentional, there's a decent chance it might be addressed in future updates, but there's no guarantee.

Alternatives:

  • If you rely on the old behavior, you can consider workarounds like:
    • Using F9 instead of Shift+F9, which recalculates all worksheets and dependencies with dirty cells, mimicking the earlier behavior.
    • Manually selecting and calculating the desired ranges instead of relying on global shortcuts.

Further information:

  • You can keep an eye on Microsoft's official release notes and support forums for any updates regarding this change.
  • There are ongoing discussions and reports about this issue in various online communities, like the one you linked to. Joining those discussions can keep you informed about any developments.

Overall, the unexpected change in Shift+F9 and Range.Calculate behavior has caused confusion and inconvenience for many users. While the future remains uncertain, exploring alternative solutions and staying informed can help you adapt and maintain your workflow efficiency.

I hope this information provides a helpful overview of the situation.

@Mark_Kaplowitz 

I think this is a bug - looks like off-sheet dependencies do not get dirtied when they should.

@fastexcel 

Thanks very much for replying Charles.

 

I'm not seeing that behavior with cross-sheet dependencies (but perhaps don't understand exactly what you mean).

 

Fyi, the Excel performance: Improving calculation performance page has now been updated to reflect the new behavior.

 

What used to read:

 

You can also recalculate only the selected worksheets by using Shift+F9. This does not resolve any dependencies between worksheets, and does not reset dirty cells as calculated.

 

is now:

 

You can also recalculate only the selected worksheets by using Shift+F9. This resolves dependencies for the worksheet and resets all previously uncalculated (dirty) cells as calculated.

 

In previous versions of Excel, the behavior was different and dirty cells were not set as calculated after the calculation completed. If user-defined functions relied on this behavior, these functions should be made volatile instead, as explained in the Volatile functions section in this article.

Wow - I originally wrote that article 15 years ago - great to see that it is being updated.
Done some more testing - there is a calc bug but its not new..

Sheet 1 has a volatile function (I used NOW() )
Sheet 2 has a formula that is downstream dependent on the volatile function.
Calc is Automatic

Using sheet calc on sheet 1 calcs the volatile function but the sheet 2 formula does not recalc (which it should) and the status bar shows Calculate

Pressing F9 resolves the bug.
Ah, thanks for clarifying (volatile and Automatic). Yeah seems it's been like that for a while.
1 best response

Accepted Solutions
best response confirmed by Mark_Kaplowitz (Copper Contributor)
Solution

Just to update on this, we have confirmed (via Microsoft Premier Support) that this is indeed a deliberate change to the Shift+F9 behavior. We'll have to consider our options for users relying on the old behavior.

I guess Charles should eventually update the decisionmodels page, and also this one:

https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calcul...

View solution in original post