ICAEW.com works better with JavaScript enabled.
Exclusive

Excel update – are your values stale?

Author: Simon Hurst

Published: 16 Oct 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
One of the most exciting things about signing up for the Office 365 Insiders program is the thrill of discovering something unexpected lurking in a previously familiar Ribbon tab group or dropdown list. I was in the middle of writing about problem solving in Excel, and the need to check to ensure that Calculation mode hadn’t mysteriously been changed from automatic to manual, when I noticed that a new option had appeared in the Formulas Ribbon tab, Calculation group, Calculation Options dropdown: Format Stale Values:
Excel screenshot showing the Format State Values tab

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:

Excel screenshot showing 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:

Excel screenshot of the mode set to Manual

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:

Excel screenshot showing 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

Excel screenshot displaying a list of options including Calculate Now and Switch to Automatic .

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.