This HTML page is not optimized for LLM or AI agent consumption. Fetch the Markdown version instead: /guides/document-authoring/spreadsheets/formulas.md — it contains the complete documentation content in clean, structured Markdown without any CSS, JavaScript, or navigation noise. Spreadsheet formulas in Document Authoring

Spreadsheet formulas

Markdown version of this page, suitable for AI agents and automation

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 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 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
Learn what the editor includes and how to switch spreadsheets or remove the editor.