Google Sheets Formulas Cheat Sheet (50 Functions with Examples)

By Gerard Fernandez · Updated · 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.

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

FunctionWhat it doesExample
SUMAdds numbers=SUM(B2:B10)
AVERAGEMean of numbers=AVERAGE(B2:B10)
MIN / MAXSmallest / largest value=MAX(B2:B10)
MEDIANMiddle value=MEDIAN(B2:B10)
ROUNDRounds to N decimals=ROUND(B2, 2)
ROUNDUP / ROUNDDOWNAlways up / always down=ROUNDUP(B2, 0)
MROUNDNearest multiple=MROUND(B2, 0.05)
SUBTOTALTotal of visible rows only=SUBTOTAL(109, B2:B10)
Percentage of totalShare of a whole=B2/SUM($B$2:$B$10)
Percentage changeGrowth between two values=(B3-B2)/B2

Totals and counts with conditions

FunctionWhat it doesExample
SUMIFAdds rows matching one condition=SUMIF(A2:A10, "Food", B2:B10)
SUMIFSAdds rows matching several conditions=SUMIFS(C2:C10, A2:A10, "Food", B2:B10, "Card")
COUNTIFCounts rows matching one condition=COUNTIF(A2:A10, "Food")
COUNTIFSCounts rows matching several conditions=COUNTIFS(A2:A10, "Food", B2:B10, ">50")
AVERAGEIFAverage of matching rows=AVERAGEIF(A2:A10, "Food", B2:B10)
COUNTACounts non-empty cells=COUNTA(A2:A10)
COUNTCounts numbers only=COUNT(B2:B10)
COUNTBLANKCounts empty cells=COUNTBLANK(A2:A10)
COUNTUNIQUECounts distinct values=COUNTUNIQUE(A2:A10)

Logic

FunctionWhat it doesExample
IFOne result if true, another if false=IF(B2>=50, "Pass", "Fail")
IFSSeveral conditions in order=IFS(B2>=90, "A", B2>=70, "B", TRUE, "C")
AND / ORCombine conditions=IF(AND(B2>50, C2="Paid"), "OK", "")
IFERRORReplaces any error=IFERROR(B2/C2, "")
IFNAReplaces only #N/A=IFNA(VLOOKUP(E2, A2:B10, 2, FALSE), "Not found")

Lookups

FunctionWhat it doesExample
VLOOKUPFinds a row and returns a column to the right=VLOOKUP(E2, A2:C10, 3, FALSE)
XLOOKUPModern lookup, any direction=XLOOKUP(E2, A2:A10, C2:C10, "Not found")
INDEX + MATCHFlexible classic lookup=INDEX(C2:C10, MATCH(E2, A2:A10, 0))
Lookup on another tabSearch a different sheet=VLOOKUP(E2, Products!A:C, 3, FALSE)
IMPORTRANGEPull data from another file=IMPORTRANGE("url", "Sheet1!A1:C10")

Filtering, sorting and summarising

FunctionWhat it doesExample
FILTERReturns rows that meet a condition=FILTER(A2:C10, B2:B10>50)
UNIQUERemoves duplicates from a list=UNIQUE(A2:A10)
SORTSorted copy of a range=SORT(A2:C10, 2, FALSE)
QUERYSQL-style filtering and grouping=QUERY(A1:C10, "SELECT A, SUM(B) GROUP BY A", 1)
TRANSPOSESwaps rows and columns=TRANSPOSE(A1:C4)
ARRAYFORMULAOne formula for a whole column=ARRAYFORMULA(B2:B*C2:C)
RANKPosition in a list=RANK(B2, $B$2:$B$10)

Text

FunctionWhat it doesExample
& / CONCATENATEJoins text=A2&" "&B2
TEXTJOINJoins a range with a separator=TEXTJOIN(", ", TRUE, A2:A10)
SPLITSplits text into columns=SPLIT(A2, " ")
TRIMRemoves extra spaces=TRIM(A2)
UPPER / LOWER / PROPERChanges capitalisation=PROPER(A2)
LEFT / RIGHT / MIDPart of a text=LEFT(A2, 3)
LENNumber of characters=LEN(A2)
SUBSTITUTEReplaces text inside a cell=SUBSTITUTE(A2, "-", "")
VALUETurns text into a number=VALUE(A2)
HYPERLINKClickable link=HYPERLINK("https://example.com", "Open")

Dates and times

FunctionWhat it doesExample
TODAY / NOWCurrent date / date and time=TODAY()
Days between datesSubtract two dates=C2-B2
DATEDIFDifference in days, months or years=DATEDIF(B2, TODAY(), "Y")
NETWORKDAYSWorking days between dates=NETWORKDAYS(B2, C2)
DATEBuilds a date from parts=DATE(2026, 12, 31)
YEAR / MONTH / DAYPart of a date=MONTH(B2)
EOMONTHLast day of a month=EOMONTH(B2, 0)
TEXTDate formatted as text=TEXT(B2, "d mmm yyyy")

Random numbers and mini charts

FunctionWhat it doesExample
RANDBETWEENRandom whole number=RANDBETWEEN(1, 100)
RANDRandom decimal from 0 to 1=RAND()
SPARKLINETiny chart inside a cell=SPARKLINE(B2:G2)
SEQUENCEList of numbers=SEQUENCE(10)

When a formula shows an error

ErrorUsual cause
#N/AA 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 dependencyA formula includes its own cell

Formula tips worth knowing

  • 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.