Volatile functions in Excel, and how to find them
Volatile functions recalculate more often than ordinary formulas. In this audit the list is NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO, and CELL. A few calls in a header are normal. Hundreds of OFFSET or INDIRECT calls are a calculation problem. Find them in the file, replace the ones that do not need to be volatile, and leave the ones that do.
What volatile means in practice
A normal formula calculates when one of its inputs changes. A volatile formula is eligible to calculate on a much wider set of edits, including edits that have nothing to do with it. That is why a workbook full of OFFSET can pause when you type in a distant sheet. Excel’s dependency tree cannot be narrow if the formula’s precedents are computed rather than written. NOW and TODAY are volatile because the clock is an input that moves. RAND is volatile because a new sample is the point. OFFSET and INDIRECT are volatile even when the range they return has not changed.
The list, and what is not on it
NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT, INFO, and CELL are the eight names this site scans for. RANDBETWEEN is not counted as RAND. INDEX is not volatile. VLOOKUP, XLOOKUP, MATCH, and SUMIFS are not volatile. They can still be slow. INDIRECT is easy to spot because the formula builds an address as text. OFFSET is easy to miss because it looks like a flexible range. CELL and INFO show up in old formatting tricks and in sheets that display the file name. If you do not need the file name to recalculate forever, paste it as a value.
How the checker counts
It reads formula text in worksheet XML, skips text inside quotes, and expands shared formulas from the master so a copied formula is not hidden. Sample addresses are the first ones found, not a ranking. Use one sample to learn the pattern, then search the sheet. Conditional formatting formulas are not in this count. Whole-column references are counted beside the volatile calls because they often travel together: OFFSET or SUMIFS pointed at A:A. A zero on an .xlsb file means the binary formulas were not read.
Replacements that keep the answer
INDEX can return the range people build with OFFSET, and it is not volatile. A direct reference can replace INDIRECT when the sheet name is known. CHOOSE or a short set of real formulas can replace a sheet name built as text. TODAY in a printed header can stay if you accept one volatile call. RAND in a Monte Carlo model can stay if a new sample is the product. RAND in a dashboard that should hold still while someone scrolls should become a value, with a button or a note that explains how to draw a new sample. The repair is specific to the call. A global search-and-replace of OFFSET with INDEX is right only when the arguments match.
When you want the numbers from your own workbook, scan the formulas locally.
Whole columns are the partner problem
A:A and 1:1048576 do not appear in the volatile list and still dominate calculation time. Point the formula at a table or at the real last row. Structured table references grow when rows are added, which removes the excuse that a whole column was “safer.” If the volatile count is low and the whole-column count is high, you do not have a volatile problem. You have a range problem. The calculation-speed guide orders the edits. This page is the list and the meaning of each name.
After you edit
Calculate, save, and run the checker again. The count for the function you removed should drop. If it does not, a shared formula or another sheet still has it. Time one edit before and after if the workbook was slow enough to notice. A lower count that does not change the clock means the volatile calls were not the bottleneck. Go to formatting rules and formula volume next. Keep the functions you intentionally left, and say so in a cell comment so the next audit does not delete a TODAY that the report is supposed to show.
A pass that does not wreck a model
Work on a copy. Filter the checker’s function list to the largest count. Open one sample and read the formula aloud. If it is OFFSET of a table, sketch the INDEX version beside it and calculate both on one row before you fill down. If the results match, replace the column. If they do not, you have not understood the height or the anchor, and a mass replace will invent a new bug that looks like speed work. TODAY in a cover cell can stay; write “intentional” in a note so a later cleanup does not “fix” the date. RAND is the one people remove and then cannot reproduce a figure. If a figure depends on a random draw, paste values and label them as a frozen sample, and keep the formula in a cell off to the side if a new draw must still be possible. INFO and CELL are rarely load-bearing. They are the first deletions. After each function family, save and rerun the count. You want a falling number and a workbook that still ties out to the total you wrote down before you started. A tie-out that moves is not an acceptable price for a quieter status bar, unless the move is the RAND sample you chose to freeze.
Where people put volatile functions back
A month later someone needs a sheet name in a cell and reaches for INDIRECT because a forum post showed it. The count creeps up. The way to keep the repair is to leave one worked example of the INDEX pattern in a corner of the workbook, or in the team’s notes, so the next change copies a non-volatile pattern. The checker is cheap to rerun. A file that stays near zero without being forbidden from using TODAY has been repaired properly. A file that is fast only because calculation is manual has been hidden. If a later edit needs a sheet name, copy the INDEX example you left in the corner instead of pasting INDIRECT from a forum. Rerun the count after that edit so the repair stays visible.
Related reading
The closest repair notes are Causes of slow calculation and Clean up defined names .
Walk the same checks in order with the Workbook audit checklist.
The terms behind this guide are Volatile function and Conditional formatting rule .
The paired check for that note is the Conditional formatting bloat checker.
Questions people ask before trusting the result
Is INDIRECT always worth removing?
Remove it when it builds an address you could have written directly. Keep it when the workbook genuinely must turn text into a reference, and expect the calculation cost.
Does a volatile function in a closed workbook matter?
It matters when the workbook calculates. A closed file does not calculate. The cost appears on open and on edit.
Why is my RAND count lower than the cells that look random?
Values pasted over old RAND formulas are values. Only formulas that still call RAND are counted. A number that was once random is not volatile anymore.
Are lambda and dynamic arrays volatile?
Not as a class. A dynamic array is volatile only if the formula it spills calls a volatile function. This checker looks at those names, not at the spill itself.
Can I search for these in Excel instead?
Yes. Find can search formulas. The checker is useful when you want a count per function across every sheet without opening Excel, including sheets you would have to unhide first.