--- title: "Spreadsheet formulas in Document Authoring" canonical_url: "https://www.nutrient.io/guides/document-authoring/spreadsheets/formulas/" md_url: "https://www.nutrient.io/guides/document-authoring/spreadsheets/formulas.md" last_updated: "2026-10-14T00:00:00.000Z" description: "Which formulas Document Authoring calculates in spreadsheets, how it handles other functions, and how references change when users edit the structure." --- # Spreadsheet formulas The Document Authoring spreadsheet editor calculates six functions. It keeps other formulas in the file but doesn’t recalculate them. This guide explains when formulas update and how the editor handles unsupported formulas. Before you start, install and run the Document Authoring library in your app. If you haven’t set it up, refer to the [getting started](https://www.nutrient.io/guides/document-authoring/spreadsheets/getting-started.md) guide. ## Functions that calculate Document Authoring calculates these functions: - `SUM` - `AVERAGE` - `MIN` - `MAX` - `COUNT` - `COUNTA` Formulas can also use: - Numbers, text in double quotes, and `TRUE` and `FALSE`. - Cell references such as `A1`, absolute references such as `$A$1`, and ranges such as `A1:B10` as function arguments. - References to other sheets, such as `Sheet2!A1` or `'Q3 forecast'!A1`. Sheet names aren’t case-sensitive. - Arithmetic operators: `+`, `-`, `*`, `/`, `^`, and `%` after a value, plus `-` and `+` before a value. - Parentheses to group parts of a formula. - Comparison operators: `=`, `<>`, `<`, `>`, `<=`, and `>=`. - The `&` operator to join text. When a user types a formula, the editor suggests matching functions and displays their arguments. ## Recalculation When a user changes a cell, Document Authoring recalculates every dependent formula across all sheets. The edit and recalculation count as one undo step. Document Authoring doesn’t recalculate formulas when it opens a file, so each formula shows its stored result until an input changes. ## Error values Calculated formulas return `#DIV/0!`, `#VALUE!`, `#NUM!`, and `#REF!`. A reference to a sheet that doesn’t exist returns `#REF!`. Formulas can also contain and pass on `#NULL!`, `#NAME?`, and `#N/A`, but the calculation never creates them. Functions outside the list above don’t return `#NAME?`. For their behavior, refer to the [other functions](#other-functions) section. A formula that depends on itself, directly or through other cells, returns `#CIRC!`. Excel doesn’t have this error value. ## Other functions Formulas that use any other function, such as `VLOOKUP` or `IF`, behave like this: - **In an imported file**, the formula keeps its text and the result stored in the file. The result doesn’t update when its inputs change. - **In a formula a user types**, the cell stays blank, because Document Authoring has no result to show. A formula that refers to that cell returns `#VALUE!`. - **In an export**, Document Authoring writes the formula text and its stored result to the XLSX file. The same behavior applies to formulas that use names, whole-column or whole-row references such as `A:A`, and ranges outside function arguments, such as `=A1:B2`. Document Authoring keeps the formula text and stored result but doesn’t recalculate the formula. Names in formula text are separate from defined-name definitions. Document Authoring preserves names in formulas as described above, but it doesn’t import or export defined-name definitions. It also doesn’t support table references or array formulas, and it doesn’t import or export tables. For an array formula, it reads the first cell as a regular formula and the remaining cells as values. Formulas that refer to another file or a range of sheets, such as `Sheet1:Sheet3!A1`, keep their text and stored result. While the spreadsheet contains one, the editor blocks row or column insertion and deletion, and sheet renaming or deletion. Fix or remove the formula before making these changes. ## Structure changes When users insert or delete rows or columns, Document Authoring updates formula references to point to the same cells. References to deleted rows or columns become `#REF!`. Renaming a sheet updates references to it, while deleting a sheet changes those references to `#REF!`. The editor blocks structure changes that would push a reference or formula past the sheet’s edge. It also blocks changes that could affect a formula it can’t read. ## Typing values The editor reads typed values using US English conventions: - A value that starts with `=` is a formula. - A value that starts with `'` is text, even if it looks like a number. - Numbers can use `,` as a thousands separator and `.` as the decimal separator. - Percentages such as `50%` and dollar amounts such as `$1,200` get matching number formats. - Dates such as `10/12/2026` or `2026-10-12`, and times such as `9:30 am`, also get matching number formats. - `TRUE` and `FALSE` in capital letters are Boolean values. Cells formatted as text keep what the user types as text, except a value that starts with `=`, which is still a formula. ## Learn more **[Editor](https://www.nutrient.io/guides/document-authoring/spreadsheets/editor.md)**\ Learn what the editor includes and how to switch spreadsheets or remove the editor. --- ## Related pages - [Spreadsheets](/guides/document-authoring/spreadsheets.md) - [Spreadsheet editor](/guides/document-authoring/spreadsheets/editor.md) - [Get started with spreadsheets](/guides/document-authoring/spreadsheets/getting-started.md) - [Import and load spreadsheets](/guides/document-authoring/spreadsheets/import-and-load.md) - [Spreadsheets in Node.js](/guides/document-authoring/spreadsheets/nodejs.md) - [Save and export spreadsheets](/guides/document-authoring/spreadsheets/save-and-export.md) - [Spreadsheet JSON support with SheetJSON](/guides/document-authoring/spreadsheets/sheetjson.md)