Excel Stale Value Formatting
Microsoft announce new Stale Value Formatting for Excel
Announced by Microsoft on 8th August 2023
In this latest article in our Future of Excel series, we look at Excel's new Stale Value Formatting functionality.
Manual or automatic calculation
You can change the calculation setting in the Calculation Options area in the Formulas tab on the Excel Ribbon:
There are four shortcuts for triggering a manual calculation in Excel:
Shift + F9 - This only calculates the sheet that you are on (the active sheet)
F9 - This calculates the cells that have changed since you last recalculated
Ctrl + Alt + F9 - This is a full recalculation - Recommended
Shift + Ctrl + Alt + F9 - This is also a full recalculation but in addition it forces a complete bebuild of the calculations in the background of Excel
Introducing Stale Value Formatting
Not the greatest name in the world, but it's a welcome addition to Excel, and one that we have been waiting for for decades.
With this new Stale Value Formatting change in Excel, cells whose input values have changed but have not been updated (because the model is in manual calculation mode) will now be formatted with a strikethrough:
The setting can be managed through the Calculation Options area in the Formulas tab on the Ribbon:
Let's see it in action:
[All images courtesy of Microsoft Tech Community]
This is a great addition from Microsoft. It is incredibly helpful to be able to see which cells have not recalculated. This new feature will undoubtedly reduce some of the risk of using the manual calculation setting in Excel.
My only reservation is the fact that the formatting being applied is a strikethrough and that it is not customisable by the user. It would be great to have this as style option linked to the workbook style.
It would also be helpful to be able to see a log of cells not calculated in the navigation pane for example, or in the Formula Auditing section.
I'd love to see Stale Value Formatting built into the GoTo Special functionality.
The feature is currently available in the Beta channel version of Excel.