Native SheetsFeature
The formula engine: dependency-ordered recalculation
How Native Sheets parses, orders and recalculates formulas, what it does with circular references, and how references shift when rows and columns move.
Editing a cell recalculates exactly what depends on it, and nothing else. That is the whole design goal of the formula engine, and it is what keeps a large workbook responsive while you type in it.
Tokenizer, parser, evaluator
A hand-written tokenizer and a precedence-climbing parser turn formula text into a syntax tree. The evaluator walks that tree against the workbook, resolving arithmetic, comparison, string concatenation, ranges, absolute and relative references, cross-sheet references such as 'Results'!$A$1, and a library of 91 functions.
Dependency ordering
Each formula's precedents are collected at parse time, so the workbook knows which cells feed which. Recalculation evaluates cells in topological order, with cycle detection, and values are cached. Editing a cell dirties only its dependents.
A worked example. Given B1 = 4, C1 = B1 * 2 and D1 = C1 + B1, typing 10 into B1 marks C1 and D1 dirty, evaluates C1 first because D1 depends on it, and leaves every unrelated cell in the workbook untouched. A circular reference, such as A1 = A1 + 1, is detected and reported rather than looping.
References that move
Relative references shift when rows or columns are inserted or deleted, and when a formula is copied to another cell. Absolute references, written with $, do not. This is the behaviour a spreadsheet user expects, and it is applied to the stored formula text, so the change is visible in the file afterwards.
Measured
| Operation | Time |
|---|---|
| Open | 0.09 s |
| Save | 0.11 s |
| Recalculate everything | 0.41 s |
All 4,530 formulas in that workbook produced the same results as the values the file had already cached.
Prerequisites and limits
- Array formulas,
INDIRECT,OFFSETand defined names are not evaluated. Defined names survive a save. - A formula the engine does not evaluate keeps its cached value from the file, which will not update as its inputs change.
- Recalculation happens in the macOS app. The browser viewer on this site does not recalculate.
To check a specific workbook against the engine, run the opt-in test described in the article on formula coverage.
Related
Will my formulas work? The functions Native Sheets evaluates, and the ones it does not
The complete list of the 91 spreadsheet functions Native Sheets evaluates, what happens to a formula it cannot evaluate, and how to test your own workbook before you rely on it.
Round-trip fidelity: saving without rewriting the whole file
Native Sheets keeps the raw bytes of every part of an .xlsx package and regenerates only the parts it models, so charts, pivot caches and themes survive a save.
XLSX viewer: open a spreadsheet in your browser
Open an .xlsx workbook in this browser tab, read every sheet, see the formula behind a cell, and export a sheet as CSV. The file is parsed on your device and is never uploaded.