QUERY function in Google Sheets: syntax, clauses and examples
QUERY runs an SQL-like query on a range: it selects columns, filters rows, groups, pivots and sorts in one formula. This guide explains the syntax and each clause in the order they must be written, shows how to query several tabs and other spreadsheets, how to pass cell values into the query, and what the common errors mean.
The QUERY function runs a query written in an SQL-like language on a range of cells. One formula can select columns, filter rows, group, pivot and sort:
=QUERY(B2:D17, "SELECT B, SUM(D) GROUP BY B ORDER BY SUM(D) DESC", 1)
This returns one row for each sales representative in column B with the total of column D, the largest total first.
QUERY syntax
=QUERY(data, query, [headers])
- data: the range to query, for example B2:D17, a named range, or an array in curly braces.
- query: the query text in quotation marks, or a reference to a cell that holds it.
- headers: the number of header rows at the top of the data. Optional. If it is left out, Sheets guesses from the content. Set it to 1 for a table with one header row and to 0 for a range without headers; a wrong guess turns the first data row into a header or merges two rows into one.
The query is built from clauses. All of them are optional, but they have to come in this order:
| Clause | What it does | Example |
|---|---|---|
SELECT |
Chooses the columns and their order; * means all |
SELECT B, D |
WHERE |
Keeps the rows that meet a condition | WHERE D > 5000 |
GROUP BY |
Collapses rows with the same value into one | GROUP BY B |
PIVOT |
Turns the values of a column into column headers | PIVOT C |
ORDER BY |
Sorts the result | ORDER BY D DESC |
LIMIT |
Returns only the first rows | LIMIT 5 |
OFFSET |
Skips the first rows | OFFSET 5 |
LABEL |
Renames column headers | LABEL B 'Sales rep' |
FORMAT |
Sets a number or date format | FORMAT D '#,##0' |
Three rules cause most of the errors:
- Keywords can be written in any case (
selectorSELECT), but column letters must be capitals:select breturns an error. - Text values go in single quotes inside the query:
WHERE B = 'John Smith'. Text comparison is case-sensitive. - When the data is a plain range, columns are named by their sheet letters (B, C, D), even if the range starts in column B. When the data is an array in curly braces or comes from IMPORTRANGE, they are named Col1, Col2, Col3 by position.
The full language is described in Google’s Query Language reference.
The example data
The examples use a table of 15 sales records in B2:D17, with the sales representative in column B, the region in column C and the amount in column D.
The formulas are shown as in the screenshots, without the third argument. In your own sheets, add , 1 at the end.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
QUERY examples, clause by clause
QUERY examples, clause by clause
SELECT: choose columns
=QUERY(B2:D17, "SELECT B")
List several columns with commas, in the order you want them: "SELECT D, B". "SELECT *" returns all columns of the range.
WHERE: filter rows
=QUERY(B2:D17, "SELECT * WHERE B = 'John Smith'")
Conditions are joined with AND and OR, and grouped with brackets:
=QUERY(B2:D17, "SELECT * WHERE C = 'Northeast' AND D > 5000")
The operators available in WHERE:
| Operator | Meaning | Example |
|---|---|---|
=, !=, <> |
Equal, not equal | WHERE C != 'West' |
>, >=, <, <= |
Comparison | WHERE D >= 5000 |
contains |
The text contains the value | WHERE B contains 'Smith' |
starts with, ends with |
The text starts or ends with the value | WHERE B starts with 'J' |
like |
Pattern: % is any text, _ one character |
WHERE C like 'North%' |
matches |
Regular expression, on the whole value | WHERE C matches '.*east' |
is null, is not null |
Empty, not empty | WHERE D is not null |
date |
A date value, always as yyyy-mm-dd | WHERE A >= date '2024-06-01' |
To ignore the case of a text, compare its lowercase form: WHERE lower(B) = 'john smith'. Dates have their own traps, covered in the guide to date filtering with QUERY.
ORDER BY: sort
=QUERY(B2:D17, "SELECT * ORDER BY D DESC")
ASC, the default, sorts upwards and DESC downwards. A second column breaks ties: ORDER BY C, D DESC.
LIMIT and OFFSET: the top rows
=QUERY(B2:D17, "SELECT * ORDER BY D DESC LIMIT 5")
OFFSET skips rows before LIMIT counts: ORDER BY D DESC LIMIT 5 OFFSET 5 returns places six to ten.
Aggregate functions: SUM, AVG, COUNT, MAX, MIN
=QUERY(B2:D17, "SELECT SUM(D)")
The result has a generated header, “sum Sales Amount ($)”. Remove or replace it with LABEL, shown below.
GROUP BY: one row per value
=QUERY(B2:D17, "SELECT B, SUM(D) GROUP BY B")
Every column in SELECT that is not inside an aggregate function has to be listed in GROUP BY. Sorting by the aggregate puts the best result on top:
=QUERY(B2:D17, "SELECT B, AVG(D) GROUP BY B ORDER BY AVG(D) DESC")
PIVOT: values as columns
=QUERY(B2:D17, "SELECT B, SUM(D) WHERE B IS NOT NULL GROUP BY B PIVOT C")
GROUP BY makes the rows and PIVOT the columns: each region becomes a column, and each cell holds the sum for that representative and region. For an interactive version of the same table, use a pivot table.
LABEL: rename headers
=QUERY(B2:D17, "SELECT B, C, D LABEL B 'Sales Rep', C 'Sales Region', D 'Sales Amount'")
For an aggregate, repeat the expression exactly as it is written in SELECT: LABEL SUM(D) 'Total'. An empty label, LABEL SUM(D) '', removes the header and leaves the number alone in the cell.
Arithmetic on columns
Numeric columns can be added, subtracted, multiplied and divided, with each other or with a number:
=QUERY(B2:D17, "SELECT B, D * 0.1 LABEL D * 0.1 'Bonus'")
A value from the sheet is joined into the query text. Here each sale is divided by the total in cell D18:
=QUERY(B2:D17, "SELECT B, (D / " & D18 & ") * 100")
Without LABEL, the header of a calculated column is generated from the expression, as in the screenshot.
FORMAT: number and date formats
=QUERY(B2:D17, "SELECT B, AVG(D) GROUP BY B FORMAT AVG(D) '#,##0.0'")
FORMAT changes how the values are displayed, not the values. The codes are the same as in the TEXT function.
Use cell values in a query
The query is a text, so a cell value is joined to it with an ampersand. Text needs single quotes around it, a number does not:
=QUERY(B2:D17, "SELECT * WHERE B = '" & F2 & "' AND D > " & G2)
With “John Smith” in F2 and 5000 in G2, the query becomes SELECT * WHERE B = 'John Smith' AND D > 5000. Change the cells and the result follows. A date has to be converted to the yyyy-mm-dd form first: "WHERE A >= date '" & TEXT(F2, "yyyy-mm-dd") & "'".
QUERY across several tabs
Stack the ranges in curly braces, one under the other with a semicolon, and query the result. Take the header row from the first tab only:
=QUERY({'Tab1'!B2:D9; 'Tab2'!B3:D9}, "SELECT *")
The data is now an array, so the columns are Col1, Col2, Col3:
=QUERY({'Tab1'!B2:D9; 'Tab2'!B3:D9}, "SELECT Col2, SUM(Col3) GROUP BY Col2 ORDER BY SUM(Col3) DESC")
The tabs must have the same columns in the same order.
QUERY on another spreadsheet
IMPORTRANGE brings the range in, QUERY works on it. On first use, the cell shows #REF! until you click Allow access:
=QUERY(IMPORTRANGE("spreadsheet_url", "'Another sheet'!B2:D17"), "SELECT * WHERE Col2 = 'Northeast'")
Replace spreadsheet_url with the address of the source file. The columns are again Col1, Col2, Col3. The guide to QUERY with IMPORTRANGE goes through this combination in detail.
Two spreadsheets side by side
Two imported ranges can be placed next to each other with a comma inside the curly braces:
=QUERY({IMPORTRANGE("sales_url", "Sales!B2:D17"), IMPORTRANGE("bonuses_url", "Bonuses!B2:D17")}, "SELECT Col1, Col3, Col6 WHERE Col1 = 'John Smith'")
Grouping reduces this to one row:
=QUERY({IMPORTRANGE("sales_url", "Sales!B2:D17"), IMPORTRANGE("bonuses_url", "Bonuses!B2:D17")}, "SELECT Col1, SUM(Col3), SUM(Col6) WHERE Col1 = 'John Smith' GROUP BY Col1")
This is not a join. The two ranges are glued together row by row, so the result is right only while both files have the same rows in the same order. If a row is added to one file and not to the other, sales and bonuses of different people end up on one line, with no error. To match rows by a key, use VLOOKUP or XLOOKUP on the imported ranges.
Errors in QUERY
⚠️ Error: #VALUE! with “Unable to parse query string”.
✅ Solution: The query text is not valid. The usual causes: clauses in the wrong order (ORDER BY before WHERE), a column letter in lower case, text without single quotes, a missing comma between columns.
⚠️ Error: #VALUE! with a message that contains NO_COLUMN.
✅ Solution: The query names a column that is not in the data: column E for the range B2:D17, or a letter where Col1 is required. Arrays in curly braces and IMPORTRANGE use Col1, Col2; plain ranges use letters.
⚠️ Error: The result is only a header row.
✅ Solution: No row meets the condition. Check the case of text values (‘john smith’ does not match “John Smith”), spaces at the end of the cells, and whether the numbers are stored as text.
⚠️ Error: Some values are missing from a column of the result.
✅ Solution: The column mixes types. QUERY gives each column one type, the one most of its cells have, and treats the other cells as empty. A column of numbers with a few text entries loses the text. Make the column one type before querying it, for example by converting it with TO_TEXT or VALUE.
⚠️ Error: The first data row has become part of the header.
✅ Solution: The third argument was left out and Sheets guessed the number of header rows. Set it: 1 for one header row, 0 for none.
⚠️ Error: #REF! with “Array result was not expanded”.
✅ Solution: A cell where the result should go already holds a value. Clear the cells below and to the right of the formula.
⚠️ Error: #REF! on a query over IMPORTRANGE.
✅ Solution: Access to the source file has not been granted. Enter the IMPORTRANGE part alone in an empty cell, click Allow access, then return to the query.
⚠️ Error: A formula parse error in a locale with decimal commas.
✅ Solution: Separate the arguments with semicolons, and columns inside curly braces with a backslash.
QUERY or another function
- For rows that meet a condition, with no grouping, FILTER is shorter and takes cell references directly.
- For one value from a table, use a lookup. See QUERY and VLOOKUP compared.
- For a summary that readers will re-arrange themselves, use a pivot table.
- QUERY is the right tool when one formula has to filter, group and sort, or when the result has to grow as new values appear.
Related guides
- Date filtering with QUERY: Dates and date ranges in WHERE.
- QUERY with IMPORTRANGE: Query data from other spreadsheets.
- QUERY with CONCATENATE: Join columns in the result.
- QUERY with UNIQUE: Distinct rows from a query.
- QUERY and VLOOKUP: Which one to use for a lookup.
- FILTER: Rows that meet a condition.
- Array formulas: Curly braces and ARRAYFORMULA.
- IMPORTRANGE: Bring a range from another file.
QUERY reshapes the rows it is given
QUERY reshapes the rows it is given
QUERY is a small SQL engine inside the sheet, and like any query it is only as good as the table it runs on. In most reports that table is an export or a chain of IMPORTRANGE formulas, and the QUERY on top repeats work that was already done once in the warehouse: the same filter, the same grouping, written again by hand in every file.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. QUERY is then left for the last step, slicing a table that is already current and already defined once. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
What is the QUERY function in Google Sheets?
The QUERY function in Google Sheets allows you to retrieve specific data from a dataset by using SQL-like queries. It enables filtering, sorting, and performing calculations on data, making it a powerful tool for analyzing and managing large sets of information efficiently.
Can the QUERY function handle data from multiple sheets or tabs in Google Sheets?
Yes. The tabs go into the first argument, not into the query text: stack the ranges in curly braces with a semicolon, for example QUERY({Tab1!B2:D9; Tab2!B3:D9}, "SELECT Col2, SUM(Col3) GROUP BY Col2"). Because the data is now an array, the columns are named Col1, Col2, Col3 instead of letters, and the tabs must have the same columns in the same order.
What are some common issues I might encounter with the QUERY function, and how can I resolve them?
A #VALUE! error with 'Unable to parse query string' means the query text is invalid: clauses in the wrong order, a column letter in lower case, or text without single quotes. A message with NO_COLUMN means the query names a column outside the data, or uses a letter where Col1 is required. A result with only a header row means no row matched, often because text comparison is case-sensitive. Missing values in a column come from mixed data types: QUERY keeps the type most cells have and treats the rest as empty.
What are some practical applications of the QUERY function for SEO and marketing?
You can use the QUERY function for SEO and marketing to analyze website traffic data, track keyword performance, and evaluate the effectiveness of marketing campaigns.
How can I use the QUERY function to sort and filter large datasets?
You can use the QUERY function by including sorting and filtering criteria within the query string. For example, you can use the "ORDER BY" clause to sort data based on a specific column, and the "WHERE" clause to filter data based on certain conditions.
