How to Use QUERY in Google Sheets: Comprehensive Guide

QUERY runs a SQL-like command against a range and returns the result as a table. It can filter, sort, group, aggregate, pivot and relabel data in a single formula, which makes it the most powerful function in Google Sheets. If you find yourself chaining FILTER, SORT, SUMIF and UNIQUE, there is probably a single QUERY that does the same job.

Syntax

=QUERY(data, query, [headers])

data: The range or array to query, for example A1:E100 or Sheet2!A:E.
query: A text string in the Google Visualization API Query Language. It must be in quotes.
headers [Optional]: The number of header rows in data. If omitted, Sheets guesses, which can go wrong. Pass it explicitly, for example 1.

Column references

In the query string, refer to columns by letter: A, B, C, and so on. This is the natural choice when your range starts at column A. If the range starts elsewhere, or when data is an array literal such as {A:A, C:C} or the result of another function like IMPORTRANGE, use Col1, Col2, Col3 (the position within the data) instead. Wrapping a normal range in curly braces, {D1:F100}, also lets you use Col1-style names with it.

The data's headers are not part of the query text. If your headers are in the first row, QUERY(A1:E100, "select A, C", 1) uses row 1 as headers.

The clauses, in order

A query is made of clauses that must appear in this order:

select ... where ... group by ... pivot ... order by ... limit ... offset ... label ... format

Every clause is optional. Here are the ones you will use most.

select

Chooses the columns to return.

=QUERY(A1:E100, "select A, C, E", 1)

Use select * for all columns.

where

Filters rows.

=QUERY(A1:E100, "select A, B where C > 100", 1)

Text must be in single quotes: where B = 'Sales'.
Combine conditions with and, or and not: where C > 100 and B = 'Sales'.
Text matching: where A contains 'inc', where A starts with 'A', where A matches '.*corp$' (regular expression).
Missing values: where C is not null.

order by and limit

=QUERY(A1:E100, "select A, C order by C desc limit 5", 1)

Returns the five rows with the highest value in column C.

group by and aggregates

Aggregation functions are sum, avg, count, min and max.

=QUERY(A1:E100, "select B, sum(C) group by B", 1)

Returns total C for each distinct value of B, a quick summary table that would otherwise need UNIQUE plus SUMIF. Every column in select must either be in group by or be wrapped in an aggregate.

pivot

Turns the distinct values of a column into columns.

=QUERY(A1:E100, "select B, sum(C) group by B pivot D", 1)

label and format

Rename or format output columns:

=QUERY(A1:E100, "select B, sum(C) group by B label sum(C) 'Total sales' format sum(C) '#,##0'", 1)

Using cell values in a query

Build the query string by concatenating cell references. Dates and text need careful quoting.

=QUERY(A1:E100, "select A, C where B = '" & G1 & "'", 1)

If G1 contains Sales, the query becomes where B = 'Sales'. For numbers, no quotes are needed: "where C > " & G2.

Dates must be converted into the form the query language expects:

=QUERY(A1:E100, "select A, C where D >= date '" & TEXT(G1, "yyyy-mm-dd") & "'", 1)

Combining ranges from several sheets

Use an array literal to stack ranges, then refer to the columns as Col1, Col2, and so on:

=QUERY({Jan!A2:C; Feb!A2:C}, "select Col1, sum(Col3) group by Col1", 0)

Here the semicolon stacks the ranges vertically.

Common errors

Unable to parse query string for Function QUERY parameter 2: .... Typically a typo or a clause in the wrong order. Check the order above, and that text values are in single quotes.
#VALUE! with "column ... does not exist". You used a column letter outside the data range, or used A/B when the data is an array (use Col1/Col2).
Mixed data types in a column. QUERY decides a column's type from the majority of its values and treats the minority as empty. A numeric column with a few text entries silently drops them. Clean the column first.
Headers guessed wrongly. Always set the third argument.
Wrong totals in group by. Text numbers do not sum. Convert them to real numbers first.

QUERY vs. FILTER vs. pivot tables

FILTER is simpler for one-off row filtering. QUERY wins when you also need to sort, aggregate or select columns in the same step. A pivot table is better for interactive exploration, while QUERY gives you a formula that stays live and can feed other formulas. For a full tour, see Mastering the QUERY Function in Google Sheets.

Frequently asked questions

Is QUERY the same as SQL? It looks similar but is a limited dialect: there are no joins, and columns are referenced by letter. For joins, combine ranges first or use XLOOKUP.

Can QUERY pull data from another file? Yes: =QUERY(IMPORTRANGE("url", "Sheet1!A:E"), "select Col1, Col3 where Col2 = 'Sales'", 1). Note that you use Col1, Col2 there.

Does it update automatically? Yes. The result recalculates whenever the source data or any cell used in the query string changes.

Related Functions

FILTER: Returns the rows of a range that meet a condition.
SORT: Sorts the rows of a range.
UNIQUE: Returns the distinct rows of a range.
SUMIF: Adds up values that meet a condition.
IMPORTRANGE: Imports a range from another spreadsheet.

QUERY