Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 29 ยท Advanced Formulas

The QUERY Function

Master the Google Sheets QUERY function: SQL-like select, where, group by, order by, limit and label clauses, with clear examples you can adapt.

QUERY is the most powerful single function in Google Sheets. You give it a range and a short question written in a SQL-like language, and it returns a new table: only some columns, only some rows, grouped, sorted and relabeled. One QUERY can replace a stack of FILTER, SORT, SUMIF and UNIQUE formulas.

If you know SQL, the language will feel familiar. If you do not, it is only a handful of keywords. Google Sheets cannot run in this page, so formulas are read-only. The Python stand-ins implement the same ideas on a small table, so you can see what each clause does.

A query is a sentence

select Which columns?
where Which rows?
group by Summarize per group.
order by In what order?

Clauses must appear in this order.

1 / 2

The basics

The syntax is QUERY(data, query, [headers]). The query is a text string in double quotes. Columns are not named by their header but by letters of the range: Col1, Col2... when the data comes from an array, or the sheet's own column letters A, B, C when the range is a normal one.

Our sample sheet in A1:E8 (the next examples use it):

TEXT
Region   Rep    Product  Units  Revenue
East     Omar   Pens        40      120
West     Dana   Paper       25      200
East     Ivo    Paper       10       80
North    Omar   Pens        55      165
West     Dana   Pens        30       90
East     Omar   Paper       20      160
North    Ivo    Paper       15      120

The simplest query returns everything:

TEXT
=QUERY(A1:E8, "select *")

To choose columns:

TEXT
=QUERY(A1:E8, "select B, E")

The third argument tells Sheets how many header rows the data has. It is usually detected correctly. Write 1 to be sure: =QUERY(A1:E8, "select B, E", 1).

Filtering with where

Add a where clause to keep only matching rows. Text values go in single quotes, numbers need no quotes:

TEXT
=QUERY(A1:E8, "select B, D where A = 'East'")
=QUERY(A1:E8, "select * where D > 20 and C = 'Pens'")
=QUERY(A1:E8, "select * where A = 'East' or A = 'West'")
=QUERY(A1:E8, "select * where B contains 'o'")

The comparison operators are =, != (or <>), <, >, <= and >=. For text there are contains, starts with, ends with, matches (a regular expression that must match the whole value) and like (with % as the wildcard). Test emptiness with is null and is not null.

To use a value from a cell, join it into the string: "select * where A = '" & G1 & "'". For a number, drop the single quotes: "... where D > " & G2. Dates need the keyword date, as in where F > date '2025-01-31', and the TEXT function can build that string from a cell.

Python Stand-in: select and where

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Aggregating with group by

Aggregate functions are sum, avg, count, min and max. When you mix aggregates with ordinary columns, every non-aggregated column must appear in group by:

TEXT
=QUERY(A1:E8, "select A, sum(E) group by A")
=QUERY(A1:E8, "select A, C, sum(D), avg(E) group by A, C")

The result headers are generated, like sum Revenue. Use label to rename them: label sum(E) 'Total revenue'. To format a column, add format: format sum(E) '$#,##0'.

You can pivot as well. pivot C turns the distinct values of a column into columns:

TEXT
=QUERY(A1:E8, "select A, sum(E) group by A pivot C")

That gives one row per region and one column per product. Filtering on an aggregate uses having-like logic, but QUERY has no having. Wrap one query in another, or use a where on the outer one: QUERY(QUERY(...), "select * where Col2 > 150").

Python Stand-in: group by, sum and avg

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Sorting, limiting and labels

The remaining clauses:

TEXT
=QUERY(A1:E8, "select A, sum(E) group by A order by sum(E) desc")
=QUERY(A1:E8, "select * order by E desc limit 3")
=QUERY(A1:E8, "select * limit 3 offset 2")
=QUERY(A1:E8, "select A, sum(E) group by A label sum(E) 'Total'")
  • order by accepts asc (default) or desc, and several keys: order by A, E desc.
  • limit keeps the first n rows and offset skips rows first. limit 1 after a descending order is "the top one".
  • label renames headers. Use it for every aggregate, or the header becomes sum Revenue.
  • format sets a display format, options no_format skips source formatting.

The order of the clauses is fixed: select, where, group by, pivot, order by, limit, offset, label, format, options. Writing them in another order gives a parse error.

Putting a whole range in the data argument

QUERY accepts arrays as data, which allows a lot. You can combine ranges from two sheets with {Sheet1!A1:C10; Sheet2!A2:C10}, or use IMPORTRANGE as the source (see Importing Live Data). When the data is an array, columns are named Col1, Col2, ... instead of letters:

TEXT
=QUERY({A2:A8, D2:D8}, "select Col1, sum(Col2) group by Col1")

A final, important catch: each column in a QUERY must hold one type of data. If a numeric column contains some text, Sheets treats the whole column as text, and a numeric where or sum may return nothing. Clean the data first (see Entering and Editing Data).

Python Stand-in: order by, limit and label

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Wrong quotes. The whole query uses double quotes, text values inside use single quotes. A missing quote gives a parse error.
  • Header names instead of letters. Use select B, not select Rep, unless you built the query from a reordered array.
  • Mixed types in a column. Convert numbers stored as text before querying.
  • Aggregates without group by. select A, sum(E) fails; add group by A.
  • Clause order. Keep the fixed order above.
  • Locale decimals. In some locales the decimal separator in the query text must still be a period.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which query returns the total of column E for each value in column A?

    Aggregate columns need a group by for every other selected column.
  2. 2. How is a text value written inside a QUERY string?

    The query is already in double quotes, so values inside use single quotes. Numbers have no quotes.
  3. 3. Which is the correct clause order?

    QUERY requires select, where, group by, pivot, order by, limit, offset, label, format in that order.
  4. 4. A numeric column returns nothing in a where condition. What is a likely cause?

    A QUERY column has a single type; stray text makes the whole column text.

Summary

  • QUERY(data, "query", headers) runs a SQL-like question over a range and returns a table.
  • Use column letters (or Col1, Col2 for arrays); text values go in single quotes.
  • where filters, group by with sum, avg, count, min, max summarizes, pivot makes columns.
  • order by, limit, offset, label and format finish the result; keep the clause order.
  • Keep each column to one data type.

Next: Importing Live Data