Google Sheets QUERY Formula: Syntax, Examples, and Tips

In Google Sheets, the QUERY formula filters, sorts, selects, and summarizes a table using a query written in plain text. For a table in A1:D100 with one header row, a simple starting formula is =QUERY(A1:D100, "select *", 1); it returns the full table, including its columns, while treating the first row as headers.

What the QUERY formula does

QUERY is useful when you want to ask a structured question of a range and have Sheets return a result that updates as the source data changes. A query can return only selected columns, filter records that meet conditions, arrange results, or group rows and calculate totals. The result is an array that spills into nearby cells, so the cells where it needs to appear must be empty.

The function follows this general structure: QUERY(data, query, headers). The data argument is the range or array to examine, the query argument is text containing instructions, and headers is the number of header rows at the top of the data. The third argument is optional. The query language resembles SQL, but it is not a complete SQL implementation; it follows Google Visualization API Query Language rules.

QUERY is especially handy for reports that need several operations together. A single query can, for example, select two columns, keep only rows for one region, sort by sales, and limit the output to the top records. For a simple one-condition filter, however, FILTER may be easier to read.

How to write the query string

The second argument is a text string, so the query goes inside quotation marks in the formula. Within that text, clauses appear in a defined order. A common pattern is select, followed by optional where, group by, pivot, order by, limit, offset, label, and format clauses. You do not need every clause, and many everyday queries use just select and where.

For data in a normal sheet range, columns are commonly identified by their letters, such as A or D. The asterisk in select * means all columns. When the data argument is an array expression rather than a standard range, the query language may refer to columns as Col1, Col2, and so on. Keep the query text in the correct order: for instance, put where before order by.

Text values inside a query condition use single quotation marks, even though the whole query string is enclosed in double quotation marks. This distinction keeps the formula and its query text separate. If the text being searched itself contains an apostrophe, that can require escaping it in the query string, so test the condition on a small range before building a larger report.

Filter and sort rows with WHERE and ORDER BY

Suppose A1:D6 contains a header row and five sales records: column A is Product, B is Region, C is Salesperson, and D is Sales. The records are: Tea, East, Ana, 120; Coffee, West, Ben, 90; Cocoa, East, Cam, 150; Tea, West, Dev, 80; and Coffee, East, Eli, 110. To show the product and sales for East-region records, with the highest sale first, use:

=QUERY(A1:D6, "select A, D where B = 'East' order by D desc", 1)

The output has three rows: Cocoa—150, Tea—120, and Coffee—110, in that order. The first argument includes the headers as well as the records, and the final 1 tells QUERY that the first row contains one header row. The desc keyword sorts from largest to smallest; asc sorts from smallest to largest and is the default if no direction is specified.

Conditions can use operators such as =, <>, >, >=, <, and <=. The query language also supports conditions such as is null, is not null, contains, and starts with. For example, to find rows with sales of at least 100, apply a condition to the numeric Sales column. Use query-language operators inside the quoted string, not spreadsheet operators outside it.

Summarize data with GROUP BY

QUERY can aggregate rows using functions including sum, avg, count, max, and min. When a result includes both a grouped column and an aggregate, the non-aggregated column generally belongs in the group by clause. Using the same six-row sales example above, this formula totals sales by region:

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

It returns East with 380 and West with 170: 120 + 150 + 110 = 380, while 90 + 80 = 170. The label clause changes the displayed heading for the calculated column; it does not rename the source data. Grouped results are often useful for compact summaries that would otherwise require a pivot table.

A related clause, pivot, turns distinct values from a column into result columns. It can be useful for cross-tab summaries, such as comparing totals by region across product categories. Pivoted output can change shape when new category values appear, so leave enough blank space beside and below the formula for the result to expand.

Dates, text, and header rows

Dates need particular care because QUERY interprets values according to their underlying types. In a date condition, the query language expects a date literal in the form date 'yyyy-mm-dd', rather than a date written in the sheet’s display format. For example, assuming A1:C20 has headers and column A contains real dates, column B has customer names, and column C has amounts, this returns records from 2026 onward:

=QUERY(A1:C20, "select A, B, C where A >= date '2026-01-01'", 1)

