What is the syntax for the QUERY formula in Google Sheets?

The standard syntax is: =QUERY(data, query, [headers]).

What causes errors in QUERY calculations?

Error: Unable to parse query string: 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: 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)).

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])

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)

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).

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)).

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

Does QUERY recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas

Data Transformation