Established onExcel 365 and 2016 and laterGoogle Sheets, web: comparable list, small differences
The calculation engine keeps a map of which cell depends on which, and recalculates only what a change could have affected. That is why a workbook with a hundred thousand formulas is usable at all.
A small number of functions opt out. They declare themselves as needing recalculation on every pass, whatever changed, because their value could have changed for reasons the engine cannot see.
Which ones and why
The current time and today's date, because the clock moves. The random number generators, because that is their purpose. The function that returns a reference offset from another, and the one that builds a reference from text, because the engine cannot know in advance what they will point at. And some information functions about the file or the environment.
Each declaration is defensible in isolation. The cost is not the function itself.
Why one cell can slow a whole file
Anything depending on a volatile cell must recalculate whenever the volatile cell does, which is always. That property is transitive: it travels down the dependency chain to everything below.
So a single such function placed high in a model marks the entire model as needing recalculation on every keystroke anywhere in the workbook. The symptom is a file that has become slow for no apparent reason after an edit that looked trivial.
The substitutions
The offset function can almost always be replaced by an index into a range, which is not volatile and expresses the same thing. The text-built reference can be replaced by structured references into a named table, which is better in every other respect too.
Today's date is harder: where a model genuinely depends on the current date, the answer is a single cell holding it, refreshed deliberately, with everything else referring to that cell. One volatile cell is survivable; a volatile cell repeated in a thousand rows is not.
The workaround that belongs in the first part of this site
Faced with a slow workbook, the common remedy is to switch calculation to manual. The numbers on screen then stop updating until somebody presses the key that recalculates.
Which means every figure in the file is now potentially stale, showing a result computed from inputs that have since changed, and nothing marks it. A sheet in manual mode is a machine for producing plausible wrong answers, and it is a setting most people who use it forget is on.
If it must be used, put a cell somewhere prominent that recomputes something trivially checkable — the current time, or a count — so that a stale sheet is visibly stale. That is the same technique as the check cells in the final part, applied to the calculation state itself.
What else makes recalculation expensive
References to entire columns, which in older versions meant a million cells each. Conditional formatting rules with formulas, which are evaluated for every cell in their range. Large numbers of cross-file references. And formulas that perform a lookup per row over a large table, which the entry on the lookup family ends on.
How to find the cause
The dependency tracing tools in the final part will show what a slow cell depends on. Before that, the cheaper move is a search of the workbook for the names of the volatile functions listed above: the result is usually one cell, in one place, put there years ago for a reason nobody remembers.
What we cannot verify
Which functions are volatile is documented by the vendor and is observable. The list differs slightly between products and has changed between versions; the account here was checked against current builds of both. Claims about the relative cost of the other causes are our observation from testing rather than published measurements.
How to measure it rather than guess
Excel reports calculation time in its status bar when a workbook is recalculating, and both products respond to a full recalculation keystroke that forces every formula to be evaluated regardless of the dependency map.
Timing a normal recalculation against a full one tells you whether the dependency map is doing any work at all. Where the two times are close, almost everything in the workbook is being treated as dirty, which means a volatile cell is high in the chain, and the search described above is the next step.
The one place volatility is the point
A cell holding the moment a report was produced is worth being volatile, because a report showing a stale timestamp is worse than one showing none.
Put it in exactly one cell, refer to that cell everywhere else, and accept the cost. The rule that follows from this whole entry is not to avoid these functions but to use each of them once.
Why this matters more than it used to
Files are larger than they were, and the expanding formulas described in the previous entry concentrate more work into single cells. A volatile dependency above one of those now triggers the recalculation of a whole block rather than of one value.
The two features are individually good and interact badly, which is worth knowing before attributing a slow file to its size.
In short
- The engine recalculates only what a change could have affected, which is why files are usable.
- A few functions declare themselves as always needing recalculation.
- The property is transitive, so one such cell high in a model marks the whole model.
- The offset function has a non-volatile equivalent that says the same thing.
- Manual calculation is a machine for producing stale numbers that nothing marks as stale.
- Searching the workbook for the volatile function names usually finds one cell.