Using QUERY in Google Sheets: Syntax, Examples, and Tips

In Google Sheets, use =QUERY(data, query, headers) to filter, sort, select, or summarize rows with a query-language string. For example, =QUERY(A1:C6,"select A, C where B > 100 order by C desc",1) returns the region and representative for rows where sales exceed 100, sorted by representative in descending order.

What QUERY Does in Google Sheets

QUERY is a Google Sheets function for working with a range of data using clauses such as select, where, and order by. Rather than writing separate formulas to filter and rearrange rows, you describe the result you want in one query string. The output appears in the cell containing the formula and spills into the cells below or beside it as needed.

The function is useful when a table has many rows, when you want a compact report that updates as source data changes, or when the same conditions would otherwise be repeated across several formulas. It is not full SQL: it does not connect tables with joins or change source data. QUERY reads the supplied range and returns a result based on it.

For example, a sales table might have columns for region, amount, and salesperson. A query can show only sales above a threshold, sort the matching rows, and display just the region and salesperson columns. A different query can group sales by region and calculate a total.

Syntax and the Headers Argument

The function has three arguments: QUERY(data, query, [headers]). The data argument is the cell range or array to analyze. The query argument is text containing the requested operations. The optional headers argument tells Sheets how many rows at the top of the data range are column headings.

data: for example, A1:C100 or a named range.
query: a quoted instruction such as "select A where B > 10".
headers: usually 1 when the range starts with one heading row, or 0 when it contains no heading row.

If you omit the headers argument or use -1, Sheets tries to infer the number of header rows. That inference can be wrong, especially when the first data row resembles a heading or when the range has an unusual layout. Providing the count explicitly makes formulas easier to understand and helps avoid unexpected labels or missing rows.

Build a Basic Filter and Sort

Suppose A1:C6 contains these headings and records: Region, Sales, Rep; North, 120, Ava; South, 90, Ben; North, 150, Chen; West, 110, Dee; and South, 130, Eli. With those headings in row 1, enter this formula in an empty cell:

=QUERY(A1:C6,"select A, C where B > 100 order by C desc",1)

The result has four rows: North–Chen, South–Eli, North–Ava, and West–Dee. The query selects columns A and C, keeps rows whose value in B is greater than 100, and sorts by column C in descending order. The source table is unchanged.

Use select * when you want every column in the matching rows. For example, "select * where B > 100" returns all three columns for qualifying records. The asterisk is convenient for exploration, while explicitly naming columns makes a report narrower and less likely to change shape if the source range gains extra fields.

Useful QUERY Clauses

Query clauses are written inside the second argument, in a recognizable order. The query language is based on the Google Visualization API Query Language, not on every feature of database SQL. These clauses cover most spreadsheet reporting tasks:

select chooses columns or aggregate expressions, such as select A, C.
where filters records, for example where B >= 100 or where A is not null.
order by sorts the output. Add asc or desc to choose direction; ascending is the default.
limit restricts the number of returned rows, while offset skips rows before returning results.
group by groups rows for aggregate calculations such as sum, count, avg, min, or max.
label changes a result column’s displayed heading, and format can set a display format for selected columns.

For multiple conditions, use operators such as and and or, and use parentheses when they make the intended logic clearer. Text values in a query condition are enclosed in single quotes inside the query string. Because the query itself is also enclosed in double quotes in the formula, this nesting is normal: "select A where A = 'North'".

Summarize Data with GROUP BY

QUERY can calculate one summary row per category. Using the same A1:B6 portion of the sample table, where A contains Region and B contains Sales, this formula groups sales by region and labels the total column:

=QUERY(A1:B6,"select A, sum(B) where A is not null group by A label sum(B) 'Total sales'",1)

It returns North with 270, South with 220, and West with 110. Those totals come from 120 + 150, 90 + 130, and 110 respectively. The order of grouped results is not guaranteed unless you add an order by clause, such as order by sum(B) desc. A selected column that is not aggregated generally needs to appear in the group by clause.

Aggregation is useful for concise summaries, but a pivot table may be easier when people need to rearrange categories interactively or inspect subtotals. QUERY is a good fit when the summary should be formula-driven and presented in a predictable layout.

Dates, Text, and Column References

For a date condition, use a date literal in the query language rather than placing a spreadsheet date value directly inside the query text. For example, suppose A1:B4 has headings Date and Item, followed by 2025-12-15 / Pears, 2026-01-03 / Apples, and 2026-05-20 / Plums, with the date cells stored as real dates. This formula returns the two 2026 records:

=QUERY(A1:B4,"select A, B where A >= date '2026-01-01' and A < date '2027-01-01'",1)

