QUERY practice in Google Sheets

Ask a table questions in a language much like SQL: pick columns, keep the rows you want, total per group and sort, all with one formula.

Start the first exercise

How the QUERY function works in Google Sheets

QUERY lets you ask a table questions in a short, SQL-like language. It takes three arguments: the data range, a query written as text in quotes, and optionally the number of header rows. Inside the query, columns are called by letter, A, B, C, counted from the first column of your range, no matter where the range sits on the sheet.

=QUERY(A1:C6,"select A, sum(C) group by A") totals the Amount column for each region and gives one row per region. To keep only some rows, add where: =QUERY(A1:C6,"select B, C where A = 'North'") shows the rep and amount for the North region. Text inside the query goes in single quotes, numbers need no quotes.

The parts of a query must come in a fixed order: select, where, group by, order by, limit. Numbers in the query always use a decimal point, whatever language your spreadsheet is set to. Each column should hold one type of data, because a column that mixes numbers and text can come back with blanks. The exercises below cover select, where with numbers and text, group by, and order by with limit.

  1. 1Pick columns with selectIn E1, use QUERY to show the Rep and Amount columns of A1:C6.
  2. 2Keep rows with wherePremiumIn E1, use QUERY to show the Rep and Amount of every sale over 100.
  3. 3Match text in wherePremiumIn E1, use QUERY to show the Rep and Amount of every sale in the North region.
  4. 4Totals per group with group byPremiumIn E1, use QUERY to total the Amount for each region.
  5. 5Top results with order by and limitPremiumIn E1, use QUERY to show the Rep and Amount of the two biggest sales, biggest first.

Functions in this topic

Questions

How do I use QUERY with data from another sheet?

Put the sheet name in front of the range: =QUERY(Sheet2!A1:C6,"select A, B"). For another file, wrap the range in IMPORTRANGE: =QUERY(IMPORTRANGE("spreadsheet URL","Sheet1!A1:C6"),"select Col1, Col3"). Imported columns are called Col1, Col2 and so on instead of A, B, C. The first time, Sheets asks you to allow access between the two files.

How do I use QUERY with more than one condition?

Join the tests in where with and or or. =QUERY(A1:C6,"select B, C where A = 'North' and C > 100") keeps only North sales over 100. With or, a row stays if it meets either test. When you mix and and or, add parentheses around the tests that belong together so the query reads the way you mean.

How do I use a cell value in a QUERY?

Break the query text and join the cell in with &. For a number, use =QUERY(A1:C6,"select B, C where C > "&F1). For text, put single quotes around the cell: =QUERY(A1:C6,"select B, C where A = '"&F1&"'"). Change F1 and the result updates.