Stale Value Formatting
December 12, 2024
What is Stale Value Formatting?
When the underlying data for a formula changes, but the formula has not been recalculated yet, then that cell is considered stale.
When can Stale Value Formatting be a Problem?
When the calculation settings in Excel are set to “Manual”, “Partial” or “Except for data tables”, circular calculations, including circular interest can get a strikethrough, making models look like they have blown up or gone wrong. This is a setting added to Excel to indicate cells that are stale and need recalculating. Users may wish to turn off this setting.
Key Learning Points
- Stale value formatting can lead to strikethroughs of circular interest cells or cells relying on data tables
- This indicates cells that need recalculating
- Users can press “Calculate Now” or the F9
Stale Value Formatting
In 2024 an update was made to Excel regarding circular calculations, including on interest.
The calculation options were renamed. “Except for data tables” was renamed as “Partial” calculation. There was no big issue here (partial does not recalculate data tables or Python formula). When workbook calculation is set to “Automatic”, Excel calculates every cell whenever a change is made. This is useful as all cells are always up to date, but a big model can become very slow. So “Partial” or the older “Except for data tables” only calculates cells that need to be re-calculated. It reduces the workload on Excel and speeds up a workbook.
But another recently added update means all the circular numbers in Excel may get a strikethrough if “Partial” or “Manual” are used in models featuring circular calculations, including circular interest. This has the potential to concern users:
This also happens with the outputs of data tables or Python formulas. Excel sees the cells as “stale” until the calculation resumes and completes.
This is Stale Value Formatting.
How to Deal with Stale Value Formatting?
It mostly happens when the calculation setting is set to Manual.
Options to deal with this include:
1. Turn stale value formatting off. To do this, go to the Formulas ribbon/Calculation Options and untick “Format Stale Values.”
2. Alternatively, turn off stale value formatting by changing the Excel Options. Go to File/Options/Formulas/Error checking Rules/Cells containing stale value and uncheck this box
3. Instead, you can keep stale value formatting on and deal with the strikethroughs each time they occur. Press F9 or click “Calculate Now” by going to the Formulas ribbon/Calculation section/Calculate Now. However, as soon as you perform another task, the strikethroughs will come back.
Download
Download the free file to see stale value formatting in a three-statement circular interest model. Your Excel settings will need to be set correctly to see stale value formatting:
- Excel calculation set to Manual. In Excel, go to File/Options/Formulas/Workbook Calculation = Manual
- Circular iterations on. In Excel, go to File/Options/Formulas/Enable Iterative Calculation = checked.
- Stale value formatting on. In Excel, go to File/Options/Formulas/Error Checking Rules/Cells containing stale value = checked
- Now make any change to the model e.g. delete a cell, and watch the numbers strikethrough.
Conclusion
The strikethrough from Stale Value Formatting and Partial calculations can be confusing but understanding why it is there (to indicate old/stale values that need re-calculating) and how to remove the strikethroughs where they are not needed (by changing Excel settings) should provide solutions to users.