The Google Sheets QUERY function lets you pull, summarize and reshape a range with one formula. This guide covers how to build the formula, how to keep the query text in its own cell, and how to avoid the two problems that cause most wrong results: mixed data types and guessed header rows.
All the rules below come from Google's own QUERY function help page.
The three parts of a QUERY formula
Google gives the syntax as:
QUERY(data, query, [headers])
- data is the range of cells you want to query.
- query is the instruction, written in the Google Visualization API Query Language. It must be in quotation marks, or be a reference to a cell that holds the query text.
- headers is optional. It is the number of header rows at the top of your data.
Build your first QUERY formula
- Click an empty cell where you want the results to appear.
- Type
=QUERY(and select your data range, for exampleA2:E6. - Type a comma, then your query in double quotation marks.
- Optionally, type a comma and the number of header rows.
- Close the bracket and press Enter.
Here is the sample Google uses on its help page:
=QUERY(A2:E6,"select avg(A) pivot B")
Refer to columns by letter or by Col number
The help page says QUERY accepts either "Col" notation or "A, B" notation for columns. Letters match the sheet's column letters. Pick one style and use it for the whole query so the formula is easier to read later.
Keep the query text in a separate cell
You don't have to write the query inside the formula. Put the query text in its own cell and point QUERY at it. Google's second sample does exactly this:
=QUERY(A2:E6,F2,FALSE)
Here F2 holds the query text. This makes long queries easier to edit, and you can change the query without touching the formula.
Set the headers argument yourself
If you leave out the third argument, or set it to -1, Google says the number of header rows is guessed from the data. A guess can differ from what you meant, so type the number when you know it:
=QUERY(A1:E6,"select avg(A) pivot B",1)
The headers argument also helps with messy sheets. According to the help page, it can turn a header that spans several rows into a single-row header.
Why some values come back blank
This is the most common QUERY surprise. Each column in your data can hold only one type of value: boolean, numeric (which includes dates and times), or text. When a column mixes types, Google's help page says the majority type decides the column's type, and the minority values are treated as null.
So if a column holds mostly numbers and a few text entries like "N/A" or "pending", those text entries are treated as empty. The reverse happens too: a mostly-text column will drop its few numbers.
The fix: keep each column to one type before you query it.
- Replace placeholder text like "N/A" in number columns with a blank cell.
- Check that dates were entered as real dates, not text that only looks like a date.
- If a code column holds things like product IDs that are partly numeric, make the whole column text so none of the entries get dropped.
If you're cleaning up a shared sheet, restore an earlier version if a bulk edit goes wrong.
Querying data from another file
The data argument is just a range, so you can feed QUERY data that you've pulled from another spreadsheet. See how to pull data from another Google Sheet with IMPORTRANGE for that first step.
Where to go for clause-by-clause examples
Google's help page has example tabs for Select & where, Pivot and Headers. For a longer walkthrough written for people coming from SQL, see Ben Collins's QUERY guide.
