Query Table Google Sheets: How to Filter and Summarize

To query a table in Google Sheets, use the QUERY function to select, filter, sort, or summarize rows. With headers in row 1 and data in columns A through D, for example, =QUERY(A1:D100,"select A, B, D where D >= 100 order by D desc",1) returns rows whose value in column D is at least 100, sorted from highest to lowest.

What a query table means in Google Sheets

Google Sheets does not have a separate object called a query table that you create from a dedicated menu. People usually mean a result table made with the QUERY function. The formula reads a range, applies instructions written in Google’s query language, and returns matching data in the cells below and beside the formula.

This is useful when a source table changes regularly. Instead of manually copying rows that meet a condition, you can make a formula-driven view that updates when the source data changes. A query can also group records and calculate totals, so it can do some jobs that might otherwise call for a filter or pivot table.

The function’s query language resembles SQL, but it is a Google Visualization API query language, not a complete SQL database system. It works on the supplied spreadsheet data; it does not create a database connection or modify the original rows. The source range remains intact, and the results appear separately.

How the QUERY function works

The general structure is QUERY(data, query, [headers]). The first argument is the range to examine. The second is a text string containing instructions such as which columns to return and which rows to include. The optional third argument tells Sheets how many header rows are at the top of the range.

Data: Use a continuous range such as A1:D100. Including the header row is often convenient.
Query: Put the query language instructions inside quotation marks. Common clauses include select, where, order by, and group by.
Headers: Enter 1 for one header row, 0 if there are none, or the actual number of header rows when there are several.

In a normal sheet range, you can refer to columns by their sheet letters, such as A or D. A query can return a different order of columns from the source, and it can leave some columns out. The function outputs a rectangular result; leave enough empty space around the formula for that result to expand.

Filter and sort rows with a simple query

Suppose a sales sheet has headings in A1:D1: Date, Region, Product, and Sales. Rows 2 onward contain the records. To show only West-region sales of at least 100 and put the largest sales first, use the following formula. The example assumes the source data is in A1:D100, including its one header row.

=QUERY(A1:D100,"select A, B, C, D where B = 'West' and D >= 100 order by D desc",1)

The result contains the four selected columns, but only rows where Region is West and Sales is 100 or more. desc means descending order; use asc for ascending order. Text values inside the query are written with single quotation marks, while the entire query string is enclosed in double quotation marks for the spreadsheet formula.

You can change the selection without changing the source table. For instance, choosing only A and D returns dates and sales values, while adding another condition with and narrows the results further. Conditions can compare numbers, match text, or test dates, provided the source column contains consistently typed data.

Group records and calculate totals

A query can turn transaction rows into a summary. Using the same headings and range described above, this example totals sales by product for the West region and sorts the products by their totals. The data layout is A: Date, B: Region, C: Product, D: Sales, with a header row in row 1.

=QUERY(A1:D100,"select C, sum(D) where B = 'West' group by C order by sum(D) desc label C 'Product', sum(D) 'West sales'",1)

If West has sales of 120 for Tea and 150 for Coffee, the result lists Coffee with 150 and Tea with 120. The sum(D) expression adds the values in column D, and every selected non-aggregated column—in this case C—must appear in group by. The label clause replaces generated headings with clearer names.

Other aggregate expressions include count, avg, min, and max. For example, grouping by region and counting records can show how many transactions each region has. For a straightforward cross-tabulation, the query language also has a pivot clause, though pivot tables may be easier to configure when readers need interactive controls.

Work with dates, text, and headers

Dates need attention because the query language distinguishes date values from ordinary text. If column A contains real spreadsheet dates, a date condition uses the date literal format shown below. This example assumes A1:D100 has the same headings as above and filters for dates on or after January 1, 2026.

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

The date in the source should be stored as a date value, not merely as text that looks like a date. Text matching is different: a condition such as B = 'West' looks for a matching text value. Query operators also include comparisons such as > and <=, and text conditions can use operators including contains and starts with.

