This series looks at some of the things that can go wrong in an Excel spreadsheet and at what we can do to avoid or resolve the issue. Having dealt with a range of issues including many that cause numbers to appear not to add up correctly, for our last episode we are going to look at the most read post ever on the Excel Community and see how the key issue it concerns has now been addressed by a recent Excel enhancement.
Introduction
The series so far:
- Excel, what’s occurrin’ – It doesn’t add up
- Excel, what’s occurrin’ 2 – Precision as displayed
- Excel, what’s occurrin’ 3 – Incomplete ranges
- Excel, what’s occurrin’ 4 – Number or text?
- Excel, what’s occurrin’ 5 – Getting iffy with it
- Excel, what's occurrin' 6 – Towards zero
- Excel, what's occurrin' 7 – Conditional formatting from top to bottom
- Excel, what’s occurrin’ 8 – Data validation
- Excel, what’s occurrin’ 9 – The wrong trousers
- Excel, what’s occurrin’ 10 – A date with PivotTable destiny
- Excel, what’s occurrin’ 11 – Optional errors
Formulas not calculating
The Excel Community Excel tips and tricks series (the articles previously known as Excel ‘Tip Of The Week’) has just reached the significant milestone of the 500th article. Back in 2014, a seemingly simple tip concerning reasons for Excel calculations to stop working started its meteoric progress to generating well over 1,000,000 views:
The tip didn’t contain any particularly revolutionary insights, but the popularity of the title showed just how many Excel users search for a solution to a sudden cessation in normal Excel calculation service.
In my experience, you would be lucky for a user to initially identify the problem as a general issue with all formulas not calculating properly. It would be more likely that they would ask something much more specific such as “why has the sum function stopped working?”, and some more investigation would be required to identify the wider problem.
The original post identified three reasons for formulas to suddenly and mysteriously stop calculating: the calculation mode having been changed from automatic to manual, the cells that contain the formula having been previously formatted as text and the presence of a circular reference somewhere in the workbook.
An Excel enhancement now helps significantly with preventing the first of these issues. ‘Stale Value Formatting’ has been generally available for several months now.
As long as the ‘Format Stale Values’ option is turned on, if the calculation mode has been switched from automatic to manual, then any formulas that haven’t been recalculated should be formatted with a strikethrough:
As well as the strikethrough formatting, selecting the cell will also display a warning icon. Clicking on this icon displays a message warning that the cell’s value is stale and needs recalculation. The stale formatting only works with formulas that are part of the normal Excel calculation chain, not for cells in PivotTables or Power Query Tables that have not been refreshed, although cells that refer to cells in those Tables will be shown as stale when the Tables themselves change. Accordingly, it’s best to treat this new feature as a useful indication of the current calculation mode, rather than as complete proof that all values are up to date.
It’s all over now
That was the final episode in our Excel oddities series, I hope that you found some of the posts useful and have come across some solutions to problems you have, have had or are yet to have.
Additional resources
You can explore all aspects of Excel, including the rest of this series, using the ICAEW Excel archive portal:
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
