Google Sheets QUERY Function: The Complete Guide

QUERY is Google Sheets' secret weapon. It lets you run SQL-like queries on your spreadsheet data — filtering, sorting, grouping, and pivoting without helper columns or complex formula chains. If you learn one advanced Google Sheets function, make it QUERY.

Basic Syntax

The QUERY function takes a data range and a query string written in Google Visualization API Query Language (similar to SQL):

=QUERY(data, query, [headers])

// Example: all rows where Status is "Active"
=QUERY(A1:E100, "SELECT * WHERE E = 'Active'")

The query string supports SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, and FORMAT clauses.

Filtering Data

Text filter:

=QUERY(data, "SELECT A, B, D WHERE C = 'Marketing'")

Numeric filter:

=QUERY(data, "SELECT A, B, C WHERE C > 1000")

Date filter:

=QUERY(data, "SELECT A, B WHERE A > date '2026-01-01'")

Multiple conditions:

=QUERY(data, "SELECT * WHERE B = 'Sales' AND C > 5000 ORDER BY C DESC")

Grouping & Aggregation

QUERY handles aggregation that would require SUMIFS or pivot tables in Excel:

// Sum by category
=QUERY(data, "SELECT B, SUM(C) GROUP BY B")

// Average by department, sorted
=QUERY(data, "SELECT B, AVG(C), COUNT(A) GROUP BY B ORDER BY AVG(C) DESC")

// Pivot table equivalent
=QUERY(data, "SELECT A, SUM(C) PIVOT B")

Using Cell References

Embed cell references in your query string for dynamic filtering:

// Reference a cell value (text)
=QUERY(data, "SELECT * WHERE B = '"&F1&"'")

// Reference a cell value (number)
=QUERY(data, "SELECT * WHERE C > "&F2)

// Reference a date cell
=QUERY(data, "SELECT * WHERE A > date '"&TEXT(F3,"yyyy-mm-dd")&"'")

Common Mistakes

• Column letters in queries refer to the data range, not the sheet — if your data starts at B1, column B in the data is referenced as Col1 in the query… actually no, it's still B. But if you use an array like {A1:A, C1:C}, then use Col1 and Col2.
• Mixed data types cause errors — if a column has both numbers and text, QUERY may skip rows. Ensure consistent data types.
• Date formatting — use date 'yyyy-mm-dd' format in queries, or use TEXT() with cell references.
• Headers — set the third parameter to 1 if your range includes headers, or QUERY may misinterpret the first row.