Every formula you know, and the new ones too

Familiar names and arguments

Functions keep the names, argument order and error values that spreadsheet users already expect, so a formula you type from memory works the first time. Autocomplete shows the signature and a short description as you type, and every function has a reference page with worked examples.

• Math, statistics, text, dates, lookups, finance, engineering and database functions
• Error values like #N/A, #DIV/0! and #SPILL! behave the way you expect
• Compatibility names such as STDEV and PERCENTILE still work in older workbooks

Dynamic arrays that spill

Formulas that return several values fill the cells next to them automatically. Sort a list with SORT, keep matching rows with FILTER, remove duplicates with UNIQUE or stack ranges with VSTACK, and the result grows and shrinks with your data.

• FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TAKE, DROP, CHOOSECOLS
• TEXTSPLIT, VSTACK, HSTACK, TOCOL, WRAPROWS
• GROUPBY and PIVOTBY for summaries in a single formula

LET and LAMBDA for formulas you can read

Name parts of a long calculation with LET so it reads like a sentence, and wrap logic in LAMBDA to reuse it. MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY apply a LAMBDA across ranges without helper columns.

• Save a LAMBDA as a named function and call it anywhere in the workbook
• Iterative calculation for models that refer back to themselves
• Volatile functions like TODAY and RAND recalculate on every change

Tables, names and references

Format a range as a table and refer to its columns by name, like Sales[Revenue]. Named ranges and absolute references keep large models readable, and Show formulas reveals every formula in the sheet at once.