Google Sheets functions with live examples

Select a block of numbers in Google Sheets and the status area at the bottom right shows their sum without any formula.

Google Sheets also has QUERY, which can total by group in one formula, for example a select with sum and group by clauses.

SUMPRODUCT works on arrays natively in Google Sheets, so it needs neither ARRAYFORMULA nor a special key combination.

In Google Sheets the bounds of RANDBETWEEN are both inclusive, and the value changes whenever the sheet recalculates.

RAND in Google Sheets is volatile: it gives a new number on every recalculation, and the Calculation setting under File, Settings decides whether that also happens every minute or every hour.

COUNTUNIQUE is a Google Sheets function with no Excel counterpart of the same name, and it counts distinct values in one step.

To apply IF to every row from one cell in Google Sheets, wrap it: ARRAYFORMULA(IF(B2:B10>=70,"Pass","Fail")).

AND returns a single TRUE or FALSE for a range even inside ARRAYFORMULA, so it cannot test row by row.

OR also collapses a range into a single answer in Google Sheets, even when it sits inside ARRAYFORMULA.

In Google Sheets the second argument of IFERROR is optional, and leaving it out returns a blank instead of an error.

TEXTJOIN accepts arrays in Google Sheets, so you can feed it the result of FILTER, UNIQUE or ARRAYFORMULA directly.

In Google Sheets, LEN over a range returns a single number unless it is wrapped in ARRAYFORMULA, which gives one length per row.

Google Sheets has SPLIT, which cuts text at a delimiter into separate cells and often replaces a FIND and MID combination.

CHAR in Google Sheets accepts Unicode code points beyond 255, so CHAR(8364) returns the euro sign.

UPPER handles one cell at a time, so ARRAYFORMULA(UPPER(A2:A)) is the Google Sheets way to convert the entire column from one formula.

TEXT format codes in Google Sheets are read according to the spreadsheet locale (File, Settings), which affects separators and month names.

Google Sheets counts date serial numbers from 30 December 1899, so serial 0 is that day, while Excel shows its serial 0 as 0 January 1900.

TODAY follows the time zone of the spreadsheet (File, Settings), not the time zone of the person who opens it.

NOW returns the current date and time in the time zone set under File, Settings, General.

DATEDIF is documented in Google Sheets and appears in its function help, unlike Excel where it is a hidden legacy function.

NETWORKDAYS counts Monday to Friday; for a different weekend pattern Google Sheets offers NETWORKDAYS.INTL.

WORKDAY skips Saturday and Sunday only; Google Sheets has WORKDAY.INTL for other weekends.

In Google Sheets the table of VLOOKUP can come from another file: wrap the source in IMPORTRANGE with its URL and range as the second argument.

XLOOKUP is built into Google Sheets, with the same argument order as the Excel version shown here.

Google Sheets INDIRECT takes an optional second argument: FALSE makes the text be read as an R1C1 reference.

TRANSPOSE spills its result in Google Sheets without ARRAYFORMULA, swapping rows and columns of a range.

UNIQUE spills its result natively in Google Sheets, so one formula in the top cell fills the cells below it.

SEQUENCE fills a block of cells from one formula in Google Sheets with no ARRAYFORMULA wrapper.

LET is supported in Google Sheets, where it can name an intermediate result such as a tax rate or a total.

Google Sheets supports LAMBDA together with the helper functions MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY.

REDUCE is a built-in helper in Google Sheets, and it returns a single value from the whole range.

SCAN returns all the running results as an array, which spills natively in Google Sheets.

BYROW passes each row of a range to your LAMBDA in Google Sheets and returns one value per row.

BYCOL hands each column of a range to your LAMBDA in Google Sheets and gives back a row of results.

Google Sheets REGEXREPLACE takes exactly three arguments: the text, the regular expression and the replacement.

ISBLANK is FALSE for a cell whose formula returns an empty string, although the cell looks empty.