The formula assumes the cells in column A contain date values, not text that merely looks like a date. If dates were imported as text, convert or clean them in the source data before relying on date comparisons. A displayed date such as 1/2/2026 can also be ambiguous across locales, which is one reason the query’s ISO-style date literal is preferable.

The headers argument describes how many header rows are in the input range, not how many rows of output labels you want. Use 1 when the range begins with one header row, 0 when it contains only data, or another number if it has multiple header rows. If omitted, Sheets attempts to infer the number. Setting it explicitly usually makes a query more predictable, especially when the first data row could be mistaken for a header.

Common errors and ways to fix them

Parse error: Check spelling and clause order in the query string, make sure quotes are balanced, and verify that the formula’s parentheses are closed. Query text uses its own syntax; a SQL clause or function not supported by the Google Visualization query language will not work just because it is valid SQL.
Unexpected blank values: QUERY determines a column’s type from its values. If a column mixes numbers and text, the minority type can be treated as null. Keep each column consistent or clean mixed values in a helper range.
Wrong columns or headers: Confirm the range starts where you think it does and that the headers argument matches its header rows. A range beginning in column C changes the sheet-letter references you should use; check the actual source columns rather than assuming the range begins in A.
Array result was not expanded: Clear cells in the intended output area. A QUERY result may occupy many rows and columns, and existing values can block the spill.
Formula separator error: Examples here use commas between function arguments. Depending on the spreadsheet’s locale, Sheets may expect semicolons instead. Use the separator accepted by your file’s locale while preserving the commas or other punctuation inside the query text as required by the query language.

Also check whether a condition is comparing numbers to numbers and dates to dates, rather than values stored as text. If a result looks truncated, inspect limit and offset clauses. If a summary seems to omit records, check for blank cells, inconsistent types, or a filter condition that excludes more rows than intended.

QUERY compared with FILTER and pivot tables

Choose QUERY when you need several operations in a compact expression, especially column selection, grouping, aggregation, or pivoting. Its query text can be powerful, but it has a learning curve and can be harder for a new spreadsheet user to troubleshoot.

FILTER is often a clearer choice when the goal is simply to return rows that meet one or more conditions while retaining their original columns. A pivot table is a good alternative when you want an interactive summary that can be rearranged through a graphical interface rather than maintained as a query string. These approaches solve overlapping problems, but they are not identical: choose based on whether you value a concise formula, a simple row filter, or an interactive report.

QUERY is a Google Sheets function, not a formula that can be pasted unchanged into Microsoft Excel. Excel has other tools and functions for filtering and summarizing data, but the Google Visualization query-string syntax is specific to Sheets. If a workbook is shared between Sheets and Excel, expect to adapt the formula rather than assuming both applications interpret it the same way.

Frequently Asked Questions

What is the basic QUERY formula in Google Sheets?
Its structure is QUERY(data, query, headers). For example, with a table in A1:D100 and one header row, =QUERY(A1:D100, "select *", 1) returns all columns and identifies the first row as headers.

Can QUERY select only certain columns?
Yes. Use the select clause with the column letters you need, separated by commas, such as select A, C. The letter references correspond to the sheet columns in a normal range.

Why does QUERY return blank or null results for some cells?
A common cause is mixed data types in one source column. QUERY infers the column type, and values of a different type may be treated as null. Standardizing the column’s contents can resolve the issue.

Does QUERY work in Microsoft Excel?
No. Google Sheets’ QUERY function and its query-language syntax are not directly supported in Excel. Use Excel’s own filtering, table, or summarizing features and functions instead.

What does the third QUERY argument mean?
It is the number of header rows in the input data range. Set it to 1 for one header row or 0 for no header rows. You can omit it, but Sheets then tries to infer the header count.

Making a QUERY formula easier to maintain

Start with a small, clearly defined range and add one clause at a time: first select the needed columns, then add a filter, and finally add sorting or aggregation. This makes it easier to identify whether an error comes from the range, a condition, or the query syntax. Once the result is correct, widen the source range if new records will be added, while ensuring the output area remains clear.

Keep the query readable by using consistent column types and an explicit headers argument. If a report will be used by others, consider labeling calculated columns and documenting what the filter includes. Those small choices make a QUERY-based report easier to check, update, and hand off.

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.