How to Use the QUERY Function in Google Sheets
QUERY pulls, filters, and sorts data with one formula using a small SQL-like language. The syntax is =QUERY(data, "SELECT A, B WHERE C > 100", 1). Here is exactly how it works, with real examples of SELECT, WHERE, and ORDER BY.
QUERY is the closest thing Google Sheets has to a database. With one formula it can pull specific columns, filter rows by a condition, sort the results, and even total them — all at once. It uses a small, readable language that looks a lot like SQL, and once you learn three keywords you can replace a whole pile of manual filtering and sorting.
The QUERY syntax, explained
=QUERY(data, query, [headers])| Part | Meaning |
|---|---|
| data | The range to read, like A1:D100 |
| query | The instruction, in quotes, like "SELECT A, B WHERE C > 100" |
| headers | How many header rows your data has (usually 1) — optional |
The three keywords to learn first
- SELECT — which columns to return, by their letter: SELECT A, C. Use SELECT * for all columns.
- WHERE — the condition rows must meet: WHERE C > 100 or WHERE B = 'North'.
- ORDER BY — how to sort the results: ORDER BY C DESC for highest first.
A real example
Say A1:D100 holds a sales table: Region in A, Rep in B, Product in C, Sales in D. To list the Rep and Sales for every row where Sales is over 500, sorted from highest to lowest, you write:
=QUERY(A1:D100, "SELECT B, D WHERE D > 500 ORDER BY D DESC", 1)The result spills automatically into the cells below — you only enter the formula once, in the top-left cell. Change the data and the query result updates on its own.
Common clauses at a glance
| Clause | What it does |
|---|---|
| SELECT A, B | Return only columns A and B |
| WHERE D > 500 | Keep rows where D is over 500 |
| ORDER BY D DESC | Sort by column D, highest first |
| GROUP BY A | Group rows by column A (for totals) |
| LIMIT 10 | Return only the first 10 rows |
QUERY overlaps with tools you may already know. It can filter like sort and filter but as a live formula, look up values like VLOOKUP, and total groups like SUMIF — often all in a single formula.
Frequently asked questions
Do I use column letters or header names in QUERY?
When the data is a normal range like A1:D100, use the column letters (A, B, C) inside the query. Header names only work when the data comes from an external source such as IMPORTRANGE. For everyday sheets, stick to the letters.
Why does my QUERY return #VALUE! or an error?
The most common causes are a missing quote, matching text without single quotes (write WHERE B = 'North'), or mixing text and numbers in one column. Check that the whole query is in double quotes and that text values inside it use single quotes.
Can QUERY total or count data?
Yes. Combine an aggregate with GROUP BY, like =QUERY(A1:D100, "SELECT A, SUM(D) GROUP BY A", 1), to total column D for each value in column A. You can also use COUNT, AVG, MAX, and MIN the same way.
Is QUERY available in Excel?
No — QUERY is unique to Google Sheets. Excel reaches similar results with features like FILTER, tables, or Power Query, but the single-formula QUERY function itself does not exist in Excel.