Sheetsdrill / Cheat sheet

Google Sheets formulas cheat sheet

A hundred formulas in ten groups with the syntax you actually type, the Sheets-only functions Excel users miss (QUERY, ARRAYFORMULA, REGEXEXTRACT, IMPORTRANGE), and a drill for most of them. Print the page or get the PDF by email.

The ones worth learning first

What	How
Add a column	`=SUM(B2:B)`
Add the rows that match	`=SUMIFS(C:C, A:A, "North", B:B, ">100")`
Look something up	`=XLOOKUP(E2, A:A, C:C, "Not found")`
Keep the rows that match	`=FILTER(A2:C, B2:B>100)`
One formula for a whole column	`=ARRAYFORMULA(B2:B*C2:C)`
SQL-style summary	`=QUERY(A1:D, "select A, sum(C) where B > 100 group by A")`
Distinct values	`=UNIQUE(A2:A)`
Hide an error	`=IFERROR(A2/B2, 0)`
Pull from another file	`=IMPORTRANGE("url", "Sheet1!A1:D")`
Lock a reference	`$B$1 (F4 while editing)`

Totals and counts

Sum a column to the bottomOpen-ended ranges (B2:B) grow with the data.	`=SUM(B2:B)`
Average, min, max	`=AVERAGE(B2:B) =MIN(B2:B) =MAX(B2:B)` drill
Count numbers / non-empty	`=COUNT(B2:B) =COUNTA(A2:A)` drill
Weighted total	`=SUMPRODUCT(B2:B, C2:C)` drill
Average order value	`=SUM(C2:C)/COUNTA(A2:A)` drill
Unit price	`=B2/C2` drill
Share of total	`=B2/SUM($B$2:$B)` drill
Month over month	`=(C2-B2)/B2` drill
Convert with a rate	`=B2*$F$1` drill
Round	`=ROUND(B2, 2) =ROUNDUP(B2, 0)`
Median, mode	`=MEDIAN(B2:B) =MODE(B2:B)`
Running total	`=ARRAYFORMULA(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B))`

Conditions

If	`=IF(B2>C2, "Over", "OK")` drill
Several tiers	`=IFS(B2>=1000, "Gold", B2>=500, "Silver", TRUE, "Bronze")` drill
And / or	`=IF(AND(B2>100, C2="Won"), B2*0.1, 0)` drill
Switch on a value	`=SWITCH(A2, "US", "Americas", "DE", "Europe", "Other")` drill
Discount if over threshold	`=IF(B2>=100, B2*0.9, B2)` drill
Commission	`=B2*IF(B2>5000, 0.05, 0.03)` drill
Count if	`=COUNTIF(A:A, "North") =COUNTIFS(A:A, "North", B:B, ">100")` drill
Sum if	`=SUMIF(A:A, "North", C:C) =SUMIFS(C:C, A:A, "North", B:B, ">100")` drill
Average if	`=AVERAGEIF(A:A, "North", C:C)` drill
Max if	`=MAXIFS(C:C, A:A, "North") =MINIFS(C:C, A:A, "North")` drill
Rate above a threshold	`=COUNTIF(B:B, ">0.5")/COUNTA(B:B)` drill

Lookups

XLOOKUP	`=XLOOKUP(E2, A:A, C:C, "Not found")` drill
VLOOKUP exact	`=VLOOKUP(E2, A:C, 3, FALSE)` drill
INDEX and MATCH	`=INDEX(C:C, MATCH(E2, A:A, 0))` drill
Lookup a column by header	`=INDEX(A:F, MATCH(E1, A:A, 0), MATCH(F1, 1:1, 0))` drill
Two-way grid lookup	`=INDEX(B2:F10, MATCH(H1, A2:A10, 0), MATCH(H2, B1:F1, 0))` drill
Rate tier (approximate match)Tier table sorted ascending.	`=VLOOKUP(B2, Tiers!A:B, 2, TRUE)` drill
Last match	`=XLOOKUP(E2, A:A, C:C, , 0, -1)` drill
If not found, points	`=IFNA(VLOOKUP(E2, A:B, 2, FALSE), 0)` drill
Lookup from another sheet	`=VLOOKUP(E2, Rates!A:B, 2, FALSE)` drill
Lookup whole column at once	`=ARRAYFORMULA(IFNA(VLOOKUP(E2:E, A:C, 3, FALSE)))`
Horizontal	`=HLOOKUP(E1, A1:F5, 3, FALSE)`

