Volatile functions recalculate every time anything in the workbook changes, not only when their own inputs change. TODAY, NOW, RAND, RANDBETWEEN, OFFSET and INDIRECT are the usual ones. A few of them in a small sheet cost nothing; thousands of them on top of a statement with several thousand rows make every keystroke lag.
Which Excel functions are volatile?
| Function | Why it is volatile | Common use in an expense file |
|---|---|---|
| TODAY, NOW | Value changes with the clock | Days since a bill, current month filter |
| RAND, RANDBETWEEN | New random number each time | Rarely needed in budgets |
| OFFSET | Result depends on a position computed at run time | Moving range for "last 30 days" or running totals |
| INDIRECT | Builds a reference from text | Pulling a figure from a tab whose name is typed in a cell |
| CELL, INFO | Can depend on the environment | File name in a title cell |
INDEX is not volatile in current Excel versions, which is why it is the favourite replacement. The catch is that anything which depends on a volatile cell is recalculated too, so one TODAY at the top of a chain can drag a lot of formulas with it.
How do I tell whether volatile functions are my problem?
- Press Ctrl+F, set Look in to Formulas and Within to Workbook, and search for OFFSET(, INDIRECT( and TODAY(. Click Find All and read the count at the bottom of the box.
- Switch calculation to manual under Formulas, Calculation Options and type in a cell. If typing is now instant, calculation is your bottleneck. Switch back to Automatic afterwards.
- Another sign: you close a workbook you only looked at and Excel asks whether to save changes. Volatile formulas recalculated when it opened.
Say your file has 5,000 statement rows and each row has an OFFSET to fetch the previous balance. Every edit forces 5,000 OFFSET recalculations, plus whatever sits downstream. That is the pattern to hunt.
What can I use instead of OFFSET?
The most common use is a total of the used part of a column. Instead of an OFFSET-based range, use INDEX to mark the end:
- Slow: =SUM(OFFSET($C$2,0,0,COUNTA($C$2:$C$5000),1))
- Faster: =SUM($C$2:INDEX($C$2:$C$5000,COUNTA($C$2:$C$5000)))
Both add up the filled cells from C2 downward, assuming there are no gaps. The second version produces the same answer without volatility. For a "previous row" lookup, point straight at the cell above: in D3 write =D2+C3 rather than building the address.
What can I use instead of INDIRECT?
INDIRECT is usually there to switch between month tabs, as in =INDIRECT("'"&A1&"'!C10"). Two safer routes:
- Keep every month in one table with a Month column, then use SUMIFS(Table1[Amount],Table1[Month],$A$1). One table, no tab names, no INDIRECT.
- If you must use separate tabs, write the direct reference to each tab once, for example ='Apr'!C10, and use CHOOSE to pick among them.
Structured references such as Table1[Amount] also grow when you add rows, which is the other thing OFFSET was being used for.
How do I keep a TODAY without paying for it everywhere?
Put =TODAY() in one cell, say $H$1, and have every other formula refer to $H$1 rather than call TODAY() itself. That reduces the number of volatile formulas to one. When you are finalising a month, you can also freeze the date: type it with Ctrl+; so the cell holds a fixed date, and the sheet stops recalculating because of it.
Fix order for a slow statement file
- Replace OFFSET with INDEX or direct references.
- Replace INDIRECT with a single table and SUMIFS.
- Move TODAY into one cell and point everything at it.
- Check that you are not also using whole-column array formulas; those slow files down even without volatile functions.
- Save, reopen and test the typing speed.
If you keep several years of statements only to produce category reports, SpendixAI can take the statement files directly and build those reports, which leaves your sheet doing less work.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