The date literal format is date 'yyyy-MM-dd'. Using a less-than boundary for the next year is a practical way to include all dates in a year without needing to account for the last day. A cell that only looks like a date but is actually text may not compare as expected, so check the underlying cell type if date filtering returns surprising rows.

For a normal rectangular range, letters such as A and B refer to columns in that supplied range. When the data argument is an array expression rather than a plain range, use identifiers such as Col1 and Col2 where appropriate. A column letter in the query refers to the sheet column, whereas Col1 means the first column of the input data. That distinction matters when the input is assembled from selected or reordered columns.

Headers, Data Types, and Output Behavior

QUERY assigns a data type to each input column based on the values it finds. When a column mixes types—for example, numbers and text—Sheets uses the majority type for query processing; values of the minority type can be treated as null. A number stored as text may therefore fail a numeric comparison or disappear from an aggregate. Keep each source column consistent, or convert and clean the data before querying.

The result is an array, so it needs room to expand. If an existing value blocks part of the output area, Sheets displays an expansion error rather than overwriting that value. Put the formula in a clear area, and avoid typing notes or other formulas into cells where the result may spill. If the source range grows, adjust it or use a range that includes the expected future rows.

Formatting the source cells does not always mean the output will have the exact display format you expect. QUERY returns values and may infer headings or data types; number and date display can be adjusted in the output cells with the regular Google Sheets formatting controls. The format clause is available for formatting query output, but it does not convert the underlying value into a different data type.

Common Errors and How to Fix Them

A QUERY formula can fail because the query-language text is malformed, the headers count is wrong, or the data does not have the type the condition assumes. When a result is empty, the syntax may be valid but no rows may meet the criteria. Check the source values and temporarily simplify the condition to isolate the issue.

Parse error: Check clause order, spelling, parentheses, and the quotes around text and date literals. The query clause goes inside the quoted second argument.
Unexpected heading or first row: Set the headers argument to the actual number of heading rows instead of relying on automatic detection.
Numbers not matching: Look for numbers stored as text, blanks, or mixed values in the column. Clean the source or use a consistent data type.
Missing records: Confirm comparison boundaries, text spelling, and whether blank cells are excluded by the condition.
Output cannot expand: Clear cells in the intended result area or move the formula to a larger empty area.

One frequent misconception is that QUERY is a spreadsheet version of unrestricted SQL. It supports a useful subset of query operations, but not every SQL function, join, or syntax convention. If an advanced SQL example does not work, check the Google Sheets query-language documentation rather than assuming that a database feature is available.

QUERY Compared with FILTER and Pivot Tables

Choose QUERY when you need filtering plus sorting, column selection, grouping, or a compact formula-based report. Choose FILTER when the main requirement is returning rows that meet one or more conditions and you prefer ordinary cell references rather than a query string. For example, FILTER can be easier to maintain for a simple condition, while QUERY can express a multi-step result in one formula.

A pivot table is often the better choice for interactive analysis, drag-and-drop rearrangement, and built-in subtotal exploration. QUERY is usually more convenient when the result needs to sit in a predictable place and update automatically as its source changes. Neither approach is universally better: consider who will maintain the sheet, whether the output needs to be interactive, and how complex the transformation is.

Google Sheets uses its own QUERY function and query language. Excel has different tools and does not provide Google Sheets’ QUERY function as a direct equivalent. In Excel, users commonly use tools such as Filter, PivotTables, Power Query, or formulas such as FILTER, depending on the task and Excel version. A QUERY formula pasted into Excel will not become an Excel formula automatically.

Frequently Asked Questions

How do I use QUERY in Google Sheets?
Enter =QUERY(range, "query text", header_count) in an empty cell. Replace the range with your data, write clauses such as select and where in the query text, and set the header count to match the input.

What does the third argument in QUERY mean?
It specifies how many rows at the top of the input range are headings. Use 1 for one header row and 0 when there are none. Leaving it out asks Sheets to guess, which can lead to inconsistent results.

Can QUERY filter by more than one condition?
Yes. Use and or or within the where clause. For example, a query can require sales to exceed 100 and the region to equal ‘North’. Keep text values in single quotes inside the query string.

Why does QUERY return blank or missing values?
Check whether any records meet the condition, whether values are stored as the expected type, and whether mixed types occur in a column. Also verify the headers count and the column reference. A query can be syntactically valid while returning no matching rows.

Can Google Sheets QUERY use a cell value as a condition?
Yes, but the query string must be assembled with a valid concatenation and the value must be represented in the query language’s expected form. Text values need quotes inside the query; dates need date literals. For a frequently changing or complex condition, FILTER may be simpler to maintain.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.