The third argument matters because Sheets needs to distinguish headings from records. If the range starts at row 2 and contains only data, use zero headers. If you omit this argument, Sheets attempts to infer the number of header rows, which may not produce the intended labels. Set it explicitly when your range has a known structure.

Mixed data types can also cause surprises. In a column containing mostly numbers but a few text entries, QUERY may treat values of the less common type as null for query purposes. Keep each column consistent—dates in date columns, numbers in numeric columns, and text in text columns—to make filtering and aggregation dependable.

Put the result in a useful table

Enter the formula in a blank cell outside the source data, often on a separate summary sheet. For example, if the source is on a tab named Sales data, the range can be written with the sheet name and an exclamation mark: 'Sales data'!A1:D100. Quotation marks around a sheet name are needed when it contains spaces or certain special characters.

The result spills into adjacent cells automatically. Do not type into cells where the result needs to expand; occupied cells can prevent the output from displaying and trigger an array expansion error. Also avoid placing a query result inside the very range it reads, since that can create a circular reference or overwrite the intended layout.

For ongoing records, a range such as A:D can include new rows added later, but whole-column ranges may require more work than a bounded range on very large sheets. Choose a range that fits how the data is maintained. Keep a single header row at the top and avoid blank rows or notes inside the data block, because they can complicate interpretation.

Common problems and how to fix them

Formula parse error: Check that the outer formula’s quotation marks are balanced and the query text is inside them. Text values inside the query need their own single quotation marks. In some spreadsheet locales, the formula argument separator differs; use the separator expected by the sheet’s locale.
Unrecognised column or unexpected output: Confirm that the column letters refer to the range you supplied and that the header count is correct. If the range starts in a different column, check the references rather than assuming its first column is A.
Missing totals or blank-looking results: Look for inconsistent types, blank cells, and numbers stored as text. Check that every selected detail column is included in group by when using an aggregate.
Array result was not expanded: Clear cells in the output area, including cells that appear empty but contain spaces or formulas. The query cannot overwrite existing content.
Results omit new records: Extend the source range or use a range that includes future rows. Make sure new records are added within the range and follow the same column structure.

When the output does not match expectations, test a smaller query first: return one column, then add a condition, then add grouping or sorting. This isolates whether the issue is the range, a data type, or a clause in the query string.

QUERY versus filters, pivot tables, and Excel

Use QUERY when you want a formula-based view that updates as its source changes, especially when several conditions or a grouped summary are needed. A standard filter is simpler when you want to inspect or temporarily hide rows in the original table. A filter view is useful when different people need their own view without changing what others see.

Google Sheets pivot tables are another good choice for summaries that people need to rearrange by dragging fields. QUERY can be more compact and reproducible once the desired output is known; a pivot table offers a more visual way to explore categories. Neither replaces good source-data structure: meaningful headings and consistent columns help both.

Excel does not include Google Sheets’ QUERY function, so a QUERY formula is not portable as-is to Excel. Excel users can use tools such as AutoFilter, PivotTables, Power Query, or worksheet functions for comparable tasks, depending on whether they need an interactive view, a summary, or a transformation process. Choose the tool based on the workflow, not just on a similar name.

Frequently Asked Questions

Does Google Sheets have a query table feature?
It has the QUERY function, which creates a formula-driven result table from a range. There is no separate query-table object that must be created first.

How do I query a table in Google Sheets?
Enter a QUERY formula in a blank cell, provide the data range and a query string, and specify the number of header rows. Use clauses such as select to choose columns and where to filter rows.

Can QUERY sum values by category?
Yes. Select the category and an aggregate such as sum, then use group by for the category column. The category column must be grouped when it is returned alongside the calculated total.

Why is QUERY returning blank or incorrect rows?
Check the range, header count, criteria spelling, and column data types. In particular, verify that numbers and dates are stored as values of those types rather than as text, and that the condition uses the correct column.

Can I use a QUERY formula in Microsoft Excel?
No. Google Sheets’ QUERY function is not an Excel function. In Excel, use a suitable alternative such as filters, a PivotTable, Power Query, or formulas available in your Excel version.

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.