150 Google Sheets Formulas with Examples, Use Cases (Beginner to Advanced)

Last updated on Jun 20, 2026, 5:38 PM
150 Google Sheets Formulas with Examples, Use Cases (Beginner to Advanced)
#FormulaExample#74b259
1SUM=SUM(A1:A10)Add values
2AVERAGE=AVERAGE(A1:A10)Calculate average
3MEDIAN=MEDIAN(A1:A10)Find middle value
4MIN=MIN(A1:A10)Lowest value
5MAX=MAX(A1:A10)Highest value
6COUNT=COUNT(A1:A10)Count numbers
7COUNTA=COUNTA(A1:A10)Count non-empty cells
8COUNTBLANK=COUNTBLANK(A1:A10)Count blank cells
9COUNTIF=COUNTIF(A:A,"Paid")Count matching values
10COUNTIFS=COUNTIFS(A:A,"Paid",B:B,"Yes")Multiple conditions
11SUMIF=SUMIF(A:A,"Sales",B:B)Conditional sum
12SUMIFS=SUMIFS(C:C,A:A,"Sales",B:B,"East")Multi-condition sum
13AVERAGEIF=AVERAGEIF(A:A,"Sales",B:B)Conditional average
14AVERAGEIFS=AVERAGEIFS(C:C,A:A,"Sales")Multi-condition average
15IF=IF(A1>100,"High","Low")Conditional logic
16IFS=IFS(A1>90,"A",A1>80,"B")Multiple conditions
17AND=AND(A1>50,B1="Yes")All conditions true
18OR=OR(A1>50,B1="Yes")Any condition true
19NOT=NOT(A1>100)Reverse logic
20IFERROR=IFERROR(A1/B1,0)Handle errors
21IFNA=IFNA(VLOOKUP(E2,A:B,2,FALSE),"Not Found")Handle N/A
22SWITCH=SWITCH(A1,"A",100,"B",80)Replace nested IF
23XLOOKUP=XLOOKUP(E2,A:A,B:B)Advanced lookup
24VLOOKUP=VLOOKUP(E2,A:B,2,FALSE)Vertical lookup
25HLOOKUP=HLOOKUP("Jan",A1:M2,2,FALSE)Horizontal lookup
26LOOKUP=LOOKUP(100,A:A,B:B)Simple lookup
27INDEX=INDEX(B:B,5)Return value by position
28MATCH=MATCH("John",A:A,0)Find position
29INDEX+MATCH=INDEX(B:B,MATCH(E2,A:A,0))Flexible lookup
30FILTER=FILTER(A:C,C:C="Paid")Filter records
31QUERY=QUERY(A:D,"SELECT A,B")Database-like query
32SORT=SORT(A2:C10,2,TRUE)Sort data
33SORTN=SORTN(A2:B100,10)Top N results
34UNIQUE=UNIQUE(A:A)Remove duplicates
35TRANSPOSE=TRANSPOSE(A1:D4)Swap rows & columns
36ARRAYFORMULA=ARRAYFORMULA(A2:A*B2:B)Bulk calculations
37SEQUENCE=SEQUENCE(10)Generate sequence
38CHOOSECOLS=CHOOSECOLS(A:E,1,3)Select columns
39CHOOSEROWS=CHOOSEROWS(A:E,1,3)Select rows
40WRAPROWS=WRAPROWS(A1:A10,3)Wrap into rows
41WRAPCOLS=WRAPCOLS(A1:A10,3)Wrap into columns
42TEXT=TEXT(A1,"$0.00")Format values
43CONCATENATE=CONCATENATE(A1," ",B1)Join text
44CONCAT=CONCAT(A1,B1)Combine text
45TEXTJOIN=TEXTJOIN(", ",TRUE,A1:A10)Join with delimiter
46JOIN=JOIN("-",A1:A5)Join range
47LEFT=LEFT(A1,5)Left characters
48RIGHT=RIGHT(A1,4)Right characters
49MID=MID(A1,3,5)Middle characters
50LEN=LEN(A1)Character count
51TRIM=TRIM(A1)Remove spaces
52UPPER=UPPER(A1)Uppercase
53LOWER=LOWER(A1)Lowercase
54PROPER=PROPER(A1)Proper case
55SUBSTITUTE=SUBSTITUTE(A1,"old","new")Replace text
56REPLACE=REPLACE(A1,1,3,"ABC")Replace by position
57FIND=FIND("@",A1)Find position
58SEARCH=SEARCH("google",A1)Case-insensitive search
59REGEXMATCH=REGEXMATCH(A1,"[0-9]")Pattern match
60REGEXEXTRACT=REGEXEXTRACT(A1,"\d+")Extract pattern
61REGEXREPLACE=REGEXREPLACE(A1,"\s+"," ")Regex replace
62SPLIT=SPLIT(A1,",")Split text
63VALUE=VALUE(A1)Text to number
64DATE=DATE(2026,6,19)Create date
65TODAY=TODAY()Current date
66NOW=NOW()Current date/time
67YEAR=YEAR(A1)Extract year
68MONTH=MONTH(A1)Extract month
69DAY=DAY(A1)Extract day
70WEEKDAY=WEEKDAY(A1)Day number
71WEEKNUM=WEEKNUM(A1)Week number
72DATEDIF=DATEDIF(A1,B1,"D")Date difference
73EDATE=EDATE(A1,3)Add months
74EOMONTH=EOMONTH(A1,0)End of month
75NETWORKDAYS=NETWORKDAYS(A1,B1)Working days
76WORKDAY=WORKDAY(A1,10)Future workday
77TIME=TIME(10,30,0)Create time
78HOUR=HOUR(A1)Extract hour
79MINUTE=MINUTE(A1)Extract minute
80SECOND=SECOND(A1)Extract second
81ROUND=ROUND(A1,2)Round value
82ROUNDUP=ROUNDUP(A1,0)Round up
83ROUNDDOWN=ROUNDDOWN(A1,0)Round down
84INT=INT(A1)Integer part
85MOD=MOD(10,3)Remainder
86ABS=ABS(A1)Absolute value
87POWER=POWER(2,3)Exponents
88SQRT=SQRT(A1)Square root
89RAND=RAND()Random number
90RANDBETWEEN=RANDBETWEEN(1,100)Random integer
91PRODUCT=PRODUCT(A1:A5)Multiply values
92CEILING=CEILING(A1,5)Round up multiple
93FLOOR=FLOOR(A1,5)Round down multiple
94GOOGLEFINANCE=GOOGLEFINANCE("GOOG")Financial data
95GOOGLETRANSLATE=GOOGLETRANSLATE(A1,"en","es")Translate text
96IMPORTXML=IMPORTXML("https://example.com","//title")Scrape web data
97IMPORTHTML=IMPORTHTML("https://example.com","table",1)Import tables
98IMPORTRANGE=IMPORTRANGE("URL","Sheet1!A:C")Import other sheets
99IMAGE=IMAGE("https://example.com/image.jpg")Display image
100SPARKLINE=SPARKLINE(A1:A10)Mini charts
101QUERY=QUERY(A:F,"SELECT A,B,C WHERE D='Paid'")SQL-style reporting
102QUERY + GROUP BY=QUERY(A:C,"SELECT A,SUM(B) GROUP BY A")Sales summary
103QUERY + ORDER BY=QUERY(A:C,"SELECT * ORDER BY C DESC")Sort reports
104QUERY + LIMIT=QUERY(A:C,"SELECT * LIMIT 10")Top records
105QUERY + LABEL=QUERY(A:C,"SELECT A,SUM(B) GROUP BY A LABEL SUM(B) 'Total'")Custom reports
106FILTER=FILTER(A2:F100,F2:F100="Completed")Dynamic filtering
107FILTER + Multiple Conditions=FILTER(A:F,B:B="USA",C:C="Active")Advanced filtering
108UNIQUE + SORT=SORT(UNIQUE(A:A))Sorted unique values
109SORTN=SORTN(A2:B100,10,0,2,FALSE)'Top 10 results
110XLOOKUP=XLOOKUP(E2,A:A,C:C,"Not Found")Modern lookup
111INDEX + MATCH=INDEX(C:C,MATCH(E2,A:A,0))Flexible lookup
112INDEX + MATCH + MATCH=INDEX(A:Z,MATCH(E1,A:A,0),MATCH(F1,1:1,0))2D lookup
113ARRAYFORMULA=ARRAYFORMULA(B2:B*C2:C)Entire column calculations
114ARRAYFORMULA + IF=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))Auto calculations
115ARRAYFORMULA + VLOOKUP=ARRAYFORMULA(VLOOKUP(E2:E,A:C,3,FALSE))Bulk lookups
116MAP=MAP(A2:A,LAMBDA(x,x*10))Apply custom logic
117BYROW=BYROW(A2:D10,LAMBDA(r,SUM(r)))Row calculations
118BYCOL=BYCOL(A2:D10,LAMBDA(c,AVERAGE(c)))Column calculations
119REDUCE=REDUCE(0,A1:A10,LAMBDA(a,b,a+b))Accumulation
120SCAN=SCAN(0,A1:A10,LAMBDA(a,b,a+b))Running totals
121MAKEARRAY=MAKEARRAY(5,5,LAMBDA(r,c,r*c))Dynamic arrays
122LET=LET(x,SUM(A:A),x*0.1)Reusable variables
123LAMBDA=LAMBDA(x,x*2)(10)Custom functions
124CHOOSECOLS=CHOOSECOLS(A:F,1,3,5)Extract columns
125CHOOSEROWS=CHOOSEROWS(A:F,2,4,6)Extract rows
126TAKE=TAKE(A:F,10)First rows
127DROP=DROP(A:F,1)Remove headers
128WRAPROWS=WRAPROWS(A1:A20,5)Reshape data
129WRAPCOLS=WRAPCOLS(A1:A20,5)Reshape columns
130HSTACK=HSTACK(A:A,B:B,C:C)Merge columns
131VSTACK=VSTACK(Sheet1!A:C,Sheet2!A:C)Combine tables
132TOCOL=TOCOL(A1:D10)'Convert to one column
133TOROW=TOROW(A1:D10)'Convert to one row
134REGEXEXTRACT=REGEXEXTRACT(A1,"[0-9]+")Extract numbers
135REGEXMATCH=REGEXMATCH(A1,"gmail\.com")Email validation
136REGEXREPLACE=REGEXREPLACE(A1,"[^0-9]","")Clean phone numbers
137TEXTJOIN + FILTER=TEXTJOIN(", ",TRUE,FILTER(A:A,B:B="Yes"))Dynamic lists
138IMPORTRANGE=IMPORTRANGE("URL","Sales!A:F")External sheet data
139IMPORTRANGE + QUERY=QUERY(IMPORTRANGE("URL","Sales!A:F"),"SELECT Col1,Col3")Remote reporting
140IMPORTXML=IMPORTXML("https://example.com","//h1")Web scraping
141IMPORTHTML=IMPORTHTML("https://example.com","table",1)Import tables
142GOOGLEFINANCE=GOOGLEFINANCE("NASDAQ:GOOG","price")Stock tracking
143GOOGLEFINANCE Historical=GOOGLEFINANCE("GOOG","close",TODAY()-30,TODAY())Historical prices
144NETWORKDAYS.INTL=NETWORKDAYS.INTL(A1,B1,"0000011")Custom workdays
145WORKDAY.INTL=WORKDAY.INTL(A1,30,"0000011")Business dates
146DATEDIF=DATEDIF(A1,B1,"YM")Age/month calculation
147SPARKLINE=SPARKLINE(B2:M2)'Mini charts
148FREQUENCY=FREQUENCY(A:A,{10,20,30,40})Distribution analysis
149MMULT=MMULT(A1:C3,E1:G3)Matrix multiplication
150TRANSPOSE + QUERY=QUERY(TRANSPOSE(A1:Z2),"SELECT *")Pivot-like reporting