If VLOOKUP is the first formula everyone learns, QUERY is the last one they'll ever need. It's Google Sheets' secret weapon: a single function that can replace VLOOKUP, FILTER, SORT, SUMIF, COUNTIF, and pivot tables combined.

QUERY uses a language called Google Visualization API Query Language. It looks like SQL. If you've ever written a database query, you'll feel right at home. If you haven't, that's fine. This guide starts from zero.

The Syntax

QUERY syntax =QUERY(data, query, [headers])
  • data: The range containing your data (e.g., A1:F1000 or Sheet1!A:F)
  • query: A string with SQL-like syntax (e.g., "SELECT A, B WHERE C > 100")
  • headers: Number of header rows in your data (usually 1)

Columns are referenced by letter: A, B, C, etc., corresponding to the columns in your data range.

SELECT: Choose Your Columns

Return only Name and Email columns =QUERY(A1:F100, "SELECT B, D", 1)

This returns only columns B (Name) and D (Email) from your data. Like picking columns from a database table.

Return all columns =QUERY(A1:F100, "SELECT *", 1)

WHERE: Filter Your Rows

Rows where Status = "Active" =QUERY(A1:F100, "SELECT * WHERE E = 'Active'", 1)

Important: Text values in WHERE use single quotes inside the double-quoted query string. Numbers don't need quotes.

Rows where Amount > 500 =QUERY(A1:F100, "SELECT A, B, D WHERE D > 500", 1)

Multiple conditions

AND conditions =QUERY(A1:F100, "SELECT * WHERE E = 'Active' AND D > 500", 1)
OR conditions =QUERY(A1:F100, "SELECT * WHERE E = 'Active' OR E = 'Pending'", 1)

Date filters

Rows after a specific date =QUERY(A1:F100, "SELECT * WHERE C > date '2026-01-01'", 1)
Last 30 days (dynamic) =QUERY(A1:F100, "SELECT * WHERE C > date '" & TEXT(TODAY()-30, "yyyy-mm-dd") & "'", 1)

CONTAINS and LIKE

Text contains a substring =QUERY(A1:F100, "SELECT * WHERE B CONTAINS 'smith'", 1)
Wildcard matching with LIKE =QUERY(A1:F100, "SELECT * WHERE B LIKE 'John%'", 1)

ORDER BY: Sort Results

Sort by amount, highest first =QUERY(A1:F100, "SELECT A, B, D ORDER BY D DESC", 1)
Sort by multiple columns =QUERY(A1:F100, "SELECT * ORDER BY E ASC, D DESC", 1)

GROUP BY: Aggregate Data

This is where QUERY becomes a reporting powerhouse. GROUP BY collapses rows and applies aggregation functions.

Total sales by department =QUERY(A1:F100, "SELECT E, SUM(D) GROUP BY E ORDER BY SUM(D) DESC LABEL SUM(D) 'Total Sales'", 1)

Aggregation functions available: SUM, COUNT, AVG, MAX, MIN.

Average order value by month =QUERY(A1:F1000, "SELECT MONTH(C)+1, AVG(D), COUNT(A) GROUP BY MONTH(C)+1 LABEL MONTH(C)+1 'Month', AVG(D) 'Avg Order', COUNT(A) 'Orders'", 1)
Why MONTH(C)+1?

QUERY's MONTH() function returns 0-11 (January = 0). Adding 1 gives you the normal 1-12 range.

PIVOT: Create Pivot Tables in a Formula

Revenue by department, pivoted by quarter =QUERY(A1:F1000, "SELECT E, SUM(D) GROUP BY E PIVOT F", 1)

Where column F contains quarter labels (Q1, Q2, etc.). PIVOT automatically creates columns for each unique value, exactly like a pivot table, but in a formula.

LABEL: Rename Column Headers

Clean up output headers =QUERY(A1:F100, "SELECT E, SUM(D), COUNT(A) GROUP BY E LABEL E 'Department', SUM(D) 'Revenue', COUNT(A) 'Deals'", 1)

LIMIT and OFFSET

Top 10 customers by revenue =QUERY(A1:F1000, "SELECT B, SUM(D) GROUP BY B ORDER BY SUM(D) DESC LIMIT 10 LABEL SUM(D) 'Total Revenue'", 1)

Using Cell References in QUERY

You'll often want to filter based on a dropdown or input cell. Concatenate the cell value into the query string:

Filter by dropdown value in G1 (text) =QUERY(A1:F100, "SELECT * WHERE E = '"&G1&"'", 1)
Filter by numeric cell value in G1 =QUERY(A1:F100, "SELECT * WHERE D > "&G1, 1)

Notice: text values need the single quotes around the concatenated cell reference; numbers don't.

QUERY Across Multiple Sheets

Combine two sheets and query =QUERY( {Sheet1!A1:D; Sheet2!A2:D}, "SELECT * WHERE Col4 > 100", 1)

Important: When combining ranges with { }, column references change from letters to Col1, Col2, etc.

Common QUERY Errors

  • "Unable to parse query string": Check your quote nesting. Text values need single quotes inside the double-quoted string.
  • Wrong data type: Mixing numbers and text in the same column confuses QUERY. The majority data type wins; minority cells show as null.
  • Headers showing as "sum" or "avg": Use LABEL to rename aggregated columns.
  • Date not working: Use date 'yyyy-mm-dd' format. Dates must be in the QUERY date literal format.

QUERY vs Everything Else

  • QUERY vs FILTER: FILTER is simpler for basic row filtering. QUERY wins when you need GROUP BY, aggregation, or column selection.
  • QUERY vs SUMIFS: SUMIFS returns a single number. QUERY returns an entire table with multiple rows and columns.
  • QUERY vs Pivot Table: Pivot tables require manual updates. QUERY formulas update automatically as data changes.
  • QUERY vs VLOOKUP: VLOOKUP returns one value. QUERY returns filtered, sorted, aggregated datasets.
Once you learn QUERY, you'll use it for everything. It's the most versatile function in Google Sheets.