Quick Answer

QUERY Function

QUERY lets you run SQL-like commands on your spreadsheet data. Filter, sort, aggregate, pivot — all in one formula. It's the Swiss Army knife of Google Sheets data manipulation.

Syntax

QUERY

(data, query, [headers])

Parameters

Parameter	Description	Required
data	The range of cells to query. Include header row if you want to reference columns by name.	Required
query	A query string using Google Visualization API Query Language (similar to SQL). E.g., 'SELECT A, B WHERE C > 100 ORDER BY B DESC'.	Required
headers	Number of header rows at the top of data. -1 = auto-detect, 0 = no headers. Default is -1.	Optional

Basic Example

Select all rows where the region is 'East' and sort by sales descending

=QUERY(A1:E50, "SELECT A, B, D WHERE C = 'East' ORDER BY D DESC", 1)

ResultFiltered and sorted table

SELECT A, B, D picks 3 columns. WHERE C='East' filters by region. ORDER BY D DESC sorts by sales. 1 header row.

Advanced Examples

Example 1: QUERY with aggregation (GROUP BY)

Reporting

Sum sales by region using GROUP BY

=QUERY(A1:E50, "SELECT C, SUM(D) GROUP BY C ORDER BY SUM(D) DESC", 1)

Result: Region | Total Sales

GROUP BY C groups rows by region. SUM(D) totals sales per group. Results are sorted by total descending.

Example 2: QUERY with multiple conditions

Complex filtering

Filter for East region sales above $5000 in Q1

=QUERY(A1:F50, "SELECT A, B, D WHERE C='East' AND D>5000 AND F='Q1'", 1)

Result: Filtered table

Multiple WHERE conditions combined with AND. Each condition is evaluated independently before results are combined.

Example 3: QUERY with IMPORTRANGE for cross-sheet analysis

Multi-sheet data

Query data from another spreadsheet entirely

=QUERY(IMPORTRANGE("sheet_url", "Sheet1!A1:E100"), "SELECT Col1, Col4 WHERE Col3='East'", 1)

Result: Cross-sheet filtered data

IMPORTRANGE pulls data from another spreadsheet. QUERY then filters it. Use Col1, Col2 (not A, B) when querying IMPORTRANGE data.

How QUERY Works

QUERY treats your spreadsheet range like a database table. The query string uses a SQL-like language: SELECT chooses columns, WHERE filters rows, GROUP BY aggregates, ORDER BY sorts, LIMIT restricts row count. Column references use letters (A, B, C) when querying a direct range, or Col1, Col2, Col3 when querying computed arrays like IMPORTRANGE results. The function evaluates the query and returns a dynamic result set that spills into adjacent cells.

1

Set up your data range

Select the full range including headers (e.g., A1:E50). Headers allow column name references.

2

Write your query string

Use SQL-like syntax: SELECT columns, WHERE conditions, ORDER BY, GROUP BY. Enclose in quotes.

3

Specify header rows

Set headers parameter: 1 if one header row, -1 for auto-detect.

4

Enter the formula

Type =QUERY(range, "SELECT A WHERE B > 100", 1) in a cell with space for spilled results.

Important Notes & Limitations

QUERY is exclusive to Google Sheets — there is no equivalent in Excel.
QUERY cannot reference columns by letter (A, B) when the data comes from IMPORTRANGE — use Col1, Col2 instead.
QUERY has limited date handling — dates must be formatted carefully in the query string.
Complex queries may be slow on very large datasets (10K+ rows).
QUERY doesn't support JOIN operations between different ranges.

Common Errors & Fixes

#VALUE! (Col not found)Referencing column by letter (A, B) when data comes from IMPORTRANGE or computed arrays

Fix: Use Col1, Col2, Col3 notation for computed array sources. Letters only work with direct range references.

Empty resultWHERE condition doesn't match any rows, or date format is wrong

Fix: Check that your conditions actually match data. For dates, use date 'YYYY-MM-DD' format in the query.

#N/A (query parse error)Syntax error in the query string

Fix: Verify SQL syntax: quotes around text values, proper SELECT/WHERE/ORDER BY structure, no typos.

Download Practice File

Practice QUERY with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the QUERY function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

ARRAYFORMULA

ARRAYFORMULA wraps a formula and applies it to every cell in a range at once, so you don't need to copy formulas down.

DETECTLANGUAGE

DETECTLANGUAGE tells you what language a piece of text is written in.

GOOGLEFINANCE

GOOGLEFINANCE pulls live stock prices, currency rates, and historical market data into your spreadsheet.

GOOGLETRANSLATE

GOOGLETRANSLATE translates text in a cell from a source language to a target language.

IMAGE

IMAGE inserts a picture from a URL into a spreadsheet cell and lets you control its size and cropping.

IMPORTRANGE

IMPORTRANGE imports a range from another Google Sheets file by its spreadsheet URL and a range reference.

Frequently Asked Questions

What's the Excel equivalent of QUERY?

There's no direct equivalent. FILTER + SORT can handle basic filtering and sorting. For aggregation (GROUP BY), use PivotTables or Power Query. For complex analysis, Power Query is the closest match.

Can QUERY reference another sheet?

Yes. Use the sheet name in the range: =QUERY(Sheet2!A1:E50, ...) or use IMPORTRANGE for cross-spreadsheet data.

How do I handle dates in QUERY?

Use the date keyword: WHERE A > date '2024-01-01'. The date must be in YYYY-MM-DD format regardless of your spreadsheet date formatting.

Can QUERY do pivot tables?

Yes! Use PIVOT in the query: =QUERY(A1:E50, "SELECT C, SUM(D) GROUP BY C PIVOT B", 1) creates a pivot-like summary.

Why does QUERY return Col1 instead of A?

When QUERY operates on computed arrays (IMPORTRANGE results, {array} literals), columns are referenced as Col1, Col2, etc. Letter references (A, B) only work with direct spreadsheet ranges.