Formulas and functions

The engine

Formulas are parsed and compiled once, then recalculated through a dependency graph, so changing a cell only recalculates what depends on it. Calculation runs in short time slices, which keeps typing and scrolling smooth while a large workbook recalculates.

Automatic and manual calculation are available as in Excel, and iterative calculation can resolve circular references. A workbook keeps the calculation settings it was saved with.

Functions

8sheets has 502 of Excel's worksheet functions, among them:

  • Dynamic arrays: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY, spilled ranges and the # spill operator.
  • Lookups: XLOOKUP, XMATCH, INDEX, MATCH, VLOOKUP, HLOOKUP.
  • Functions as values: LET, LAMBDA, MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY, and named LAMBDA functions in the Name Manager.
  • Aggregation: GROUPBY and PIVOTBY.
  • Text and arrays: TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, TOCOL and more.
  • The financial, statistical, engineering, date and time, database and information families.

Results are compared with the values real Excel computes for the same formulas. A few functions are known to differ: FORECAST.ETS with a seasonal pattern can differ from Excel by a few percent, because Excel does not publish its starting values.

Use Formulas > Insert Function (Shift+F3) to browse functions with their arguments and descriptions. While you type, the formula bar suggests function names and shows the arguments of the function you are in.

Structured references and names

Table references such as Sales[Amount] and Sales[@Amount], defined names, and names scoped to a sheet all work as in Excel. Formulas > Name Manager lists and edits them.

Formula auditing

  • Trace Precedents and Trace Dependents draw arrows between cells.
  • Evaluate Formula steps through a calculation.
  • Watch Window keeps an eye on cells on other sheets.
  • Show Formulas (Ctrl+`) shows formulas instead of results.

Formula languages

Formulas are always stored in the file in English, as Excel stores them, so a workbook works the same everywhere. You can choose to see and type them with localized function names under Formulas > Formula Language: Chinese, Czech, Danish, Dutch, Finnish, French, German, Hungarian, Italian, Japanese, Korean, Norwegian, Polish, Portuguese (Brazil and Portugal), Russian, Spanish, Swedish and Turkish.

Polish has the most complete list. Where a localized name hasn't been confirmed from Microsoft's published lists, the English name is used.

Regional settings, such as the list and decimal separators, follow File > Region.