QUERY Function in Google Sheets

The QUERY function in Google Sheets filters, sorts, and summarizes data using a syntax borrowed from SQL. You write conditions as a text string, and QUERY returns the matching rows and columns.

QUERY is one of the most flexible functions in Google Sheets. A single formula can replace multiple columns of VLOOKUP, FILTER, and SORT combinations. If your data lives in a table and you need a specific slice of it, QUERY is usually the fastest path.

Syntax

=QUERY(data, query, [headers])

Parameters

Parameter	Required	Description
data	Yes	The range or array containing your data. Includes headers if present.
query	Yes	A text string written in Google Visualization API Query Language. Supports SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, and more.
headers	No	The number of header rows in your data. If omitted, Google Sheets guesses based on the content. Set this explicitly when the auto-detection gets it wrong.

Column references depend on the data source. When data is a cell range (like A1:D100), you reference columns as A, B, C, D. When data is an array or the output of another function, you reference columns as Col1, Col2, Col3.

Examples

SELECT with WHERE

Pull the name and salary columns from an employee table where the department is “Sales”:

=QUERY(A1:D50, "SELECT A, D WHERE C = 'Sales'", 1)

This returns only columns A (name) and D (salary) for rows where column C contains “Sales”. The 1 tells QUERY that the first row is a header.

GROUP BY with aggregation

Calculate total revenue by region from a sales table:

=QUERY(A1:C200, "SELECT B, SUM(C) WHERE C IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC", 1)

This groups rows by the value in column B (region), sums column C (revenue) for each group, and sorts the results from highest to lowest. The WHERE C IS NOT NULL clause excludes rows with blank revenue values.

Filtering with a cell reference

Use a value in cell F1 as the filter criteria:

=QUERY(A1:D50, "SELECT A, B WHERE C = '"&F1&"'", 1)

When F1 contains “Marketing”, this becomes WHERE C = 'Marketing'. The single quotes inside the query string are required for text values. For numeric comparisons, drop the quotes: "WHERE D > "&F1.

Common Errors

#VALUE! (column letter mismatch) This appears when you reference a column letter that does not exist in your data range. If your data is A1:C50, you have columns A, B, and C. Referencing column D in the query string produces #VALUE!. Double-check that your SELECT and WHERE clauses only reference columns within the data range.

#N/A (bad query syntax) A syntax error in the query string produces #N/A. Common causes include missing quotes around text values, using == instead of =, or misspelling keywords like SELCT instead of SELECT. The error message usually includes a description of what went wrong — read it carefully.

Mixed data types in a column QUERY treats each column as a single data type based on the majority of its values. If a column has mostly numbers but a few text entries, QUERY may ignore the text rows entirely without warning. Keep your columns consistent.

Tips

When building a query string with cell references, wrap text values in single quotes (') inside the query and use & to concatenate. Numeric and date values do not need quotes. Getting the quoting wrong is the most common source of QUERY syntax errors.

Set the headers parameter explicitly instead of letting Google Sheets guess. Auto-detection works most of the time, but it fails when your first data row looks similar to a header. Passing 1 (or 0 if there are no headers) prevents unexpected results.