A column of 10,000 formulas, each one growing the value in the cell above it, is about the cheapest model a spreadsheet can hold. In an evaluator that computes a referenced formula on the spot every time it meets one, the totals still come out right, but the arithmetic bill does not: the last cell drags 9,999 ancestors through evaluation, the one above it 9,998, and a single full pass adds up to roughly 50 million evaluations for 10,000 multiplications. Change one growth rate at the top and that evaluator has to pay the whole bill again, because it keeps no record of which cell reads which
Why one edited cell can cost a whole-workbook pass
Two shortcuts produce that behavior, and most hand-rolled formula engines take one of them. The recursive shortcut evaluates every referenced formula afresh and ignores whatever value is already cached, which turns a chain into quadratic work and turns a circular reference into a stack overflow. The full-pass shortcut is safer: evaluate every formula in some fixed order, as many times as it takes for nothing to change. It is correct, and its cost tracks the size of the workbook rather than the size of the edit
Excel avoids both with its calculation chain, an ordering of formula cells that lets an edit flag a small set of cells and walk only the affected tail. The XLSX engine in HotXLS uses the same idea in the form of an explicit dependency graph behind TXLSXWorkbook.Recalculate. The first call builds the graph from every formula on every sheet and evaluates all of them. Each later call evaluates only the formulas downstream of cells whose values changed since the previous pass, plus a short list of formulas that must run every time
How Recalculate turns an edit into one ordered pass
Each formula cell becomes one node, and an edge runs from every formula cell a formula reads to the formula that reads it. A reference such as SUM(Model!B2:B500) produces an edge from every formula cell inside that rectangle, and a 3D reference across a run of sheets is matched against each sheet in the run. Plain constants are not nodes at all; they only matter as the place where an edit starts
Writing Value on a cell does almost nothing at the moment of the write. The workbook queues the cell's address, and that is it. When Recalculate starts, every formula whose references cover a queued address is marked dirty, and the mark flows along the edges to all of that formula's dependents, then to theirs. A workbook on which Recalculate has never been called keeps no graph, and its writes cost a single nil check, so code that only reads and writes values pays nothing for the feature
The dirty part of the graph is then sorted with Kahn's algorithm. The detail that makes the pass cheap is how in-degrees are counted: only dirty precedents count. A clean precedent already holds a valid cached value, so a formula never waits on it. During the pass, a formula that references another formula cell reads that cell's cached value instead of evaluating it again; the sort guarantees the cache is already fresh. Every dirty formula is evaluated exactly once
The code below builds the chain from the opening paragraph and edits its single input. Sheet indexes in HotXLS start at 1, so Inputs is sheet 1 and Model is sheet 2; rows and columns in Cells are 1-based as well
uses
SysUtils, lxCalc, lxHandleX; // lxOk is declared in lxCalc
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
Row: Integer;
Last: Variant;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs'); // Sheets[1]
Model := Book.Sheets.Add('Model'); // Sheets[2]
Inputs.Cells[2, 2].Value := 0.05; // growth assumption in Inputs!B2
Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
for Row := 3 to 10000 do
Model.Cells[Row, 2].Formula :=
'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';
if Book.Recalculate <> lxOk then // builds the graph, evaluates 9,999 formulas
Exit;
Inputs.Cells[2, 2].Value := 0.07; // queues one address
if Book.Recalculate = lxOk then // formulas fed by Inputs!B2 run again (here: all 9,999)
Last := Model.Cells[10000, 2].Value; // cached result, read like any value
// One-off read: RecalcCell runs Recalculate, then returns the cell
// (sheet 2 = Model, row 10000, column 2)
Book.RecalcCell(2, 10000, 2, Last);
finally
Book.Free;
end;
end;
In this particular model every formula reads Inputs!$B$2, so the second pass still touches all 9,999 of them; the graph cannot shrink an edit that genuinely affects everything. The saving shows up in models with many inputs, where a scenario loop writes three or four cells and the rest of the workbook keeps its cached values
Value writes are cheap, formula writes rebuild the graph
Only value edits stay incremental. Anything that can change the set of nodes or edges marks the whole graph stale, and the next Recalculate rebuilds it from scratch and evaluates every formula. That covers assigning Formula, clearing a formula cell by writing Null or an empty value to it, adding, moving or deleting sheets, inserting or deleting rows and columns, editing defined names, and changing what-if data tables. Registering or unregistering a user function, or assigning OnUserFunction, keeps the graph but marks every node dirty
The practical consequence for report generators is a loop shape. Lay out formulas once, call Recalculate, and from then on touch only values. A loop that rewrites a formula template per customer and then recalculates gets a full rebuild every iteration, which is exactly the cost the graph exists to avoid. When a template change really is needed, batch all formula edits ahead of one Recalculate, so the rebuild happens once. The scenario loop itself then writes values only
// Model built once; Recalculate already called
for I := 0 to High(Scenarios) do
begin
Inputs.Cells[2, 2].Value := Scenarios[I].Growth; // value writes only
Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
if Book.RecalcNeeded then // True when cells are queued or dirty
if Book.Recalculate <> lxOk then
Break;
Results[I] := Model.Cells[10000, 2].Value;
end;
// Let SaveAs recalculate only if something is still pending
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');
RecalcBeforeSave defaults to xrmManual, which never recalculates during a save. xrmIfDirty is the setting to use for generated files: it recalculates only when the graph is stale or has dirty formulas, so a workbook already recalculated in the loop is written as is, and one with a forgotten edit is not saved with stale cached values
Which Excel functions force recalculation on every pass?
Formulas that call NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO or GETPIVOTDATA are volatile in HotXLS: they are marked dirty at the start of every pass, whether or not anything upstream changed, and so are all of their dependents. The clock and random functions are volatile for the obvious reason. OFFSET and INDIRECT are volatile because the cells they read are computed from argument values at run time, and no static walk of the formula can say which edges to draw
The same rule applies to references the graph builder cannot pin to a concrete rectangle, to a defined name that refers to itself through other names, to dynamic-array spill references, and to what-if data tables. Defined names in general are not a problem: the builder expands a name to the ranges it refers to, so formulas that go through workbook or sheet-scoped names get real edges. The defined names and cross-sheet formulas article covers how those names resolve
The conservative direction is the right one here. An unnecessary evaluation costs a little time; a missing edge means a stale number in a report that looks finished. Keep OFFSET and INDIRECT off the hot paths of large models and confine them to the few cells that really need computed addresses, because a volatile cell near the top of a chain makes the whole chain volatile in practice
Functions supplied through OnUserFunction or RegisterUserFunction sit at the other end. They are not volatile, so a handler is called again only when the cells feeding its arguments change, like any built-in function. A handler that reads an exchange rate from a database therefore keeps returning its last answer across passes. Write the rate into an input cell and pass that cell as an argument instead; the graph then sees the dependency, and the formula reruns precisely when the rate changes. The custom functions in the HotXLS formula engine article explains how handler arguments arrive
Circular references: lxErrorRef or iterative calculation
With the default Iterate = False, TXLSXWorkbook.Recalculate returns lxErrorRef when the sort finds a cycle. Cycle members are the dirty nodes Kahn's algorithm can never release, and any dirty formula downstream of them is blocked for the same reason. Those formulas keep their previous cached values, while everything else in the dirty set is evaluated in order. A failed pass also marks the graph stale, so the call after it rebuilds the graph and evaluates every formula once more
case Book.Recalculate of
lxOk:
Book.SaveAs(OutputFile);
lxErrorRef:
// the cycle and its downstream formulas still hold their old caches;
// LogWarning is your own routine
LogWarning('Circular reference in model, report not written');
else
LogWarning('Recalculation did not complete');
end;
Setting Iterate := True turns on the same iterative calculation Excel offers. The engine evaluates the cycle and its blocked dependents repeatedly, up to IterateCount passes (default 100), until no value moves by IterateDelta (default 0.001) or more. Empty cycle members start from 0. Reaching the pass limit keeps the last results and still returns lxOk, which is why iteration should stay off for generated reports: cycles in real report models are nearly always mistakes, such as a total row caught inside its own SUM range, and a definite error code is what you want from them. Turn it on only for models built around an intended loop, such as interest charged on an average balance that includes the interest
One case that looks circular is not reported as a cycle. The lookup-array argument of VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP and XMATCH still makes the formula dirty when a cell in that range changes, but it does not count toward cycle detection, so VLOOKUP(A1,B:B,1) written in column B evaluates normally. For real cycles, the formula evaluation tracer shows the reference chain that folds back on itself, one step at a time
Array formulas and multi-threaded passes
A legacy CSE array formula is one node for its whole anchored rectangle. The root formula is evaluated once per pass, its matrix is written into each member cell (a scalar result is broadcast across the rectangle), and a formula that references any cell of the rectangle, not just the top-left anchor, gets an edge from the root. Dynamic-array formulas are handled more cautiously: they run every pass, and when a spill changes size, Recalculate rebuilds the graph and repeats the pass until the spill shapes settle. If they never settle within 32 rounds, the call returns lxErrorCalc
RecalcThreads set above 1 evaluates each topological level of independent formulas across worker threads, with a separate calculator per thread. Data tables still run serially, and a workbook that contains dynamic arrays stays on the serial path. Any user function handler must be thread-safe before you raise this value; for a handler that touches shared state, keeping RecalcThreads at its default of 0 is the safer choice
The dependency graph, volatile handling and iterative calculation are part of the XLSX engine in HotXLS Delphi Excel Component, which also covers the formula calculator, defined names and the read and write pipeline around them. For Delphi and C++Builder applications that keep pricing sheets or consolidation workbooks open and feed them new inputs all day, the work per edit then follows the cells the edit actually reaches