FILTER, SORT, UNIQUE

Filter rows	`=FILTER(A2:C, B2:B>100)` drill
Two conditions (and)	`=FILTER(A2:C, B2:B>100, C2:C="Won")` drill
Either condition (or)	`=FILTER(A2:C, (B2:B>100)+(C2:C="Won"))`
Sum of a filter	`=SUM(FILTER(C2:C, A2:A="North"))`
Unique values	`=UNIQUE(A2:A)` drill
Count unique	`=COUNTUNIQUE(A2:A)`
Sort by a columnFALSE = descending.	`=SORT(A2:C, 3, FALSE)` drill
Top 3Or =SORTN(A2:C, 3, 0, 3, FALSE).	`=ARRAY_CONSTRAIN(SORT(A2:C, 3, FALSE), 3, 3)` drill
Sort and dedupe	`=SORT(UNIQUE(A2:A))`
Rows where text contains	`=FILTER(A2:C, REGEXMATCH(A2:A, "Pro"))`
Rows longer than	`=FILTER(A2:A, LEN(A2:A)>8)` drill
Sequence	`=SEQUENCE(12, 1, 1, 1)`

QUERY

Select and filter	`=QUERY(A1:D, "select A, C where C > 100")`
Sum per group	`=QUERY(A1:D, "select A, sum(C) group by A")` drill
Count per group	`=QUERY(A1:D, "select B, count(A) group by B")` drill
Total of one column	`=QUERY(A1:D, "select sum(C) where B = 'Won'")` drill
Sort and limit	`=QUERY(A1:D, "select A, C order by C desc limit 5")`
Text contains	`=QUERY(A1:D, "select * where A contains 'Pro'")`
Date filterDates need the date keyword and yyyy-mm-dd.	`=QUERY(A1:D, "select * where D >= date '2026-01-01'")`
Rename the output header	`=QUERY(A1:D, "select A, sum(C) group by A label sum(C) 'Revenue'")`
Cell value in the query	`=QUERY(A1:D, "select * where B = '"&F1&"'")`
Pivot	`=QUERY(A1:D, "select A, sum(C) group by A pivot B")`

ARRAYFORMULA

Multiply two columns	`=ARRAYFORMULA(B2:B*C2:C)` drill
IF for a whole columnThe empty branch keeps blank rows blank.	`=ARRAYFORMULA(IF(B2:B="", , B2:B*1.1))`
Join two columns	`=ARRAYFORMULA(A2:A&" "&B2:B)`
Lookup a whole column	`=ARRAYFORMULA(IFNA(XLOOKUP(E2:E, A:A, C:C)))`
Shortcut	`Type the formula, press Ctrl+Shift+Enter to wrap it in ARRAYFORMULA.`
Map over a range (new)	`=MAP(A2:A, LAMBDA(x, LEN(x)))`
Row by row	`=BYROW(B2:D, LAMBDA(r, SUM(r)))`
Reuse a value	`=LET(x, B2*C2, IF(x>100, x, 0))`

Text and regex

