Google SheetsGoogle Sheets formulas cheat sheet
The syntax of every function on this site on one page, with QUERY clauses and regular-expression symbols at the top. Tap a function for its page. Arguments in square brackets are optional.
How to read this sheet
Each line in the function tables shows a function as Sheets' own help box shows it: the name, then the arguments in order. Square brackets mark an argument you may leave out. Three dots mean more of the same may follow. The sheet is a reminder for people who have met the function already. If a line is not clear, tap the name and read its page, which shows the function at work on a sample sheet.
If your spreadsheet's locale uses a comma as the decimal mark, read every comma between arguments as a semicolon.
Ranges and references
| Write | Meaning |
|---|---|
A2:A10 | a block of cells |
A2:A | from A2 to the bottom of the sheet |
A:A | the whole column |
Prices!B2 | a cell on another tab |
'Price List'!B2 | a tab whose name has spaces |
$A$1 | fixed when copied (F4 adds the dollar signs) |
{A2:A5; C2:C5} | two ranges stacked, one above the other |
{A2:A5, C2:C5} | two ranges side by side |
QUERY clauses, in order
| Clause | Example |
|---|---|
select | select B, sum(F) |
where | where C = 'North' and F > 1000 |
group by | group by B |
pivot | pivot D |
order by | order by sum(F) desc |
limit | limit 5 |
offset | offset 10 |
label | label sum(F) 'Total' |
format | format F '#,##0' |
Text in single quotation marks. Dates as date '2026-03-01'. Imported data: Col1, Col2. Full guide: the QUERY language.
Regular expression symbols
| Symbol | Matches |
|---|---|
. | any character |
\d \w \s | digit; letter, digit or underscore; space |
[abc] [^abc] | one of these; none of these |
^ $ | start; end |
* + ? | none or more; one or more; none or one |
{3} {2,5} | exactly three; two to five |
( ) | a group, returned by REGEXEXTRACT and reused as $1 in REGEXREPLACE |
(?i) | at the start: ignore case |
\. | a real dot |
Conditions in SUMIF and COUNTIF
| Condition | Meaning |
|---|---|
"North" | equal to the text |
">100" | greater than 100 |
"<>" | not empty |
">="&B1 | at least the value in B1 |
"*kg*" | contains kg |
Error values
| Error | Usual meaning |
|---|---|
#N/A | not found; or FILTER matched nothing |
#REF! | a spilling result is blocked; a deleted reference; IMPORTRANGE needs access |
#VALUE! | wrong kind of data; QUERY text cannot be read |
#DIV/0! | division by zero |
#NAME? | unknown function or name |
#ERROR! | the formula cannot be read: brackets, quotation marks or separators |
Keyboard shortcuts
| Keys | Action |
|---|---|
| F2 | edit the cell |
| F4 | cycle dollar signs on a reference |
| Ctrl, Shift and Enter | wrap the formula in ARRAYFORMULA |
| Ctrl and ` | show formulas |
| Ctrl and Enter | fill the selected range with the formula |
| Ctrl and D | fill down |
| Ctrl and ; | insert today's date |
| Ctrl and H | find and replace |
| Ctrl and / | show all shortcuts |
On a Mac, use Cmd in place of Ctrl. The full list is in Sheets under Help, Keyboard shortcuts.
Only in Sheets
=QUERY(data, query, [headers]) | QUERY selects, filters, sorts and summarises a range using a short query written in words, much like SQL |
=ARRAYFORMULA(array_formula) | ARRAYFORMULA makes an ordinary formula work on a whole range at once, filling a column from a single cell |
=IMPORTRANGE(spreadsheet_url, range_string) | IMPORTRANGE brings a range of cells from another Google Sheets file into this one, and keeps it up to date |
=FILTER(range, condition1, [condition2], ...) | FILTER returns the rows of a range that meet one or more conditions |
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2], ...) | SORT returns a range in sorted order; SORTN returns only the first few rows after sorting |
=SORTN(range, [n], [display_ties_mode], [sort_column, is_ascending], ...) | SORT returns a range in sorted order; SORTN returns only the first few rows after sorting |
=UNIQUE(range, [by_column], [exactly_once]) | UNIQUE returns each different value in a range once; COUNTUNIQUE counts how many different values there are |
=COUNTUNIQUE(value1, [value2], ...) | UNIQUE returns each different value in a range once; COUNTUNIQUE counts how many different values there are |
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) | SPLIT divides a text into separate cells at a chosen character; JOIN puts the values of a range together with a separator |
=JOIN(delimiter, value_or_array1, [value_or_array2], ...) | SPLIT divides a text into separate cells at a chosen character; JOIN puts the values of a range together with a separator |
=FLATTEN(range1, [range2], ...) | FLATTEN turns a block of cells, or several ranges, into a single column |
=SPARKLINE(data, [options]) | SPARKLINE draws a tiny chart inside a single cell from a range of numbers |
Lookup
=VLOOKUP(search_key, range, index, [is_sorted]) | VLOOKUP finds a value in the first column of a range and returns a value from the same row in another column |
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) | XLOOKUP finds a value in one range and returns the matching item from another, in any direction |
=INDEX(reference, [row], [column]) | INDEX returns the cell at a position in a range and MATCH finds the position of a value; together they make a flexible lookup |
=MATCH(search_key, range, [search_type]) | INDEX returns the cell at a position in a range and MATCH finds the position of a value; together they make a flexible lookup |
Logical
=IF(logical_expression, value_if_true, value_if_false) | IF returns one value when a condition is true and another when it is false; IFS checks several conditions in turn |
=IFS(condition1, value1, [condition2, value2], ...) | IF returns one value when a condition is true and another when it is false; IFS checks several conditions in turn |
=IFERROR(value, [value_if_error]) | IFERROR returns a value of your choice when a formula gives an error; IFNA does so only for the #N/A error |
=IFNA(value, value_if_na) | IFERROR returns a value of your choice when a formula gives an error; IFNA does so only for the #N/A error |
Math and counting
=SUMIF(range, criterion, [sum_range]) | SUMIF adds the cells that meet one condition; SUMIFS adds the cells that meet several |
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...) | SUMIF adds the cells that meet one condition; SUMIFS adds the cells that meet several |
=COUNTIF(range, criterion) | COUNTIF counts the cells that meet one condition; COUNTIFS counts the rows that meet several |
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...) | COUNTIF counts the cells that meet one condition; COUNTIFS counts the rows that meet several |
Text
=REGEXMATCH(text, regular_expression) | REGEXMATCH tests whether a text matches a pattern and returns TRUE or FALSE |
=REGEXEXTRACT(text, regular_expression) | REGEXEXTRACT pulls out the part of a text that matches a pattern |
=REGEXREPLACE(text, regular_expression, replacement) | REGEXREPLACE replaces every part of a text that matches a pattern with other text |
=ISEMAIL(value) | ISEMAIL, ISURL and ISDATE test whether a value has the form of an email address, a web address or a date |
=ISURL(value) | ISEMAIL, ISURL and ISDATE test whether a value has the form of an email address, a web address or a date |
=ISDATE(value) | ISEMAIL, ISURL and ISDATE test whether a value has the form of an email address, a web address or a date |
=TEXT(number, format) | TEXT turns a number or a date into text in the format you choose |
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) | TEXTJOIN joins values with a separator and can skip empty cells; CONCATENATE and the & sign join values end to end |
=CONCATENATE(string1, [string2], ...) | TEXTJOIN joins values with a separator and can skip empty cells; CONCATENATE and the & sign join values end to end |
=CONCAT(value1, value2) | TEXTJOIN joins values with a separator and can skip empty cells; CONCATENATE and the & sign join values end to end |
Date and time
=DATEDIF(start_date, end_date, unit) | DATEDIF returns the number of complete years, months or days between two dates |
Web and import
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]) | GOOGLEFINANCE fetches current or past prices of shares and currencies from Google Finance |
=GOOGLETRANSLATE(text, [source_language], [target_language]) | GOOGLETRANSLATE translates text from one language into another; DETECTLANGUAGE reports which language a text is in |
=DETECTLANGUAGE(text_or_range) | GOOGLETRANSLATE translates text from one language into another; DETECTLANGUAGE reports which language a text is in |
=IMAGE(url, [mode], [height], [width]) | IMAGE shows a picture from a web address inside a cell |
=IMPORTHTML(url, query, index) | IMPORTHTML brings a table or list from a web page into the sheet; IMPORTXML and IMPORTDATA fetch other kinds of web data |
=IMPORTXML(url, xpath_query) | IMPORTHTML brings a table or list from a web page into the sheet; IMPORTXML and IMPORTDATA fetch other kinds of web data |
=IMPORTDATA(url) | IMPORTHTML brings a table or list from a web page into the sheet; IMPORTXML and IMPORTDATA fetch other kinds of web data |
This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.