Back to Blog
Blog post image

Excel Stale Value Formatting

Aug 9, 2023 4:52:00 PM

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

In Excel there are two main calculation options: manual calculation and automatic calculation. The default setting is automatic. This means that Excel will automatically recalculate all formulae whose input values (or "ingredients") have changed.
For most Excel users the default automatic calculation setting works just fine.
Financial Modellers, however, tend to use the manual calculation setting. This is because Financial Modellers like to see the impact of their changes on key outputs like profit, net assets, IRR or debt ratios. By switching the calculation setting to manual the modeller has total control over the moment the calculation happens, and they can ensure they are looking at their key outputs when they trigger the change.
Another reason for using the manual setting is to deal with bloated models that take a long time to calculate. Users can turn the calculation to manual and only trigger calculations when they are ready. I've seen many situations when the modeller triggers the calculation and then goes off to make a cup of coffee! ☕
How to change the calculation setting in Excel

You can change the calculation setting in the Calculation Options area in the Formulas tab on the Excel Ribbon:

Disable Stale Value

How to trigger calculations manually in Excel

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

A few things to consider when using manual calculation
The first time I found out about manual calculations was when my own models suddenly stopped calculating. Luckily I spotted it pretty quickly!
The reason for this is that the calculation mode is not set at the workbook level. It is at the application level. This means that if you change the setting from automatic to manual then all open workbooks will switch to manual.
Sounds okay, right? That is under your control after all.
It would be, however there are two further aspects of manual calculation that we need to consider: 1) when you save a workbook the calculation setting at the time that you save is held within the workbook; and 2) the calculation mode is changed based on the calculation setting of the first workbook that you open.
So what does that mean?
Let me talk you through what happened to me. A colleague shared a workbook with me that was saved with the calculation mode set to manual. I opened this workbook as my first workbook and then opened two of my own models. I then saved all three models and closed Excel. Both my models were now set to manual. When I opened one of them first they set the calculation mode to manual. Any subsequent model I opened was then switched to manual. Almost like a virus of manual settings spreading through my files! 🦠 😲
It was lucky that I spotted that the calculations weren't happening automatically. It's not obvious to spot.
Until now....

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:

Updated vs Stale


The setting can be managed through the Calculation Options area in the Formulas tab on the Ribbon:

Disable Stale Value

Let's see it in action:

Stale Formatting HiRes


[All images courtesy of Microsoft Tech Community]

My thoughts

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.

Release date

The feature is currently available in the Beta channel version of Excel.

Our Future of Excel series

This series of articles focuses on new features recently released or soon to be released in Excel. See the announcement from the Microsoft Tech Community team.

See other articles in our Future of Excel series here.


Take our free 30-question skills assessment

See where you stand with your Excel, financial modelling, data visualisation & analytics, and modelling tech skills

Take Free Skills Assessment