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
- MONTHSTO
- MONTHSFROM
- NETWORKDAYS
- NOW
- SOWEEK
- SOMONTH
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- WEEKNUM
- WORKDAY
- WEEKSTO
- WEEKSFROM
- YEAR
- YEARFRAC
Financial
- IRR
- NPV
- PMT
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
- SWITCH
- 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
- HYPERLINK
- LEFT
- LEN
- LOWER
- REGEXMATCH
- REGEXEXTRACT
- RIGHT
- MID
- SEARCH
- SUBSTITUTE
- TEXT
- TEXTAFTER
- TRIM
- UPPER
- VALUE
Saved queries Keyboard shortcuts