Single TODAY header
One cell =TODAY() referenced by formulas instead of TODAY in each of five thousand rows.
What are volatile functions in Google Sheets?
Volatile functions recalculate whenever Sheets recalculates the sheet, not only when their direct arguments change. NOW, TODAY, RAND, RANDBETWEEN, OFFSET, and INDIRECT are common examples. Heavy use in large ARRAYFORMULA columns can make shared files feel sluggish and make results change on every edit.
When to use it
Use volatile functions sparingly for timestamps, random samples, and dynamic ranges that must react to layout changes when named ranges are impractical.
When to skip it
Avoid NOW in every row, RAND in published client models, and OFFSET on million-cell ranges when INDEX or TABLE references are stable.
How it works
-
1
Volatile functions mark dependent cells for frequent recalculation by the engine.
-
2
NOW and TODAY update on recalc cycles; store one timestamp cell if many formulas need current date.
-
3
RAND reshuffles on recalc; paste values before sharing Monte Carlo outputs.
-
4
OFFSET and INDIRECT build addresses at runtime, blocking some optimizations.
-
5
Replace volatile patterns with static references or QUERY on bounded tables when possible.
-
6
Profile slow sheets by temporarily removing volatile columns and measuring edit speed.
Examples in Google Sheets
Random draw
RAND in helper column for sample; copy > paste values before emailing results.
OFFSET rolling window
Last 30 days OFFSET range replaced with FILTER on date column for clearer spill behavior.
Build this without starting from a blank cell
Use a Better Sheets tool for Volatile functions, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- NOW inside ARRAYFORMULA filling an entire column on a shared ops sheet.
- RAND-driven pricing shown to customers who reopen the file and see new numbers.
- INDIRECT built from user-editable text without validation, causing surprise recalc paths.
- Assuming IMPORTRANGE volatility when the real issue is local NOW columns.
- Using OFFSET instead of expanding named range when table size only grows downward.
Frequently asked questions
- Which functions are volatile?
- Common list includes NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, and INFO per Google documentation.
- Is VLOOKUP volatile?
- No. VLOOKUP recalculates when its arguments change, not on every sheet recalc by volatility rules.
- Volatile and recalculation speed?
- Many volatile cells increase work on each edit. Reduce count for performance.
- Freeze RAND results?
- Copy range and Paste special > Values only after calculation.
- Volatile in conditional formatting?
- Custom formulas using NOW can refresh formatting timing; test performance on large ranges.
- INDIRECT vs INDEX?
- INDEX with fixed range is usually less volatile and more auditable than INDIRECT strings.
- Volatile in Apps Script?
- Script reads computed values after sheet recalc; volatility affects what value script sees when run.
- Audit volatile cells?
- Search for NOW RAND OFFSET INDIRECT with Find and review each usage context.
Related Tutorials
Watch how Volatile functions works
Related glossary terms
Recalculation
Recalculation is when Google Sheets recomputes formula results after something changes. Most functions update when their input cells change. Volatile functions like NOW, RAND, and OFFSET recalculate more often. Large workbooks may feel slow when thousands of formulas refresh at once.
Read guide →ARRAYFORMULA
ARRAYFORMULA tells Google Sheets to treat a formula as an array operation so one expression fills many cells at once. It is common with IF, VLOOKUP, and math on full columns before dynamic arrays made many patterns automatic. It still matters for legacy sheets and explicit control.
Read guide →onEdit trigger
The onEdit trigger runs a script automatically when someone changes a cell in your spreadsheet. It is one of the simplest ways to make Sheets react to user input without clicking a custom menu. You define the function in Apps Script and bind it to the onEdit event for that file.
Read guide →Open range reference
An open range reference points at an entire row, column, or unbounded slice such as B:B or 2:2 instead of a fixed rectangle like B2:B500. Formulas with open ranges include every cell in that line, which is convenient for growing tables but expensive and risky when stray values live far below your data.
Read guide →Done reading about Volatile functions?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.