What are volatile functions in Excel? Definition and the full list

Microsoft's definition is worth memorising, because it describes the value rather than the function: a volatile function is "one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed."

What volatility costs

Excel does not recalculate everything when you type. Its calculation is "a three-stage process": "Construction of a dependency tree", "Construction of a calculation chain", and "Recalculation of cells". The tree records which cells feed which, and only the affected cells are marked for work — "when new data or new formulas are entered, Excel marks all the cells that depend on that new data as needing recalculation. Cells that are marked in this way are known as dirty."

Volatile functions opt out of that economy. Microsoft: "Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly."

The phrase carrying the cost is together with all dependents. One OFFSET at the top of a chain is cheap on its own; the four hundred formulas hanging off it are not, and they are recalculated on every keystroke that triggers a calculation anywhere in the file — including edits on other sheets that have nothing to do with them. Exceljet puts the practical effect bluntly: volatile functions "trigger recalculation on every worksheet change, so they can have a drastic impact on worksheet performance."

Microsoft's list of what counts as a trigger is also longer than most people expect: beyond entering data it includes "deleting or inserting a row or column", "renaming a worksheet", and "hiding or unhiding rows, but not columns". Rename a tab in a model built on OFFSET and the whole thing recalculates.

The list

Two published lists disagree slightly, and both are worth knowing.

The overlap is the part to act on: NOW, TODAY, the random functions, OFFSET and INDIRECT are volatile wherever you read about them. CELL and INFO are conditional — both sources qualify them with "depending on its arguments" — and Microsoft adds SUMIF under the same qualification. Excel's own Evaluate Formula dialog carries a related warning list, described under Formula auditing.

User-defined functions can join the list on purpose, by calling Application.Volatile on their first line — with the timing quirk described in the FAQ below.

Writing the volatility out

Most volatile formulas in a real workbook are there by habit, not by need.

Use INDEX instead of OFFSET. INDEX appears on neither list. A dynamic range written =SUM(OFFSET($B$2,0,0,COUNT($B:$B),1)) recalculates on every change in the file; the same range written with INDEX — =SUM($B$2:INDEX($B:$B,COUNT($B:$B)+1)) — recalculates only when column B changes. The rewrite is usually mechanical, and on a large model the difference is felt immediately.

Stop copying NOW and TODAY down a column. Ben Collins' guide to slow Sheets makes the point about volume rather than the function: instead of a hundred thousand copies of TODAY(), put it once in A1 and point every other formula at $A$1. One volatile cell recalculates; the rest are ordinary references to it.

Ask whether the timestamp should recalculate at all. A date stamped when a row was entered is a value, not a formula. Paste it as a value and the question disappears.

Replace INDIRECT where a structural reference will do. INDIRECT is genuinely irreplaceable when it points at a sheet name held in a cell. It is just as often used to build a reference that a named range, an Excel table column or a plain lookup would have expressed without volatility.

Volatile functions in Google Sheets

The concept transfers, and the population is smaller. Ben Collins names four: "In Google Sheets there are four functions, NOW function, TODAY function, RAND function, and RANDBETWEEN() function, that are known as volatile functions", "which means they recalculate every time there's a change to the Sheet. All dependent formulas must also be recalculated too."

Google's own settings documentation names the same four and explains why they need a governor: "volatile functions such as TODAY, NOW, RAND, and RANDBETWEEN, because those values inherently change all the time. TODAY changes every new day, NOW changes every second, and RAND and RANDBETWEEN change at an infinite frequency. This can cause the entire sheet to not work." The control is under File → Settings → Calculation, where the "Recalculation" option "sets how often certain formulas are updated" — which is as close as Sheets gets to the subject of Calculation mode.

Google also publishes refresh intervals for the functions that fetch data from outside the file, and they are nothing like every keystroke: "ImportRange: 30 minutes", "ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour", "GoogleFinance: may be delayed up to 20 minutes." Slow imports and volatile recalculation are separate problems.

How Formula Foundry handles this

Formula Foundry does not know which functions are volatile, and it will not warn you about them. It has no calculation engine and never evaluates your workbook — it parses, formats, explains and translates formulas. Performance work stays yours: Excel's calculation options and Google's recalculation setting are where the levers are.

Where it helps is the rewrite. Swapping OFFSET for INDEX across a model means reading forty formulas you did not write and changing the ones that matter, and that is the job the sidebar editor is for. The selected cell's formula opens across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted — so an OFFSET buried at the fourth level of a nested expression is visible instead of hidden in a 200-character line. Find and replace works inside that editor, and the AI assistant explains a formula step by step in plain English when being told what an inherited construction does is faster than working it out.

Once you have a non-volatile pattern that works, the snippet library keeps it under a name and shares it with the team, so the next dynamic range someone builds starts from the INDEX version rather than the OFFSET one. The audit walkthrough runs the sequence on an inherited file.

FAQ