Google Sheets formulas
QUERY, ARRAYFORMULA, REGEX functions, SPLIT, SORTN and other formulas that work the Google Sheets way. 45 answers, each checked by running it, with a live example and an exercise.
How to use the QUERY function in Google Sheets
=QUERY(A1:F13,"select B, D, F where F > 2000",1)Give QUERY a range, a query in quotes and the number of header rows:
=QUERY(A1:F13,"select B, D, F where F > 2000",1)returns the rep, product and revenue of every order over $2,000.How to use QUERY with a text condition in Google Sheets
=QUERY(A1:F13,"select A, B, F where C = 'East'",1)Put the text in single quotes inside the query:
=QUERY(A1:F13,"select A, B, F where C = 'East'",1)returns the East orders.How to use a cell reference in a QUERY in Google Sheets
=QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1)Close the quotes and join the cell with &:
=QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1). Text values keep their single quotes; numbers don't need them.How to filter between two dates with QUERY in Google Sheets
=QUERY(A1:F13,"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'",1)Build the dates into the query as yyyy-mm-dd text:
=QUERY(A1:F13,"select A, B, F where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(I2,"yyyy-mm-dd")&"'",1).How to sum by category with QUERY in Google Sheets
=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1)Group by the category and sum the amount:
=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1).How to count rows by group with QUERY in Google Sheets
=QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1)Group by the column and count another:
=QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1)counts the orders for each rep.How to sort data with QUERY in Google Sheets
=QUERY(A1:F13,"select C, B, F order by C, F desc",1)Add an order by clause:
=QUERY(A1:F13,"select C, B, F order by C, F desc",1)sorts by region, then by revenue from largest within each region.How to get the top 5 rows with QUERY in Google Sheets
=QUERY(A1:F13,"select B, D, F order by F desc limit 5",1)Sort descending and add limit:
=QUERY(A1:F13,"select B, D, F order by F desc limit 5",1)returns the five biggest orders.How to use pivot in a QUERY in Google Sheets
=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)Add a pivot clause after group by:
=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)puts each region in its own column.How to rename column headers in a QUERY in Google Sheets
=QUERY(A1:F13,"select B, sum(E), sum(F) group by B label B 'Sales rep', sum(E) 'Units', sum(F) 'Revenue'",1)Add a label clause at the end, one name per column:
=QUERY(A1:F13,"select B, sum(E), sum(F) group by B label B 'Sales rep', sum(E) 'Units', sum(F) 'Revenue'",1).How to use QUERY contains in Google Sheets
=QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1)Use contains in the where clause:
=QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1)matches Widget and Widget Pro.How to use QUERY with multiple conditions in Google Sheets
=QUERY(A1:F13,"select B, C, D, F where (C = 'East' or C = 'North') and F >= 1500",1)Join tests with and and or, and bracket the or part:
=QUERY(A1:F13,"select B, C, D, F where (C = 'East' or C = 'North') and F >= 1500",1).How to sum by month with QUERY in Google Sheets
=QUERY(A1:F13,"select month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'",1)Group by month(A)+1:
=QUERY(A1:F13,"select month(A)+1, sum(F) group by month(A)+1 label month(A)+1 'Month', sum(F) 'Revenue'",1).How to remove the header row from QUERY results in Google Sheets
=QUERY(A2:F13,"select B, F where C = 'East'",0)Start the range below the headers and set the last argument to 0:
=QUERY(A2:F13,"select B, F where C = 'East'",0).How to skip blank rows in a QUERY in Google Sheets
=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)Add a not-null test:
=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)ignores the empty rows of an open range.How to add a calculated column in QUERY in Google Sheets
=QUERY(A1:F13,"select B, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'",1)Write the calculation in select and name it with label:
=QUERY(A1:F13,"select B, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'",1).How to combine two ranges in one QUERY in Google Sheets
=QUERY({A1:C6;E2:G6},"select Col1, Col3 order by Col3 desc",1)Stack the ranges in curly braces with a semicolon and refer to columns as Col1, Col2:
=QUERY({A1:C6;E2:G6},"select Col1, Col3 order by Col3 desc",1).How to apply a formula to an entire column in Google Sheets
=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))Write it once in the top row with open ranges and ARRAYFORMULA:
=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))fills every row, including rows added later.How to use VLOOKUP with ARRAYFORMULA in Google Sheets
=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found")))Give VLOOKUP the whole column of lookup values inside ARRAYFORMULA:
=ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"))).How to put a header and ARRAYFORMULA in the same cell in Google Sheets
={"Pay";ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))}Stack the header text on top of the results with an array:
={"Pay";ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))}in the header cell.How to calculate a running total with ARRAYFORMULA in Google Sheets
=ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12)))Sum every row up to the current one with SUMIF on row numbers:
=ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12))).How to flag duplicates in a column in Google Sheets
=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))Count each value in the column with COUNTIF inside ARRAYFORMULA:
=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))).How to number each occurrence of a value in Google Sheets
=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))Count matches from the top down to each row:
=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)))).How to split text into columns in Google Sheets
=SPLIT(A2,", ",FALSE)Use SPLIT with the separator:
=SPLIT(A2,", ",FALSE)puts each part of A2 in its own column. FALSE makes ", " count as one separator.How to split cells into rows in Google Sheets
=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1)Split the whole column, then read the pieces into one column without the gaps:
=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1).How to combine a column into one cell with commas in Google Sheets
=TEXTJOIN("; ",TRUE,B2:B8)Use TEXTJOIN with TRUE to skip blanks:
=TEXTJOIN("; ",TRUE,B2:B8)joins the emails into one line you can paste into the To field.How to filter rows with REGEXMATCH in Google Sheets
=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))Use REGEXMATCH as the FILTER condition:
=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))keeps the rows whose email matches the pattern in F1.How to extract the domain from an email address in Google Sheets
=REGEXEXTRACT(A2,"@(.+)$")Capture everything after the @:
=REGEXEXTRACT(A2,"@(.+)$").How to extract text between parentheses in Google Sheets
=REGEXEXTRACT(A2,"\(([^)]*)\)")Capture what's inside the brackets:
=REGEXEXTRACT(A2,"\(([^)]*)\)").How to remove non-numeric characters in Google Sheets
=REGEXREPLACE(A2,"\D","")Replace every non-digit with nothing:
=REGEXREPLACE(A2,"\D","").How to remove special characters from text in Google Sheets
=TRIM(REGEXREPLACE(A2,"[^A-Za-z0-9 ]",""))Keep only letters, digits and spaces, then tidy the spaces:
=TRIM(REGEXREPLACE(A2,"[^A-Za-z0-9 ]","")).How to check if an email address is valid in Google Sheets
=REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$")Test the shape with REGEXMATCH:
=REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$")returns TRUE for something@domain.tld.Why does REGEXMATCH not work on numbers in Google Sheets
=REGEXMATCH(TO_TEXT(A2),"^2026")REGEXMATCH only accepts text and returns #VALUE! for a number. Convert it first:
=REGEXMATCH(TO_TEXT(A2),"^2026").How to swap first and last names in Google Sheets
=REGEXREPLACE(A2,"^(.+), (.+)$","$2 $1")Capture both parts and put them back in the other order:
=REGEXREPLACE(A2,"^(.+), (.+)$","$2 $1")turns "Lima, Ana" into "Ana Lima".How to count cells that match a pattern in Google Sheets
=SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$"))Turn REGEXMATCH's TRUE and FALSE into 1 and 0 and add them:
=SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$")).How to count unique values in Google Sheets
=COUNTUNIQUE(B2:B12)Use COUNTUNIQUE on the range:
=COUNTUNIQUE(B2:B12)counts each different value once and ignores blank cells.How to count unique values with a condition in Google Sheets
=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))Filter first, then count the distinct values:
=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1)).How to get the top 3 values with SORTN in Google Sheets
=SORTN(A2:B11,E1,0,2,FALSE)Give SORTN the range, how many rows, a ties mode, the column and FALSE for descending:
=SORTN(A2:B11,E1,0,2,FALSE).How to combine multiple columns into one column in Google Sheets
=FLATTEN(A2:A5,B2:B5,C2:C5)Give FLATTEN each column in order:
=FLATTEN(A2:A5,B2:B5,C2:C5)stacks them into one column.How to use FILTER with multiple conditions in Google Sheets
=FILTER(A2:D11,B2:B11=G1,D2:D11>=G2)Give each condition its own argument:
=FILTER(A2:D11,B2:B11=G1,D2:D11>=G2)keeps rows that pass every condition.How to get unique values in Google Sheets
=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))Drop the blanks from the open range, then remove repeats and sort:
=SORT(UNIQUE(FILTER(B2:B,B2:B<>""))).How to use IFS in Google Sheets
=IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight")List condition and result pairs, and end with TRUE as the catch-all:
=IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight").How to count checkboxes in Google Sheets
=COUNTIF(C2:C9,TRUE)A ticked checkbox is TRUE, so count TRUE:
=COUNTIF(C2:C9,TRUE).How to sum values where the checkbox is checked in Google Sheets
=SUMIF(C2:C9,TRUE,B2:B9)Use SUMIF with TRUE as the criterion:
=SUMIF(C2:C9,TRUE,B2:B9)adds the amounts on ticked rows.How to get the last value in a column in Google Sheets
=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)Keep the filled cells and take the last one:
=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1). It works even with gaps in the column.