Array

Array formulas can operate on ranges of cells, making them useful for performing complex calculations with a single operation that would otherwise require multiple steps or the use of "helper" cells and columns.

Simply type your formula and, if it generates multiple values, they will automatically spill over into adjacent cells marked by a purple outline (the spill range). To edit an array formula, select the master cell at the top or top-left of the spill range. Note that currently, you cannot nest an array formula within other formulas.

If you encounter a #SPILL!error, ensure that no cells in the spill range are blocked.

UNIQUE
FILTER
RANDARRAY
XLOOKUP
XMATCH
SORT
TEXTSPLIT

Date and time

DATE
DATEDIF
DATEVALUE
DAY
DAYS
EDATE
EOMONTH
HOUR
MINUTE
MONTH
NETWORKDAYS
NOW
SOWEEK
TIME
TIMEVALUE
TODAY
WEEKDAY
WEEKNUM
WORKDAY
YEAR
YEARFRAC

Financial

Information

CELL
TYPE

Logical

AND
FALSE
IF
IFERROR
IFNA
IFS
NOT
OR
TRUE
ISBLANK
ISERROR
ISNUMBER

Lookup

ADDRESS
CHOOSE
COLUMN
COLUMNS
HLOOKUP
INDEX
JSONLOOKUP
LOOKUP
MATCH
ROW
ROWS
SORT
SORTBY
VLOOKUP

Math

ABS
EVEN
FLOOR
INT
LOG
MOD
ODD
PRODUCT
QUOTIENT
RAND
RANDBETWEEN
ROUND
ROUNDDOWN
ROUNDUP
SQRT
SUM
SUMIF
SUMIFS
SUMPRODUCT
TRUNC

Statistical

AVERAGE
AVERAGEIF
AVERAGEIFS
CHISQ.TEST
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
COUNTUNIQUEIFS
FORECAST
MAX
MAXIFS
MEAN
MEDIAN
MEDIANIFS
MIN
MINIFS
NORM.DIST
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
STDEV
T.TEST

Text

CHAR
CLEAN
CONCAT
CONCATENATE
EXACT
FIND
LEFT
LEN
LOWER
REGEXMATCH
RIGHT
MID
SEARCH
SUBSTITUTE
TEXT
TEXTAFTER
TRIM
UPPER
VALUE