# Google Sheets formulas in Excel and Google Sheets

> QUERY, ARRAYFORMULA, REGEX functions, SPLIT, SORTN and other formulas that work the Google Sheets way. 45 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/sheets · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Answers

- [How to use the QUERY function in Google Sheets](https://spreadsheetpractice.com/formulas/use-the-query-function): 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. `=QUERY(A1:F13,"select B, D, F where F > 2000",1)`
- [How to use QUERY with a text condition in Google Sheets](https://spreadsheetpractice.com/formulas/query-with-a-text-condition): Put the text in single quotes inside the query: =QUERY(A1:F13,"select A, B, F where C = 'East'",1) returns the East orders. `=QUERY(A1:F13,"select A, B, F where C = 'East'",1)`
- [How to use a cell reference in a QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/use-a-cell-reference-in-query): 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. `=QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1)`
- [How to filter between two dates with QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/filter-between-two-dates-with-query): 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). `=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](https://spreadsheetpractice.com/formulas/sum-by-category-with-query): Group by the category and sum the amount: =QUERY(A1:F13,"select C, sum(F) group by C label sum(F) 'Revenue'",1). `=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](https://spreadsheetpractice.com/formulas/count-rows-by-group-with-query): 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. `=QUERY(A1:F13,"select B, count(A) group by B label count(A) 'Orders'",1)`
- [How to sort data with QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/sort-data-with-query): 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. `=QUERY(A1:F13,"select C, B, F order by C, F desc",1)`
- [How to get the top 5 rows with QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/top-rows-with-query-limit): Sort descending and add limit: =QUERY(A1:F13,"select B, D, F order by F desc limit 5",1) returns the five biggest orders. `=QUERY(A1:F13,"select B, D, F order by F desc limit 5",1)`
- [How to use pivot in a QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/pivot-with-query): 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. `=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)`
- [How to rename column headers in a QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/rename-query-column-headers): 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). `=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](https://spreadsheetpractice.com/formulas/query-contains-text): Use contains in the where clause: =QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1) matches Widget and Widget Pro. `=QUERY(A1:F13,"select B, D, F where D contains 'Widget'",1)`
- [How to use QUERY with multiple conditions in Google Sheets](https://spreadsheetpractice.com/formulas/query-with-multiple-conditions): 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). `=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](https://spreadsheetpractice.com/formulas/sum-by-month-with-query): 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). `=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](https://spreadsheetpractice.com/formulas/remove-the-header-row-from-query): Start the range below the headers and set the last argument to 0: =QUERY(A2:F13,"select B, F where C = 'East'",0). `=QUERY(A2:F13,"select B, F where C = 'East'",0)`
- [How to skip blank rows in a QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/skip-blank-rows-in-query): 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. `=QUERY(A1:F,"select B, sum(F) where B is not null group by B label sum(F) 'Revenue'",1)`
- [How to add a calculated column in QUERY in Google Sheets](https://spreadsheetpractice.com/formulas/add-a-calculated-column-in-query): 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). `=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](https://spreadsheetpractice.com/formulas/query-two-ranges-at-once): 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). `=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](https://spreadsheetpractice.com/formulas/apply-a-formula-to-an-entire-column): 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. `=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))`
- [How to use VLOOKUP with ARRAYFORMULA in Google Sheets](https://spreadsheetpractice.com/formulas/vlookup-with-arrayformula): Give VLOOKUP the whole column of lookup values inside ARRAYFORMULA: =ARRAYFORMULA(IF(B2:B9="","",IFNA(VLOOKUP(B2:B9,E2:F6,2,FALSE),"Not found"))). `=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](https://spreadsheetpractice.com/formulas/put-a-header-and-arrayformula-in-one-cell): 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. `={"Pay";ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))}`
- [How to calculate a running total with ARRAYFORMULA in Google Sheets](https://spreadsheetpractice.com/formulas/running-total-with-arrayformula): 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))). `=ARRAYFORMULA(IF(B2:B12="","",SUMIF(ROW(B2:B12),"<="&ROW(B2:B12),B2:B12)))`
- [How to flag duplicates in a column in Google Sheets](https://spreadsheetpractice.com/formulas/flag-duplicates-in-a-column): Count each value in the column with COUNTIF inside ARRAYFORMULA: =ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))). `=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))`
- [How to number each occurrence of a value in Google Sheets](https://spreadsheetpractice.com/formulas/number-each-occurrence-of-a-value): Count matches from the top down to each row: =ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11)))). `=ARRAYFORMULA(IF(A2:A11="","",COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))))`
- [How to split text into columns in Google Sheets](https://spreadsheetpractice.com/formulas/split-text-into-columns): Use SPLIT with the separator: =SPLIT(A2,", ",FALSE) puts each part of A2 in its own column. FALSE makes ", " count as one separator. `=SPLIT(A2,", ",FALSE)`
- [How to split cells into rows in Google Sheets](https://spreadsheetpractice.com/formulas/split-cells-into-rows): Split the whole column, then read the pieces into one column without the gaps: =TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1). `=TOCOL(ARRAYFORMULA(SPLIT(B2:B5,", ",FALSE)),1)`
- [How to combine a column into one cell with commas in Google Sheets](https://spreadsheetpractice.com/formulas/combine-a-column-into-one-cell-in-google-sheets): Use TEXTJOIN with TRUE to skip blanks: =TEXTJOIN("; ",TRUE,B2:B8) joins the emails into one line you can paste into the To field. `=TEXTJOIN("; ",TRUE,B2:B8)`
- [How to filter rows with REGEXMATCH in Google Sheets](https://spreadsheetpractice.com/formulas/filter-rows-with-regexmatch): Use REGEXMATCH as the FILTER condition: =FILTER(A2:B10,REGEXMATCH(B2:B10,F1)) keeps the rows whose email matches the pattern in F1. `=FILTER(A2:B10,REGEXMATCH(B2:B10,F1))`
- [How to extract the domain from an email address in Google Sheets](https://spreadsheetpractice.com/formulas/extract-the-domain-from-an-email): Capture everything after the @: =REGEXEXTRACT(A2,"@(.+)$"). `=REGEXEXTRACT(A2,"@(.+)$")`
- [How to extract text between parentheses in Google Sheets](https://spreadsheetpractice.com/formulas/extract-text-between-parentheses-in-google-sheets): Capture what's inside the brackets: =REGEXEXTRACT(A2,"\(([^)]*)\)"). `=REGEXEXTRACT(A2,"\(([^)]*)\)")`
- [How to remove non-numeric characters in Google Sheets](https://spreadsheetpractice.com/formulas/remove-non-numeric-characters): Replace every non-digit with nothing: =REGEXREPLACE(A2,"\D",""). `=REGEXREPLACE(A2,"\D","")`
- [How to remove special characters from text in Google Sheets](https://spreadsheetpractice.com/formulas/remove-special-characters): Keep only letters, digits and spaces, then tidy the spaces: =TRIM(REGEXREPLACE(A2,"[^A-Za-z0-9 ]","")). `=TRIM(REGEXREPLACE(A2,"[^A-Za-z0-9 ]",""))`
- [How to check if an email address is valid in Google Sheets](https://spreadsheetpractice.com/formulas/check-if-an-email-address-is-valid): Test the shape with REGEXMATCH: =REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$") returns TRUE for something@domain.tld. `=REGEXMATCH(A2,"^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$")`
- [Why does REGEXMATCH not work on numbers in Google Sheets](https://spreadsheetpractice.com/formulas/regexmatch-error-on-numbers): REGEXMATCH only accepts text and returns #VALUE! for a number. Convert it first: =REGEXMATCH(TO_TEXT(A2),"^2026"). `=REGEXMATCH(TO_TEXT(A2),"^2026")`
- [How to swap first and last names in Google Sheets](https://spreadsheetpractice.com/formulas/swap-first-and-last-names): Capture both parts and put them back in the other order: =REGEXREPLACE(A2,"^(.+), (.+)$","$2 $1") turns "Lima, Ana" into "Ana Lima". `=REGEXREPLACE(A2,"^(.+), (.+)$","$2 $1")`
- [How to count cells that match a pattern in Google Sheets](https://spreadsheetpractice.com/formulas/count-cells-that-match-a-pattern): Turn REGEXMATCH's TRUE and FALSE into 1 and 0 and add them: =SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$")). `=SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$"))`
- [How to count unique values in Google Sheets](https://spreadsheetpractice.com/formulas/count-distinct-values-with-countunique): Use COUNTUNIQUE on the range: =COUNTUNIQUE(B2:B12) counts each different value once and ignores blank cells. `=COUNTUNIQUE(B2:B12)`
- [How to count unique values with a condition in Google Sheets](https://spreadsheetpractice.com/formulas/count-unique-values-with-a-condition): Filter first, then count the distinct values: =COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1)). `=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))`
- [How to get the top 3 values with SORTN in Google Sheets](https://spreadsheetpractice.com/formulas/top-values-with-sortn): Give SORTN the range, how many rows, a ties mode, the column and FALSE for descending: =SORTN(A2:B11,E1,0,2,FALSE). `=SORTN(A2:B11,E1,0,2,FALSE)`
- [How to combine multiple columns into one column in Google Sheets](https://spreadsheetpractice.com/formulas/combine-multiple-columns-into-one): Give FLATTEN each column in order: =FLATTEN(A2:A5,B2:B5,C2:C5) stacks them into one column. `=FLATTEN(A2:A5,B2:B5,C2:C5)`
- [How to use FILTER with multiple conditions in Google Sheets](https://spreadsheetpractice.com/formulas/filter-with-several-conditions): Give each condition its own argument: =FILTER(A2:D11,B2:B11=G1,D2:D11>=G2) keeps rows that pass every condition. `=FILTER(A2:D11,B2:B11=G1,D2:D11>=G2)`
- [How to get unique values in Google Sheets](https://spreadsheetpractice.com/formulas/unique-values-from-a-whole-column): Drop the blanks from the open range, then remove repeats and sort: =SORT(UNIQUE(FILTER(B2:B,B2:B<>""))). `=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))`
- [How to use IFS in Google Sheets](https://spreadsheetpractice.com/formulas/use-ifs-for-several-conditions): 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"). `=IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight")`
- [How to count checkboxes in Google Sheets](https://spreadsheetpractice.com/formulas/count-checkboxes): A ticked checkbox is TRUE, so count TRUE: =COUNTIF(C2:C9,TRUE). `=COUNTIF(C2:C9,TRUE)`
- [How to sum values where the checkbox is checked in Google Sheets](https://spreadsheetpractice.com/formulas/sum-where-the-checkbox-is-checked): Use SUMIF with TRUE as the criterion: =SUMIF(C2:C9,TRUE,B2:B9) adds the amounts on ticked rows. `=SUMIF(C2:C9,TRUE,B2:B9)`
- [How to get the last value in a column in Google Sheets](https://spreadsheetpractice.com/formulas/get-the-last-value-in-a-column): Keep the filled cells and take the last one: =CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1). It works even with gaps in the column. `=CHOOSEROWS(FILTER(B2:B,B2:B<>""),-1)`

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Dynamic array formulas](https://spreadsheetpractice.com/formulas/topic/dynamic) · [Duplicates and data cleanup formulas](https://spreadsheetpractice.com/formulas/topic/cleanup) · [Rounding and math formulas](https://spreadsheetpractice.com/formulas/topic/math) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent) · [Formula error fixes](https://spreadsheetpractice.com/formulas/topic/errors) · [Time formulas](https://spreadsheetpractice.com/formulas/topic/time) · [Finance formulas](https://spreadsheetpractice.com/formulas/topic/finance)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `QUERY`, `TEXT`, `ARRAYFORMULA`, `VSTACK`, `IF`, `IFNA`, `VLOOKUP`, `SUMIF`, `ROW`, `COUNTIF`, `COUNTIFS`, `SPLIT`, `TOCOL`, `TEXTJOIN`, `JOIN`, `FILTER`, `REGEXMATCH`, `REGEXEXTRACT`, `REGEXREPLACE`, `TRIM`, `TO_TEXT`, `SUMPRODUCT`, `COUNTUNIQUE`, `SORTN`, `FLATTEN`, `SORT`, `UNIQUE`, `IFS`, `CHOOSEROWS`, `INDEX`, `COUNTA`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.