Spreadsheets

Volatile functions slowing Excel: TODAY, INDIRECT and OFFSET

Why TODAY, INDIRECT and OFFSET make a big Excel statement file slow, which other functions are volatile, and what to use instead, with formulas you can copy.

Editorial illustration for Volatile functions slowing Excel: TODAY, INDIRECT and OFFSET

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?

FunctionWhy it is volatileCommon use in an expense file
TODAY, NOWValue changes with the clockDays since a bill, current month filter
RAND, RANDBETWEENNew random number each timeRarely needed in budgets
OFFSETResult depends on a position computed at run timeMoving range for "last 30 days" or running totals
INDIRECTBuilds a reference from textPulling a figure from a tab whose name is typed in a cell
CELL, INFOCan depend on the environmentFile 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?

  1. 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.
  2. 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.
  3. 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

  1. Replace OFFSET with INDEX or direct references.
  2. Replace INDIRECT with a single table and SUMIFS.
  3. Move TODAY into one cell and point everything at it.
  4. Check that you are not also using whole-column array formulas; those slow files down even without volatile functions.
  5. 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.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports