Use formulas and functions in a spreadsheet

A formula starts with =. Type it in a cell or in the formula bar above the grid (fx), which shows a cell's formula rather than its result.

What a formula can contain

Numbers, text in double quotes, TRUE and FALSE, parentheses, arithmetic with +, -, *, /, ^ and %, comparisons, & to join text, cell and range references such as B2 and B2:B11, whole columns and rows such as A:A and 2:2, references to another sheet such as Sheet2!C7 or 'Annual Budget'!B7, named ranges, table columns such as Sales[Revenue], array literals, and the functions below.

=SUM(B2:B11)
=A2*B2
=Sheet2!C7
=IF(C2>100,"Large","Small")

References may be relative, absolute or mixed: A1, $A$1, A$1 and $A1. A copied or filled formula adjusts its relative references to its new place.

As you type a function's name, autocomplete shows its syntax and what it does. Inside the parentheses it shows the syntax with the argument you are typing in bold; arguments in square brackets may be left out. While you edit a formula, the cells it refers to are outlined on the sheet.

Functions

• Math: SUM, SUMPRODUCT, SUMSQ, PRODUCT, ABS, SIGN, INT, TRUNC, ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, MOD, POWER, SQRT, EXP, LN, LOG, LOG10, PI, SIN, COS, TAN, ISEVEN, and ISODD.
• Random: RAND and RANDBETWEEN.
• Statistics: AVERAGE, AVERAGEA, MEDIAN, MODE, GEOMEAN, STDEV, VAR, CORREL, FORECAST, RANK, LARGE, SMALL, PERCENTILE, QUARTILE, MIN, MAX, COUNT, COUNTA, COUNTBLANK, and COUNTUNIQUE.
• Conditional aggregates: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, and MINIFS.
• Logic: IF, IFS, SWITCH, AND, OR, NOT, XOR, IFERROR, IFNA, and LET.
• Lookup and reference: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, XMATCH, LOOKUP, CHOOSE, ADDRESS, ROW, COLUMN, ROWS, COLUMNS, and ISFORMULA.
• Text: CONCAT, CONCATENATE, TEXTJOIN, TEXT, LEFT, RIGHT, MID, LEN, LOWER, UPPER, PROPER, TRIM, CLEAN, SUBSTITUTE, REPLACE, REPT, EXACT, FIND, SEARCH, CHAR, CODE, UNICODE, VALUE, and SPLIT.
• Dynamic arrays: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TRANSPOSE, FLATTEN, ARRAYFORMULA, VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, WRAPROWS, and WRAPCOLS.
• Lambda: LAMBDA, MAP, REDUCE, SCAN, BYROW, BYCOL, and MAKEARRAY.
• Financial: FV, PV, PMT, NPER, NPV, IRR, RATE, IPMT, PPMT, SLN, DB, DDB, EFFECT, and NOMINAL.
• Date and time: DATE, DATEVALUE, TIME, TIMEVALUE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, WEEKNUM, ISOWEEKNUM, DAYS, DATEDIF, YEARFRAC, EDATE, EOMONTH, WORKDAY, NETWORKDAYS, TODAY, and NOW.
• Value tests: ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, and TYPE.

How values are compared and counted

• Text is compared without regard to case: ="apple"="Apple" is TRUE, and VLOOKUP, MATCH, XLOOKUP, SWITCH, SORT and UNIQUE treat apple and Apple as the same value. EXACT compares case.
• Criteria for COUNTIF, SUMIF and the other conditional functions ignore case and accept * for any characters and ? for one character; write ~* or ~? to match a literal asterisk or question mark. SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS take several conditions, and each criteria range must be the same size as the range being added or compared. MAXIFS and MINIFS return 0 when nothing matches.
• XLOOKUP returns the matching value and can take a result for no match; XMATCH returns the position. Both match exactly unless told otherwise.
• Numeric text given directly to a function or an operator counts as a number, so ="5"+1 is 6, while text inside a referenced range is skipped by numeric functions.
• SORT puts numbers before text, text before TRUE and FALSE, and blanks last. Text functions count characters, so LEN("😀") is 1.
• A:A covers the column down to its last filled cell, and 2:2 the row across to its last filled cell, so SUM(A:A) adds the column.

Text, dates and money

TEXT formats a number as text. It accepts the cell number formats (number, integer, currency, percentage and date), digit patterns such as 0, 0.00, #,##0.00 and 0%, and date patterns built from yyyy, yy, mmmm, mmm, mm, m, dddd, ddd, dd, d, hh, h, ss and s, such as yyyy-mm-dd or d mmm yyyy hh:mm. Month and weekday names use the workbook's language. An unsupported pattern returns #VALUE!.

Date text must be an ISO date such as 2026-08-23; a date such as 03/04/2026 is not guessed. DATEDIF takes the units Y, M, D, MD, YM and YD. WEEKNUM counts weeks from Sunday; type 2 starts on Monday and type 21 gives ISO weeks. YEARFRAC takes the day-count bases 0 (US 30/360), 1 (actual/actual), 2 (actual/360), 3 (actual/365) and 4 (European 30/360). WORKDAY and NETWORKDAYS take an optional range of holiday dates. Apply the Date number format to show a date result as a date.

TODAY, NOW, RAND and RANDBETWEEN change when a change to the workbook is saved, not while you type. TODAY and NOW use the workbook's time zone. Everyone looking at the same saved version sees the same random numbers.

In the financial functions, money paid out and money received have opposite signs, and rates are per period: a monthly loan payment divides an annual rate by 12. NPV and IRR treat cash flows as equally spaced. SLN, DB and DDB give one period's depreciation; EFFECT and NOMINAL convert between effective and nominal annual rates.

LET and LAMBDA

LET names values inside one formula: =LET(Net, B2-C2, Tax, Net*0.13, Net+Tax). LAMBDA makes a calculation that MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY apply to each value: =MAP(A2:A10,LAMBDA(V,V*2)). Names made with LET or LAMBDA apply only inside that formula.

Results that fill several cells

Some formulas return more than one value: =SEQUENCE(10) fills ten rows, and =FILTER(A2:C100,C2:C100="Active") returns every matching row. The formula is in its first cell, and its results fill the cells beside and below it. Edit or delete the formula in that first cell; the other cells cannot be edited or pasted over. If something is in the way, the formula shows #SPILL! until you move or clear it.

Array literals use commas between columns and semicolons between rows: ={1,2;3,4} makes two rows of two values. VSTACK, HSTACK, WRAPROWS and WRAPCOLS fill gaps with #N/A unless given a pad value. TAKE, DROP, CHOOSECOLS and CHOOSEROWS accept negative positions counted from the end. TOCOL and TOROW can skip blanks, errors or both.

Named ranges

Select a cell or range, choose Data → Named ranges, enter a name such as TaxRate, and select Use current selection. A name starts with a letter or underscore, may contain letters, numbers, underscores and periods, and cannot look like a cell address. Use it in formulas, as in =Revenue*TaxRate. Open Named ranges to go to, change or delete a name, or type a name in the box above the grid and press Enter to select it. A workbook can have up to 500 named ranges.

Named functions

Choose Data → Named functions to save a calculation you repeat. Enter a Function name, the Parameters separated by commas, and a Formula definition, then select Save named function. For example, a function PRICE_AFTER_DISCOUNT with parameters PRICE, DISCOUNT and the definition =PRICE*(1-DISCOUNT) is used as =PRICE_AFTER_DISCOUNT(B2,C2) on any sheet, and autocomplete lists it. A name cannot be a built-in function's, a function can have up to 20 parameters, and a workbook up to 100 named functions. Cell references in the definition without a sheet name refer to the sheet that was open when you saved it.

Errors

An error in a referenced range is carried into results that use it, rather than treated as blank. A formula that refers to a whole row or column containing itself is circular.

When rows or columns are inserted or deleted, formulas keep pointing at the same cells. Deleting a row or column at the edge of a range shrinks the range: deleting row 2 changes =SUM(A2:A10) to cover what is left. Renaming a sheet, named range or table updates the formulas that use it. In a large workbook a formula may show … for a moment while it is calculated.

If a formula gives a result you cannot explain, Contact support.