ExcelDailyDrills

The QUERY function in Google Sheets, explained

=QUERY(data, query, [headers]) filters, sorts and summarises a range with a short text written in the Google Visualization API Query Language. It resembles a SQL query, so it is the closest Sheets gets to a pivot table in one formula.

QUERY exists only in Google Sheets. In Excel, use FILTER, SORT, SUMIFS or a PivotTable for the same jobs.

The three arguments

data is the range to query. Each column should hold one type of value: numbers, text or dates. A column that mixes types is treated as its majority type.

query is the query text in quotes, or a reference to a cell holding it.

headers is how many header rows the range has. Leave it out and Sheets guesses, which is why a header row is sometimes treated as data. Set it to 1 when you have one header row.

Select and where

Columns are named by their letters in the range (A, B, C…). =QUERY(A1:D20,"select A, C where C > 100",1) returns columns A and C for the rows where C is over 100. Text values go in single quotes: =QUERY(A1:D20,"select A, C where B = 'East'",1).

Group by and sums

Aggregate functions are sum, avg, count, min and max. =QUERY(A1:D20,"select B, sum(C) group by B",1) totals column C for each distinct value of B: a SUMIF for every region in one formula. Any selected column that isn't aggregated must be in group by.

Order by, limit and label

=QUERY(A1:D20,"select B, sum(C) group by B order by sum(C) desc limit 3 label sum(C) 'Total'",1) sorts the totals from high to low, keeps the top three, and renames the sum column to Total.

Pivot

Add pivot to turn the distinct values of a column into columns: =QUERY(A1:D20,"select B, sum(C) group by B pivot D",1) shows one column for each value of D, like a PivotTable.

Common mistakes

  • Mixed data types in a column. A column with numbers and text returns blanks for the minority type.

    Fix: Clean the column so every cell has the same type before querying it.

  • Forgetting the quotes around text values. =QUERY(A1:D20,"select A where B = East") gives an error.

    Fix: Put text in single quotes inside the query: B = 'East'.

  • Leaving out the headers argument. Sheets guesses and may treat the header row as data.

    Fix: Always give the number of header rows, usually 1.

Frequently asked questions

What is the Excel equivalent of QUERY?

There is no single one. FILTER with SORT covers select and where, SUMIFS covers group by sums, and a PivotTable covers pivot.

Do I need to know SQL?

No, but it helps: select, where, group by and order by work much like their SQL namesakes. The SQL track of this site teaches them.

Related functions