First nameOr =SPLIT(A2, " ") and take the first cell.	`=LEFT(A2, FIND(" ", A2)-1)` drill
Split	`=SPLIT(A2, ",")`
Join with a separator	`=TEXTJOIN(", ", TRUE, A2:A10)` drill
Join address parts	`=A2&", "&B2&" "&C2` drill
Domain of an email	`=REGEXEXTRACT(A2, "@(.+)$")` drill
Does it match a pattern	`=REGEXMATCH(A2, "^[A-Z]{3}-\d{4}$")` drill
Replace with regexKeeps only digits.	`=REGEXREPLACE(A2, "[^0-9]", "")` drill
Invoice number from text	`=REGEXEXTRACT(A2, "INV-\d+")` drill
Trim, proper, upper	`=TRIM(A2) =PROPER(A2) =UPPER(A2)`
Substitute	`=SUBSTITUTE(A2, "Ltd", "Limited")`
Length, position	`=LEN(A2) =FIND("@", A2)`
Number as text	`=TEXT(B2, "#,##0.00")`
Text to number	`=VALUE(A2)`
Email with a formula	`=LOWER(A2&"."&B2&"@company.com")` drill

Dates

Today, now	`=TODAY() =NOW()`
Month label	`=TEXT(A2, "mmm yyyy")` drill
Days between, tenure	`=B2-A2 =DATEDIF(A2, TODAY(), "y")` drill
Working days	`=NETWORKDAYS(A2, B2)` drill
Due date, add working days	`=A2+30 =WORKDAY(A2, 10)` drill
Add months, end of month	`=EDATE(A2, 3) =EOMONTH(A2, 0)`
Parts	`=YEAR(A2) =MONTH(A2) =DAY(A2) =WEEKDAY(A2)`
Build a date	`=DATE(2026, 12, 31)`
Text to date	`=DATEVALUE(A2)`
Week number	`=WEEKNUM(A2)`

Imports and sheets

Another sheet	`=Data!B2 ='Raw data'!B2`
Another fileAllow access once when prompted.	`=IMPORTRANGE("https://docs.google.com/spreadsheets/d/ID", "Sheet1!A1:D")`
Table from a web page	`=IMPORTHTML("url", "table", 1)`
CSV from a URL	`=IMPORTDATA("https://example.com/file.csv")`
Exchange rate	`=GOOGLEFINANCE("CURRENCY:USDEUR")`
Stock price	`=GOOGLEFINANCE("GOOG", "price")`
Translate	`=GOOGLETRANSLATE(A2, "en", "es")`
Image in a cell	`=IMAGE("url")`
Sparkline	`=SPARKLINE(B2:F2)`
Link	`=HYPERLINK("url", "Open")`

References and errors

Lock	`$B$1 B$1 $B1 (F4 cycles)`
Hide an error	`=IFERROR(A2/B2, 0) =IFNA(VLOOKUP(...), "")`
Is blank, is number	`=ISBLANK(A2) =ISNUMBER(A2)`
Flag duplicates	`=COUNTIF($A$2:A2, A2)>1`
Address from text	`=INDIRECT("B"&E1)`
Shifted range	`=OFFSET(A1, 2, 0, 5, 1)`
Whole column	`=SUM(B:B)`
Checkbox value	`=COUNTIF(D2:D, TRUE)`

sheetsdrill.com: practice every one of these with instant checking. 106 entries.

Questions

What is different from Excel?

Open-ended ranges (B2:B), ARRAYFORMULA instead of Ctrl+Shift+Enter, QUERY for SQL-style summaries, REGEXMATCH, REGEXEXTRACT and REGEXREPLACE, and the import functions (IMPORTRANGE, IMPORTHTML, GOOGLEFINANCE). The lookup and aggregate functions are the same.

Which should I learn first?

SUMIFS and COUNTIFS, XLOOKUP, FILTER, UNIQUE and SORT, IFERROR, then QUERY and ARRAYFORMULA once the basics are reflexes.

Why is QUERY better than FILTER sometimes?

QUERY groups and aggregates in one step, with labels and sorting, which would take several FILTER and SUMIF formulas. FILTER is simpler for plain row filtering and works inside other formulas more easily.

Why does ARRAYFORMULA fill the whole column with zeros?

The range runs to the bottom of the sheet, and blank rows multiply to 0. Wrap the calculation in IF(B2:B="", , ...) so blank input rows stay blank.

A cheat sheet does not make them stick

Doing them with instant checking does. The free exercises need no account.

Start practicing