Google Sheets Formulas Cheat Sheet (50 Functions with Examples)
By Gerard Fernandez · Updated October 20, 2026 · 4 min read
This page gathers the Google Sheets functions people use most, grouped by task. Each row has a short explanation, an example you can copy, and a link to a step-by-step guide with screenshots. Bookmark it and come back whenever you need a formula.
Tested: we tried the functions on this page in a real Google Sheets file (United States locale) and checked the results. Most of them are also shown step by step, with screenshots, in the guide linked from each row.
In this guide
Totals and basic maths Totals and counts with conditions Logic Lookups Filtering, sorting and summarising Text Dates and times Random numbers and mini charts When a formula shows an error Formula tips worth knowing
How to read the examples: they assume a small table with labels in column A and numbers in column B, starting in row 2. Change the ranges to match your own data.
Totals and basic maths
Function What it does Example
SUM Adds numbers =SUM(B2:B10)
AVERAGE Mean of numbers =AVERAGE(B2:B10)
MIN / MAX Smallest / largest value =MAX(B2:B10)
MEDIAN Middle value =MEDIAN(B2:B10)
ROUND Rounds to N decimals =ROUND(B2, 2)
ROUNDUP / ROUNDDOWN Always up / always down =ROUNDUP(B2, 0)
MROUND Nearest multiple =MROUND(B2, 0.05)
SUBTOTAL Total of visible rows only =SUBTOTAL(109, B2:B10)
Percentage of total Share of a whole =B2/SUM($B$2:$B$10)
Percentage change Growth between two values =(B3-B2)/B2
Totals and counts with conditions
Function What it does Example
SUMIF Adds rows matching one condition =SUMIF(A2:A10, "Food", B2:B10)
SUMIFS Adds rows matching several conditions =SUMIFS(C2:C10, A2:A10, "Food", B2:B10, "Card")
COUNTIF Counts rows matching one condition =COUNTIF(A2:A10, "Food")
COUNTIFS Counts rows matching several conditions =COUNTIFS(A2:A10, "Food", B2:B10, ">50")
AVERAGEIF Average of matching rows =AVERAGEIF(A2:A10, "Food", B2:B10)
COUNTA Counts non-empty cells =COUNTA(A2:A10)
COUNT Counts numbers only =COUNT(B2:B10)
COUNTBLANK Counts empty cells =COUNTBLANK(A2:A10)
COUNTUNIQUE Counts distinct values =COUNTUNIQUE(A2:A10)
Logic
Function What it does Example
IF One result if true, another if false =IF(B2>=50, "Pass", "Fail")
IFS Several conditions in order =IFS(B2>=90, "A", B2>=70, "B", TRUE, "C")
AND / OR Combine conditions =IF(AND(B2>50, C2="Paid"), "OK", "")
IFERROR Replaces any error =IFERROR(B2/C2, "")
IFNA Replaces only #N/A =IFNA(VLOOKUP(E2, A2:B10, 2, FALSE), "Not found")
Lookups
Function What it does Example
VLOOKUP Finds a row and returns a column to the right =VLOOKUP(E2, A2:C10, 3, FALSE)
XLOOKUP Modern lookup, any direction =XLOOKUP(E2, A2:A10, C2:C10, "Not found")
INDEX + MATCH Flexible classic lookup =INDEX(C2:C10, MATCH(E2, A2:A10, 0))
Lookup on another tab Search a different sheet =VLOOKUP(E2, Products!A:C, 3, FALSE)
IMPORTRANGE Pull data from another file =IMPORTRANGE("url", "Sheet1!A1:C10")
Filtering, sorting and summarising
Function What it does Example
FILTER Returns rows that meet a condition =FILTER(A2:C10, B2:B10>50)
UNIQUE Removes duplicates from a list =UNIQUE(A2:A10)
SORT Sorted copy of a range =SORT(A2:C10, 2, FALSE)
QUERY SQL-style filtering and grouping =QUERY(A1:C10, "SELECT A, SUM(B) GROUP BY A", 1)
TRANSPOSE Swaps rows and columns =TRANSPOSE(A1:C4)
ARRAYFORMULA One formula for a whole column =ARRAYFORMULA(B2:B*C2:C)
RANK Position in a list =RANK(B2, $B$2:$B$10)
Text
Function What it does Example
& / CONCATENATE Joins text =A2&" "&B2
TEXTJOIN Joins a range with a separator =TEXTJOIN(", ", TRUE, A2:A10)
SPLIT Splits text into columns =SPLIT(A2, " ")
TRIM Removes extra spaces =TRIM(A2)
UPPER / LOWER / PROPER Changes capitalisation =PROPER(A2)
LEFT / RIGHT / MID Part of a text =LEFT(A2, 3)
LEN Number of characters =LEN(A2)
SUBSTITUTE Replaces text inside a cell =SUBSTITUTE(A2, "-", "")
VALUE Turns text into a number =VALUE(A2)
HYPERLINK Clickable link =HYPERLINK("https://example.com", "Open")
Dates and times
Function What it does Example
TODAY / NOW Current date / date and time =TODAY()
Days between dates Subtract two dates =C2-B2
DATEDIF Difference in days, months or years =DATEDIF(B2, TODAY(), "Y")
NETWORKDAYS Working days between dates =NETWORKDAYS(B2, C2)
DATE Builds a date from parts =DATE(2026, 12, 31)
YEAR / MONTH / DAY Part of a date =MONTH(B2)
EOMONTH Last day of a month =EOMONTH(B2, 0)
TEXT Date formatted as text =TEXT(B2, "d mmm yyyy")
Random numbers and mini charts
Function What it does Example
RANDBETWEEN Random whole number =RANDBETWEEN(1, 100)
RAND Random decimal from 0 to 1 =RAND()
SPARKLINE Tiny chart inside a cell =SPARKLINE(B2:G2)
SEQUENCE List of numbers =SEQUENCE(10)
Error Usual cause
#N/A A lookup didn't find the value
#DIV/0! Dividing by zero or an empty cell
#VALUE! Text where a number is expected
#NAME? Misspelled function or text without quotes
#REF! Deleted reference or blocked array result
Circular dependency A formula includes its own cell
Lock references with $ so they don't move when you copy a formula: $B$2. Press F4 while editing to add them. See absolute references .
Text always goes in double quotes inside formulas: "Food", never Food.
Show every formula at once with Ctrl + ` . More in keyboard shortcuts .
Separators depend on the locale. Some countries use ; instead of , between arguments. Check File → Settings → Locale if a copied formula won't work.
About the author
Gerard Fernandez — I use spreadsheets every day and write the guides I wish I had found when I was learning. I keep these guides practical and update them when Google Sheets changes. Guides that say "in our test" were reproduced step by step in a real Google Sheets file, with real screenshots. More about this site .