Google Sheets QUERY Function: SQL-Style Data Analysis in Your Spreadsheet

The QUERY function is arguably Google Sheets' most powerful built-in function. It accepts a range of data and a query string written in the Google Visualization Query Language (GQL) — a SQL dialect — and returns a dynamically filtered, sorted, or aggregated result set, all within a single formula.

Basic Syntax

=QUERY(data, query, [headers])

• data — The cell range to query (e.g., A1:F200 or a named range).
• query — A GQL query string enclosed in double quotes, or a cell reference to a string.
• headers — Optional. Number of header rows in data (usually 1).

Your First QUERY: Selecting Columns

Think of SELECT as choosing which columns to return. Columns are referenced by letter (A, B, C…) matching their position in the data range.

Return only the Name and Revenue columns from a dataset:

=QUERY(A1:F200, "SELECT A, D", 1)

To return all columns in order:

=QUERY(A1:F200, "SELECT *", 1)

Filtering Rows with WHERE

The WHERE clause filters rows, equivalent to a spreadsheet filter.

Return all rows where Region is "North":

=QUERY(A1:F200, "SELECT A, B, D WHERE B = 'North'", 1)

Important: String values in GQL must use single quotes, not double quotes (which enclose the entire query string).

Combining Conditions

Use AND and OR to combine multiple conditions:

=QUERY(A1:F200, "SELECT A, B, D WHERE B = 'North' AND D > 5000", 1)

=QUERY(A1:F200, "SELECT A, B, D WHERE B = 'North' OR B = 'South'", 1)

Using Cell References as Filter Values

To make the filter dynamic, concatenate a cell value into the query string. Assume the region filter is in cell H1:

=QUERY(A1:F200, "SELECT A, B, D WHERE B = '"&H1&"'", 1)

For numeric comparisons:

=QUERY(A1:F200, "SELECT A, B, D WHERE D > "&H2, 1)

Sorting with ORDER BY

Sort results by revenue descending, then by name ascending:

=QUERY(A1:F200, "SELECT A, B, D ORDER BY D DESC, A ASC", 1)

DESC = descending (largest first), ASC = ascending (smallest first, the default).

Limiting Results with LIMIT and OFFSET

Return only the top 10 rows:

=QUERY(A1:F200, "SELECT A, B, D ORDER BY D DESC LIMIT 10", 1)

Skip the first 5 rows and return the next 10 (useful for pagination):

=QUERY(A1:F200, "SELECT A, B, D LIMIT 10 OFFSET 5", 1)

Aggregating Data with GROUP BY

Summarize total revenue per region:

=QUERY(A1:F200, "SELECT B, SUM(D) GROUP BY B", 1)

Rename the aggregated column header with LABEL:

=QUERY(A1:F200, "SELECT B, SUM(D) GROUP BY B LABEL SUM(D) 'Total Revenue'", 1)

Available Aggregate Functions

Filtering with Date Columns

Dates in GQL use the date 'YYYY-MM-DD' literal syntax:

=QUERY(A1:F200, "SELECT A, B, D WHERE C >= date '2024-01-01'", 1)

To use a dynamic date from cell J1 (which contains a date value):

=QUERY(A1:F200, "SELECT A, B, D WHERE C >= date '"&TEXT(J1,"yyyy-mm-dd")&"'", 1)

The TEXT function formats the date serial number into the string format GQL requires.

QUERY Across Multiple Sheets with IMPORTRANGE

Combine QUERY with IMPORTRANGE to query data from another Google Sheets file:

=QUERY(IMPORTRANGE("spreadsheet_url","Sheet1!A1:F200"), "SELECT Col1, Col4 WHERE Col2 = 'North'", 1)

Note: When using IMPORTRANGE as the data source, use Col1, Col2... notation instead of A, B... since the columns are renumbered.

Common Errors and Solutions