Two questions sprang to mind: firstly, what is a stale value and secondly, why would I want to format it in a particular way? The location of the option in the Calculation Options dropdown is a big clue to what a stale value is, it is a value that would have been recalculated if the calculation mode was set to Automatic, but hasn’t been, because the calculation mode is Manual (there are other reasons that could cause a formula to become stale, such as cancelling a very slow recalculation). Given that one of the most read articles in the history of the Excel Community dealt with reasons for formulas not calculating as expected, this ability to highlight uncalculated formulas could be really beneficial.
We’ll see how this works. Here we have entered a simple SUM() formula with the calculation mode set to Automatic and Format Stale Values turned on:
As you can see, no formatting is applied automatically to our cells.
We will change the mode to Manual and then change one of our values:
Our SUM() formula in cell A3 hasn’t been recalculated and neither has our FORMULATEXT() formula in the adjacent cell, and we can see that both cells are formatted with a text strikethrough. We can click on the Calculate Now command in the same group, or use the equivalent F9 keyboard shortcut, to recalculate our spreadsheet and the formulas will no longer be stale and the formatting will be removed.
The strikethrough formatting is not the only consequence of turning on Format Stale Values. A new rule has been added to the list of Error checking Rules in the Formulas section of Excel Options:
It’s worth noting that, in this Beta preview of the feature at least, the formatting and error checking rule are inextricably linked. Turning the rule on or off turns the formatting on or off and turning the formatting on or off turns the rule on or off. It also appears that the formatting is not customisable, so there is a risk that it could clash with strikethrough text being used for some other purpose.
Here, we have clicked on a cell containing a stale value, the cell is not marked with the usual triangle in the top left corner, but when the cell is selected, the error checking warning icon appears, and clicking on this displays a list of options including Calculate Now and Switch to Automatic Calculation
There is always a danger in exploring new Excel features before they are made generally available, as significant changes could be made in the meantime. However, in this case, it’s worth preparing for the introduction of this feature. If people understand what the stale value formatting signifies, it could be useful in avoiding one of the most significant Excel cries for help: “My formula isn’t calculating properly!”
Conclusion
The ability to identify stale values, once it becomes generally available, and once spreadsheet users become familiar with its implications, could be of great benefit in helping users identify the issue of manual calculation mode and could help them to resolve the problems it creates for themselves.