Google Sheets QUERY Formula: Syntax, SQL Querying & Real Examples
The QUERY function is Google Sheets' most versatile formula, executing a SQL-like Google Visualization API Query Language string across spreadsheet tables to filter, aggregate, group, pivot, and sort data in a single formula.
=QUERY(data, query, [headers])Technical Overview & Engine Mechanics
Formula Comparison: QUERY vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| data | Range | Required | The cell range to query (e.g., A1:F500 or IMPORTRANGE). |
| query | Text (SQL String) | Required | The query string using clauses like SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, and LABEL. |
| headers | Integer | Optional | The number of header rows at the top of data. Defaults to 1 or -1 (auto-detect). |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Filter High-Value Won Deals Sorted by Date
=QUERY(A1:E100, "SELECT A, B, D WHERE D > 10000 AND E = 'Won' ORDER BY C DESC", 1)Example 2: Departmental Expense Sum with Custom Label
=QUERY(A1:D500, "SELECT B, SUM(D) WHERE D is not null GROUP BY B LABEL SUM(D) 'Total Spend'", 1)Example 3: Pivot Sales by Region and Quarter
=QUERY(A1:D300, "SELECT A, SUM(D) GROUP BY A PIVOT B", 1)Example 4: Dynamic Rolling 30-Day Closed Sales Summary
=QUERY(A1:F5000, "SELECT B, COUNT(A), SUM(D) WHERE E = 'Closed Won' AND C >= date '"&TEXT(TODAY()-30,"yyyy-mm-dd")&"' GROUP BY B ORDER BY SUM(D) DESC LABEL COUNT(A) 'Deals', SUM(D) 'Revenue'", 1)Example 5: Two-Dimensional Cross-Tabulation Matrix (PIVOT)
=QUERY(A1:E2000, "SELECT A, SUM(D) WHERE D > 0 GROUP BY A PIVOT C", 1)Common Errors & Solutions
Performance Pro Tips
- When querying array literals or IMPORTRANGE, refer to columns by index number (`Col1`, `Col2`) rather than letters (`A`, `B`).
- Always explicitly pass `1` for the headers argument to prevent Google from guessing header row counts incorrectly.
Frequently Asked Questions: QUERY
Why does QUERY return empty cells for some rows in a column?
Google Sheets QUERY expects uniform data types per column. If a column is 80% numeric and 20% text, QUERY treats text entries as blank. Fix this by wrapping the source range in ARRAYFORMULA(TO_TEXT(range)) before passing it to QUERY.
Can I reference cell values dynamically inside a QUERY string?
Yes. Concatenate cell references using quotes and ampersands: "SELECT A, B WHERE C = '" & B1 & "' AND D > " & B2. String values require single quotes; numeric values do not.
What is the maximum number of rows QUERY can process efficiently?
QUERY easily handles 20,000 to 50,000 rows. For datasets approaching the 10 million cell limit, consider offloading computations to Google Cloud BigQuery or using Connected Sheets.
Can QUERY sort by calculated aggregate columns?
Yes. Use ORDER BY followed by the aggregation clause: SELECT B, SUM(C) GROUP BY B ORDER BY SUM(C) DESC.
Is the QUERY function supported in Microsoft Excel?
No. QUERY is exclusive to Google Sheets. Excel users must use Power Query or a combination of FILTER, SORT, and SUMIFS.
Related Google Sheets Formulas
ARRAYFORMULA
ARRAYFORMULA enables single spreadsheet formulas to output an entire matrix or column of calculated results automatically. When new rows are entered, ARRAYFORMULA calculates them instantly without requiring manual formula dragging.
Data TransformationFILTER
FILTER dynamically outputs only the rows or columns from a source range that satisfy one or more logical conditions. As source data updates, filtered results re-calculate automatically.
Data TransformationCHOOSEROWS
CHOOSEROWS extracts specific rows from an array or range by their index numbers. Supports negative indexing to pull rows from the bottom of a dataset.