QUERY function

Query a dataset using SQL-like syntax to filter, sort, and extract specific rows and columns that match your conditions.

=QUERY(data, query, [headers])

Generate a QUERY formula

Describe what you need. The generator will reach for QUERY where QUERY is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How QUERY reads its arguments
datarequiredqueryrequiredheadersoptionalQUERY →
ArgumentRequiredDescription
dataRequiredA range of cells containing the dataset to query, typically including headers as the first row; must be a contiguous rectangular range.
queryRequiredA Google Visualization API Query Language string specifying SELECT, WHERE, ORDER BY, LIMIT, and other clauses; must be a valid SQL-like statement.
headersOptionalThe number of header rows in the data range (default is 1); use 0 if there are no headers, or -1 to auto-detect.

Returns

A range of cells containing rows and columns that satisfy the query criteria, including headers if requested.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Find all Tesla maintenance records

VehicleOdometerService DateCostGarage
Tesla450002024-01-15250Downtown
Honda620002024-01-20180Midtown
Tesla480002024-02-10450Downtown
Toyota880002024-02-15320Uptown
Honda650002024-03-05220Midtown
Tesla510002024-03-10275Downtown
Toyota910002024-03-20500Uptown
Honda680002024-04-01190Downtown
=QUERY(A1:E8,"SELECT * WHERE A='Tesla'")

Result: VehicleOdometerService DateCostGarageTesla450002024-01-15250DowntownTesla480002024-02-10450DowntownTesla510002024-03-10275Downtown

The WHERE clause filters rows where column A (Vehicle) equals 'Tesla'. QUERY returns only matching rows with all columns (SELECT *). The result includes the header row and three matching Tesla maintenance records.

2. Find expensive services sorted by cost descending

VehicleOdometerService DateCostGarage
Tesla450002024-01-15250Downtown
Honda620002024-01-20180Midtown
Tesla480002024-02-10450Downtown
Toyota880002024-02-15320Uptown
Honda650002024-03-05220Midtown
Tesla510002024-03-10275Downtown
Toyota910002024-03-20500Uptown
Honda680002024-04-01190Downtown
=QUERY(A1:E8,"SELECT A,C,D WHERE D>300 ORDER BY D DESC")

Result: VehicleService DateCostToyota2024-03-20500Tesla2024-02-10450Toyota2024-02-15320

The WHERE clause filters to costs greater than 300, and ORDER BY D DESC sorts by column D (Cost) in descending order. SELECT A,C,D extracts only Vehicle, Service Date, and Cost columns. The result shows three expensive services sorted highest cost first.

3. Get vehicle and cost columns for Honda services

VehicleOdometerService DateCostGarage
Tesla450002024-01-15250Downtown
Honda620002024-01-20180Midtown
Tesla480002024-02-10450Downtown
Toyota880002024-02-15320Uptown
Honda650002024-03-05220Midtown
Tesla510002024-03-10275Downtown
Toyota910002024-03-20500Uptown
Honda680002024-04-01190Downtown
=QUERY(A1:E8,"SELECT A,D WHERE A='Honda'")

Result: VehicleCostHonda180Honda220Honda190

SELECT A,D specifies only columns A (Vehicle) and D (Cost) in the output. The WHERE clause limits results to Honda vehicles. QUERY returns the selected columns with headers and the three matching Honda maintenance records.

Common errors

Which QUERY error are you seeing?
QUERY returned an error#VALUE!
Check the query string for typos and ensure it follows Google Visualization API syntax; for example, use WHERE A='value' not WHERE A=value for text comparisons, and WHERE A='Tesla' AND B>50000 for compound conditions.
#N/A
Verify the filter criteria match existing data; use a simpler query (e.g., SELECT *) to confirm the data range contains values, and check that column references are correct.
#REF!
Verify the data range exists and is a single rectangular block; if referencing another sheet, use 'Sheet Name'!A1:E8 with quotes if the name contains spaces.
ErrorWhy it happensHow to fix it
#VALUE!The query string has invalid syntax, such as a malformed WHERE clause, unsupported operators, or misspelled keywords (e.g., 'WHRE' instead of 'WHERE', or using AND without boolean logic).Check the query string for typos and ensure it follows Google Visualization API syntax; for example, use WHERE A='value' not WHERE A=value for text comparisons, and WHERE A='Tesla' AND B>50000 for compound conditions.
#N/AThe query executed successfully but returned no rows—either no data matches the WHERE condition, the data range is empty, or the column reference is invalid (e.g., selecting column Z when data only has A-E).Verify the filter criteria match existing data; use a simpler query (e.g., SELECT *) to confirm the data range contains values, and check that column references are correct.
#REF!The data range reference is invalid, such as referencing a deleted range, a range that spans non-contiguous cells, or a sheet name with special characters that isn't quoted properly.Verify the data range exists and is a single rectangular block; if referencing another sheet, use 'Sheet Name'!A1:E8 with quotes if the name contains spaces.

Tips and when to use something else

  • Use FILTER for simple conditions instead of QUERY—FILTER is faster and more readable when you don't need sorting or complex aggregations.
  • Column references in QUERY use letters (A, B, C) based on position; if your data has headers, you can reference columns by name within the query string after setting the headers parameter.
  • QUERY supports LIMIT to cap result rows (e.g., 'LIMIT 10'), OFFSET to skip rows, and GROUP BY with aggregations like SUM and COUNT in SELECT clauses.
  • Combine QUERY with IMPORTRANGE to query data from other spreadsheets, or nest it with ARRAYFORMULA to apply the result across multiple cells.

Related functions

FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.SORTSORT arranges array data in ascending or descending order by a specified column, returning a sorted copy as a dynamic array.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.IMPORTRANGEIMPORTRANGE retrieves a specified range of cells from another Google Sheet and displays it in the current sheet.

Frequently asked questions

What's the difference between QUERY and FILTER?
QUERY uses SQL-like syntax and supports sorting, grouping, and aggregations but is slower and more complex. FILTER is simpler and faster for basic filtering on one or two conditions, but lacks sorting and GROUP BY. Use FILTER when speed matters and logic is simple; use QUERY for sophisticated data analysis.
How do I query based on dates in QUERY?
In WHERE clauses, compare dates using the DATE function or ISO format: 'WHERE C >= DATE(2024,1,1)' or 'WHERE C >= "2024-01-01"'. Ensure your data contains actual date values, not text. Dates are compared as numbers, so DATE functions work reliably.
Can I use QUERY to pull data from another sheet?
Yes, use IMPORTRANGE to fetch data from another sheet, then wrap it in QUERY: =QUERY(IMPORTRANGE("spreadsheet_id","'Sheet2'!A:E"),"SELECT * WHERE A='Tesla'"). This combines their power but may be slower than querying the same sheet.
Why does QUERY include empty rows in results?
Empty rows are included if they're part of your data range and don't fail the WHERE condition. Use 'WHERE A IS NOT NULL' to exclude blanks, or trim your data range to exclude trailing empty rows before querying.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17