Data Transformation FormulaGoogle Sheets V8 Engine Verified

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.

Formula Syntax:=QUERY(data, query, [headers])
πŸ’‘ Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

The QUERY function is widely considered the crown jewel of Google Sheets data processing. It brings SQL-like relational database querying capabilities directly into client spreadsheets by leveraging the Google Visualization API Query Language. Rather than nesting multiple FILTER, SORT, and ARRAYFORMULA functions, a single QUERY statement can filter rows, select specific columns, perform multi-level groupings (GROUP BY), generate dynamic two-dimensional cross-tabulations (PIVOT), and calculate aggregations like SUM, AVG, COUNT, MIN, and MAX. Because QUERY executes within the core calculation pipeline, it operates efficiently on large tables with tens of thousands of rows.

Formula Comparison: QUERY vs. Alternatives

Compared to the traditional FILTER function, QUERY offers far greater projection control because it can reorder, duplicate, or omit columns without manual array brackets. However, FILTER handles mixed-data columns (combining text and numbers) seamlessly, whereas QUERY coerces mixed columns into the dominant data type and treats minority entries as null. For simple filtering without aggregation, FILTER is often faster; for complex multi-condition analytical reporting and dynamic pivots, QUERY is unmatched.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
dataRangeRequiredThe cell range to query (e.g., A1:F500 or IMPORTRANGE).
queryText (SQL String)RequiredThe query string using clauses like SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, and LABEL.
headersIntegerOptionalThe 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)
How it works: Pulls Company (A), Contact (B), and Amount (D) for won deals over $10,000, sorted newest first.

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)
How it works: Groups rows by Department (Column B), calculates sum of Spend (D), and renames the header.

Example 3: Pivot Sales by Region and Quarter

=QUERY(A1:D300, "SELECT A, SUM(D) GROUP BY A PIVOT B", 1)
How it works: Creates an automatic dynamic 2D pivot matrix across Sales Reps (A) and Quarters (B).

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)
How it works: Aggregates revenue and deal count by sales representative for all closed deals in the last 30 days, sorted highest revenue first.

Example 5: Two-Dimensional Cross-Tabulation Matrix (PIVOT)

=QUERY(A1:E2000, "SELECT A, SUM(D) WHERE D > 0 GROUP BY A PIVOT C", 1)
How it works: Groups product categories on the vertical Y-axis and dynamically pivots fiscal quarters across horizontal columns.

Common Errors & Solutions

❌ Error: Unable to parse query string
Resolution: Ensure column letters are uppercase (SELECT A, not select a) and literal strings use single quotes: WHERE B = 'Active'.
❌ Blank or missing values in mixed-type columns
Resolution: QUERY requires a single majority data type per column. If a column contains both numbers and text, QUERY coerces the minority type to null. Convert column to uniform text using ARRAYFORMULA(TO_TEXT(range)).
❌ Formula parse error on date comparisons
Resolution: QUERY requires dates formatted as ISO text strings wrapped in the date literal keyword: WHERE A >= date '2026-01-01'. Use "&TEXT(date_cell, "yyyy-mm-dd")&" for cell references.
❌ Col1, Col2 vs. column letter mismatch
Resolution: When querying direct sheet ranges (A1:D100), use column letters: SELECT A, B. When querying array literals {A1:D100} or IMPORTRANGE, you must use index numbers: SELECT Col1, Col2.

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