Native SheetsDecision
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.
Native Sheets evaluates 91 function names. If your workbook stays inside that list, editing a cell recalculates everything that depends on it, in dependency order. If it uses something outside the list, that is worth knowing before you commit to the app, so the whole list is below rather than a marketing summary of it.
The one-line answer
Common arithmetic, aggregation, lookup, text and date work is covered. Array formulas, INDIRECT, OFFSET and defined names are not evaluated, and neither are newer dynamic-array functions such as XLOOKUP or FILTER.
The functions that are evaluated
| Area | Functions |
|---|---|
| Maths | ABS, CEILING, EXP, FLOOR, INT, LN, LOG, LOG10, MOD, PI, POWER, PRODUCT, RAND, RANDBETWEEN, ROUND, ROUNDDOWN, ROUNDUP, SIGN, SQRT, SUM, SUMIF, SUMIFS, TRUNC |
| Statistics | AVERAGE, AVERAGEIF, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, LARGE, MAX, MEDIAN, MIN, STDEV |
| Logic and information | AND, FALSE, IF, IFERROR, IFNA, IFS, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNUMBER, ISTEXT, NA, NOT, OR, SWITCH, TRUE, XOR |
| Text | CHAR, CODE, CONCAT, EXACT, FIND, LEFT, LEN, LOWER, MID, PROPER, REPLACE, REPT, SUBSTITUTE, TEXT, TEXTJOIN, TRIM, UPPER, VALUE |
| Lookup and reference | CHOOSE, COLUMN, HLOOKUP, INDEX, MATCH, ROW, ROWS, VLOOKUP |
| Dates and time | DATE, DAY, DAYS, EDATE, HOUR, MINUTE, MONTH, NOW, SECOND, TODAY, YEAR |
Alongside the functions, the engine handles arithmetic and comparison operators, string concatenation with &, ranges, absolute and relative addressing, cross-sheet references such as 'Results'!$A$1, and wildcard criteria in COUNTIF and its relatives.
What is not evaluated
- Array formulas. Not evaluated.
- INDIRECT and OFFSET. Not evaluated. Both build a reference at runtime, which the dependency graph is not designed to follow.
- Defined names. Not evaluated, although they survive a save: a workbook that uses them keeps them, the formulas that reference them just do not recompute.
- Anything outside the table above. Including XLOOKUP, SUMPRODUCT, FILTER, SEQUENCE, UNIQUE, SMALL, the financial functions, and COLUMNS (ROWS is present, COLUMNS is not).
What happens to a formula it cannot evaluate
The formula text is part of the cell and is preserved through a save, so an unsupported formula is not destroyed by opening and saving the workbook. What you lose is recalculation: the cell keeps showing the value cached in the file by whichever application last computed it, and that value will not update when its inputs change in Native Sheets.
That is a real hazard if you are editing inputs to an unsupported formula, because a stale number does not look stale. Circular references are a different case and are handled deliberately: they are detected and reported rather than looping.
Testing your own workbook
The repository ships an opt-in test that recomputes every formula in a workbook you point it at and compares the results against the values already stored in the file. It is the most direct answer to "does this handle my spreadsheet":
xcodebuild build-for-testing -scheme "Native Sheets" -derivedDataPath build/DerivedData
XLSX_CHECK_SOURCE=~/book.xlsx xcrun xctest -XCTest XLSXKitTests.ExternalWorkbookTests \
build/DerivedData/Build/Products/Debug/XLSXKitTests.xctestThe same command also runs a round-trip check: it reads the file, writes it, reads it back, and compares every cell, formula, style, merge and frozen pane. On the project's sample workbook, a 1.6 MB four-sheet tracker, all 4,530 formulas matched the values the file had cached, and recalculating the whole workbook took 0.41 seconds in a release build.
If you would rather not build anything first, the browser viewer shows the formula behind each cell, which is enough to find out which functions a workbook actually uses before you decide. How the engine recalculates covers the dependency ordering in more detail.
Related
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.
Why charts and pivot tables disappear when you edit someone else's workbook
What is actually inside an .xlsx file, why a save in the wrong app strips charts, pivot caches and conditional formatting, and how to check whether your own workflow does it.
How to open and edit an .xlsx file on a Mac without Excel
Four ways to open a .xlsx workbook on macOS without a Microsoft 365 subscription, what each one changes about the file, and how to check a workbook in the browser first.