Lesson 37: QUERY Function Exercises

QUERY Function

Practice the Google Sheets QUERY function: select columns, filter with where, sort with order by, take the top results with limit, and build summaries with group by and sum.

Task 1 of 5

Use QUERY to return just the Rep and Revenue columns, with their headers. Formula in G1.

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

SQL-style questions on your data

QUERY runs a small query language over a range. One formula can pick columns, filter rows, sort them, total them by group and rename the headers, so it often replaces FILTER, SORT and a pivot table together.

=QUERY(data, query, [headers])

data: the range, including its header row, e.g. A1:E9.
query: the query text, in double quotes.
headers: how many header rows the data has. Usually 1. If you leave it out, Sheets guesses.

The clauses, in the order they must appear

Clause	What it does	Example
`select`	Which columns to return (`*` for all)	`select A, E`
`where`	Keep rows that match	`where B = 'West' and E > 1000`
`group by`	One row per value, with sum/avg/count/min/max	`select B, sum(E) group by B`
`order by`	Sort, `asc` or `desc`	`order by E desc`
`limit`	Keep only the first N rows	`limit 3`
`label`	Rename a column header	`label sum(E) 'Total'`

Rules that catch people out

Text values inside the query use single quotes, because the whole query is already in double quotes: "where B = 'West'".
Columns are named by their sheet letters in uppercase (A, B, C). When the data is the output of another function, such as IMPORTRANGE, use Col1, Col2… instead.
Text comparisons are case-sensitive: 'west' won't match "West".
Each column is treated as one type. In a mostly-numeric column, text values are ignored (they come back blank).