Google Sheets QUERY Function: The Complete Guide (With Examples)

The QUERY function is the closest thing Google Sheets has to a database engine. With a single formula, you can filter rows, pick specific columns, sort results, and aggregate totals without helper columns or stacked formulas. If you know a little SQL, the syntax will feel familiar. If you do not, this guide will get you there fast.

My tutorial walks you through the Google Sheets QUERY function, where to use it, its basic syntax, and much more. We will take it one step at a time, starting from simple to complex examples.

What Does Google Sheets QUERY Do?

The QUERY function in Google Sheets applies a query to a data table and returns a filtered, sorted, or summarized result. I use it regularly to extract subsets of data from main datasets, which gives me better analytical insight without touching the source data. In Excel, you’d do this with Power Query. Here QUERY uses SQL-like syntax to evaluate data.

A query can act as a filter, a pivot table, a column selector, and more. If you’ve worked with databases using SQL before, the format of the QUERY function will feel familiar. The function uses Google Visualization API query language, which is closely modeled on SQL syntax.

Google Sheets QUERY Syntax

=QUERY(data, query_string, [headers])

• data: The range of cells containing your data
• query_string: The query you want to run on your data
• headers: The number of header rows at the top of your dataset. This parameter is optional, but always set it explicitly to avoid inconsistent behavior.

The QUERY function reads the query_string, applies it to the given data, and returns the resulting table.

Note: The query_string contains only a query. Nothing else belongs inside it.

The Google Spreadsheets query format is similar to a standard SQL query. A query consists of one or more clauses and can include optional aggregate functions and arithmetic operations.

Clauses and QUERY

A clause is a part of a query that filters or shapes the given data. Some clauses let you customize how your data gets queried. For example, SELECT lets you choose a subset of columns, and WHERE filters those columns by a condition.

Here are all supported query clauses. They must appear in this order when combined:

• Select
• Where
• Group by
• Pivot
• Order By
• Limit
• Offset
• Label
• Format
• Options

You cannot place GROUP BY before WHERE or SELECT. The order above is the required order when building multi-clause queries.

Aggregate Functions and QUERY

Aggregate functions perform calculations across a set of values and return a single result. Use them with GROUP BY to summarize data by category. Supported aggregate functions include:

• SUM: Adds all values in a column or subset
• COUNT: Counts the number of rows in a column or subset
• AVG: Finds the average value in a column or subset
• MIN: Returns the lowest value in a column or subset
• MAX: Returns the highest value in a column or subset

Note: All aggregate functions ignore NULL values except COUNT.

Arithmetic Operations and QUERY

Arithmetic operations let you perform calculations on data selected from your dataset. You can use standard operators: addition (+), subtraction (-), multiplication (*), division (/), and modulus (%). Comparison operators (>, <, =, <=, >=) are also supported inside WHERE conditions.

QUERY vs. FILTER: Which Should You Use?

QUERY and FILTER often solve the same problem, but they are built for different situations. Use this table to decide which one fits your task:

Task	Best Function
Return rows that match one or more conditions	Either works; FILTER is simpler
Return only specific columns	QUERY
Sort the output	QUERY
Aggregate data (SUM, COUNT, AVG by group)	QUERY
Rename output column headers	QUERY
Combine filtering with sorting and column selection	QUERY
Dynamic array that updates with ARRAYFORMULA	FILTER
Filter based on another array or range	FILTER

A practical rule: reach for FILTER when you only need rows that match a condition. Use QUERY when you need to reshape, sort, or summarize the data at the same time.

10 Google Sheets QUERY Examples

To demonstrate how to use the Google Sheets QUERY function, I will apply different queries to the some sample data. You can follow along with the images in the dataset sample here:

1. A Simple QUERY Using SELECT

Start with the simplest possible query: selecting a single column from the data.

=QUERY(A:F,"SELECT A",1)

This formula applies the query “SELECT A” to the dataset in columns A through F. In SQL terms, it is equivalent to “SELECT Name FROM Dataset.” The SELECT clause tells QUERY which columns to include in the output.

The formula returns the Name column because column A holds the Name data. To return the Address column instead, use column D:

=QUERY(A:F,"SELECT D",1)

Using Named Ranges in QUERY

You can also use named ranges as the data argument. Select columns A through F, type the name “Dataset” into the Name Box, and press Enter. Then reference it directly:

=QUERY(Dataset,"SELECT D",1)

Note: The QUERY function returns the data table only, without any source formatting.

Download the QUERY Practice Worksheet

2. Using QUERY to SELECT Multiple Columns

Separate column letters with commas to return more than one column. To display both Name and Address:

=QUERY(Dataset,"SELECT A,D",1)

Use the asterisk (*) in SELECT to return all columns:

=QUERY(Dataset,"SELECT *",1)

3. Filtering Data with SELECT and WHERE

Add a WHERE clause to filter rows by one or more conditions:

=QUERY(Dataset,"SELECT columns WHERE condition(s)",1)

To extract only employees in the Manufacturing department:

=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)

Note: Wrap text values in single quotes inside the query string.

To filter employees who worked more than 30 hours, use a comparison operator. Numbers do not require single quotes:

=QUERY(Dataset,"SELECT * WHERE E>30",1)

You can also combine QUERY with Google Sheets wildcards when you need partial character matching.

WHERE with CONTAINS

Use CONTAINS for partial text matching. To find all employees with an address containing “NJ”:

=QUERY(Dataset,"SELECT * WHERE D contains 'NJ'",1)

Note: CONTAINS is case-sensitive. The string “NJ” will not match “nj” or “Nj”.

WHERE with Regex Matching

For more flexible text matching, use the matches keyword with a regular expression. To return all employees whose department starts with “Man”:

=QUERY(Dataset,"SELECT * WHERE B matches 'Man.*'",1)

The .* pattern matches any characters after “Man.” Regex matching gives you more control than CONTAINS when the text patterns are variable.

WHERE with AND and OR

Combine multiple conditions using AND and OR. To find employees from either NY or NJ:

=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' or D contains 'NY'",1)

To find employees from NJ who are also in the Manufacturing department, use AND:

=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' and B='Manufacturing'",1)

4. Sorting Results with ORDER BY

ORDER BY sorts your output. Add ASC for ascending order or DESC for descending. Combine it with WHERE for sorted, filtered results.

To display Manufacturing employees sorted alphabetically by name:

=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY A ASC",1)

To display the same group sorted by Hours Worked from highest to lowest:

=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC",1)

Using LIMIT with ORDER BY

Add LIMIT to cap the number of rows returned. To show the top 5 Manufacturing employees by hours worked:

=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC LIMIT 5",1)

5. Grouping Data with GROUP BY

GROUP BY is one of the most powerful QUERY clauses, and it takes a little practice to master. It works like a pivot table: it groups rows by a shared value and then applies an aggregate function across each group.

To count how many employees appear in each department:

=QUERY(A1:F9,"SELECT B, COUNT(B) GROUP BY B",1)

To show the total hours worked per department:

=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")

You can combine multiple aggregate functions in a single SELECT. To show count, total hours, and average salary per department in one formula:

=QUERY(A1:F9,"SELECT B, COUNT(B), SUM(E), AVG(F) GROUP BY B LABEL COUNT(B) 'Headcount', SUM(E) 'Total Hours', AVG(F) 'Avg Salary'",1)

6. Pivot Clause

The PIVOT clause is unique to Google Sheets QUERY and has no direct equivalent in standard SQL. It rotates a column’s distinct values into separate header columns, giving you a cross-tabulation view of your data.

Starting from the GROUP BY formula that shows total hours by department:

=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")

Adding PIVOT for employee names produces a breakdown of each person’s hours within each department:

=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B PIVOT A")

The PIVOT column values become the header row of the output table. This is especially useful when you want an in-depth breakdown of a specific variable across groups without building a full pivot table manually.

7. Renaming Column Headers with LABEL

When you use aggregate functions, QUERY generates automatic column labels like “sum Hours Worked.” These labels are functional but not presentable. The LABEL clause lets you rename any column in the output.

To rename the aggregated hours column to “Total Hours Worked”:

=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B LABEL SUM(E) 'Total Hours Worked'",1)

Rename multiple columns by separating each label pair with a comma inside the LABEL clause:

=QUERY(A1:F9,"SELECT B,SUM(E),AVG(F) GROUP BY B LABEL B 'Department', SUM(E) 'Total Hours', AVG(F) 'Avg Salary'",1)

8. Querying Data from Another Tab

To query a dataset in a different tab of the same workbook, reference the sheet name followed by an exclamation mark and the cell range.

To reference cells A2:B10 in a sheet named “MyData”:

MyData!A2:B10

Say you have the following dataset in a sheet named “MyData” and want to display the Name and Department columns in a separate sheet:

=QUERY(MyData!A:F,"SELECT A,B",1)

This formula queries columns A through F in the MyData sheet and returns only the Name and Department columns in the current sheet.

9. Querying Data from Multiple Tabs

When your data spans multiple sheets, combine ranges into a single array using curly braces before passing them to QUERY. Enclose both sheet ranges inside { } and separate them with a semicolon to stack one dataset on top of the other.

Say you have employee data in Sheet1:

And more data in Sheet2:

To query both sheets and return only the Name and Department columns:

=QUERY({Sheet1!A1:F6;Sheet2!A2:F4},"SELECT Col1,Col2",1)

Two things happen in this formula:

• Both sheet ranges combine into a single array by enclosing them in curly braces and separating them with a semicolon.
• Column references inside the query string switch to Col1, Col2, Col3 notation (based on position) rather than A, B, C. This is required whenever the data argument is an array rather than a direct range.

All individual datasets must have the same columns in the same order. Col1 must refer to the same type of data across every source sheet, Col2 must match, and so on.

10. Querying Data from a Different Workbook

To query a dataset in a separate Google Sheets file, use IMPORTRANGE as the data argument inside QUERY:

=QUERY(IMPORTRANGE(sheet_url,sheet_name!cell_range_reference),"SELECT *",1)

I have the following dataset in a separate workbook:

IMPORTRANGE takes two parameters:

• The sheet URL or spreadsheet ID of the source workbook (both must be enclosed in their own set of quotes)
• The sheet name and cell range to import

To find the spreadsheet ID, copy it from the URL bar of your browser:

To return the first two columns from the external workbook:

=QUERY(IMPORTRANGE("19-M6-FBRLHUlHa8eYxug41vXXJvoRMHT0OmgfvXqmaU","Sheet1!A:F"),"SELECT Col1,Col2",1)

After typing the formula, Google Sheets will ask you to allow access to the referenced workbook. Click “Allow access” to authorize the import.

Important: When IMPORTRANGE is the data source, always use Col1, Col2, Col3 notation in your query string instead of A, B, C. Using letter notation with IMPORTRANGE will cause a parse error. This is the most common mistake when combining these two functions.

Using Literals in the QUERY Function

Literals are fixed values you write directly into a query string to compare or assign against your data. Each data type has its own literal format.

String Literals

Strings are text values. Wrap them in single quotes inside the query string. String comparisons are case-sensitive.

=QUERY(A1:F,"SELECT * WHERE A='Cierra'",1)

To use a cell reference instead of a hard-coded value, concatenate it into the string:

=QUERY(A1:D,"SELECT * WHERE C='"&A6&"'",1)

Number Literals

Number values need no quotes. Use them directly in the WHERE clause:

=QUERY(A1:F,"SELECT * WHERE E=30",1)

To reference a cell value instead:

=QUERY(A1:F,"SELECT * WHERE E="&E2,1)

Date Literals

Date literals require two parts: the keyword DATE followed by the date in yyyy-mm-dd format inside single quotes.

=QUERY(A1:F,"SELECT * WHERE C <= date '1999-06-25'",1)

Important: The column you filter must contain actual date values, not text that looks like a date. If dates are stored as text, use DATEVALUE to convert them first or the comparison will return no results.

Time Literals

Time literals use the keyword TIMEOFDAY followed by the time in hh:mm:ss format. To combine date and time, use the DATETIME keyword with the format yyyy-mm-dd hh:mm:ss.

Boolean Literals

Boolean literals use the values true or false directly in the query string, similar to how you use number literals. You can also substitute a cell reference in place of the value.

Filtering Empty Cells with IS NULL and IS NOT NULL

Two of the most useful WHERE conditions for real-world data are IS NULL and IS NOT NULL. These let you include or exclude rows with empty cells.

To return only rows where the Salary column (column F) has a value:

=QUERY(A1:F9,"SELECT * WHERE F IS NOT NULL",1)

To return only rows where Salary is empty (useful for finding data entry gaps):

=QUERY(A1:F9,"SELECT * WHERE F IS NULL",1)

IS NULL and IS NOT NULL work with any data type: text, numbers, and dates. I use IS NOT NULL regularly when building summary dashboards to make sure incomplete rows do not skew aggregated totals.

Using Cell References Inside QUERY

Hard-coded values inside the query string make formulas rigid and hard to maintain. Concatenate cell references into the string to make QUERY dynamic.

Say cell F1 contains a region name you want to filter by. Build the query string like this:

=QUERY(A1:D100,"SELECT * WHERE B = '"&F1&"'",1)

The ampersands join the query string around the cell value. The single quotes inside the double-quoted string are required for text values.

For numeric cell references, skip the single quotes:

=QUERY(A1:D100,"SELECT * WHERE E > "&F2,1)

Connect F1 to a data validation dropdown and the QUERY output updates automatically every time you change the selection. This is one of the fastest ways to build a lightweight interactive dashboard in Google Sheets.

Handling Empty Results with IFERROR

When a QUERY returns no matching rows, it displays an #N/A error by default. Wrap the formula in IFERROR to show a clean message instead:

=IFERROR(QUERY(A1:F9,"SELECT * WHERE B='Finance'",1),"No results found")

For dashboard use, a blank output is often preferable to an error message. Pass an empty string as the IFERROR value:

=IFERROR(QUERY(A1:F9,"SELECT * WHERE B='"&G1&"'",1),"")

This keeps the dashboard clean when a dropdown selection returns no data.

Difference Between SQL and QUERY

SQL (Structured Query Language) is a full programming language for storing, retrieving, and manipulating data in a relational database. It handles complex operations like table joins, transactions, and large-scale data management.

The Google Sheets QUERY function borrows SQL’s syntax for a much narrower purpose: querying a flat table within a spreadsheet. It does not support table joins or transactions, and it is not designed for large datasets. Think of it as SQL-lite for spreadsheet analysts.

According to Dr. Johns, editor at Hackr.io:

“While the Google Sheet Query function is sufficient for simple, small-scale data tasks, Python is better suited to complex data analysis and manipulation, especially with diverse data formats and advanced calculations thanks to libraries like Pandas and NumPy. Similarly, SQL excels in managing and querying large relational databases, outperforming Google Sheets in complex operations like joining tables and handling transactions.”

Common QUERY Errors

• #N/A: No results match the query conditions. The WHERE conditions may be too restrictive, or a text comparison may have a case or spacing mismatch in the source data.
• #REF: A reference error. The sheet specified in the data argument may not exist, or the output range overlaps with existing data.
• PARSE_ERROR The query string contains a syntax problem. The error message points to where the issue is. Common causes: missing single quotes around text values, incorrect column notation (using A/B/C when Col1/Col2/Col3 is required), or a typo in a clause keyword.
• NO_COLUMN: A column reference is outside the data range. Expand the range to include the column, or correct the column letter.

Tips for Writing QUERY Formulas

1. Always set the headers argument explicitly. Leaving it blank causes inconsistent behavior when data types change near the top of your range. Default to 1.
2. Column letters refer to position within the data range, not the sheet. If your range starts at column C, the first column in the query is still referenced as C, not as the third column of the sheet. When using arrays or IMPORTRANGE, switch to Col1, Col2, Col3.
3. Wrap all text filter values in single quotes. Forgetting the single quotes around text is the single most common QUERY error. Numbers and booleans do not need them; text always does.
4. Use TRIM on source data when string comparisons fail unexpectedly. Extra spaces in cells break exact match filters even when the value looks correct on screen.
5. Build complex queries incrementally. Start with SELECT *, confirm the data loads, then add WHERE, ORDER BY, and other clauses one at a time. This makes it much easier to isolate errors.
6. Use IS NULL and IS NOT NULL to audit data quality. Before aggregating, filter out empty rows to avoid blank values skewing your totals.

Wrapping Up

The Google Sheets QUERY function rewards the time you put into learning it. Once you are comfortable with SELECT, WHERE, ORDER BY, and GROUP BY, you can replace dozens of stacked formulas with a single clean expression. Add PIVOT and dynamic cell references to the mix and you have a lightweight but genuinely powerful data analysis tool built right into your spreadsheet.

If you have questions about any of the examples above, leave a note in the comments.

Related:

• Google Sheets Index Match Multiple Criteria
• How to Use the Google Sheets INDIRECT Formula
• How to Use the 3 Google Sheets Wildcards
• Fix Circular Dependency Errors in Google Sheets
• How to Use IMPORTRANGE in Google Sheets

References

1. Google Developers. Interactive charts and data tools [Internet]. [cited 2026 Mar]. Available from: https://developers.google.com/chart/interactive/docs/querylanguage
2. Google. QUERY function: Google Docs Editors Help [Internet]. [cited 2026 Mar]. Available from: https://support.google.com/docs/answer/3093343
3. MIT. Introduction to Scalar and Vector [Internet]. MIT; [cited 2024 Jan 9]. Available from: https://web.mit.edu/wwmath/vectorc/scalar/intro.html

Frequently Asked Questions

How do I use a QUERY in Google Sheets?

Use the following syntax: =QUERY(data, query_string, [headers]). The data argument is your cell range, query_string contains your SQL-style clauses (such as SELECT, WHERE, and ORDER BY), and headers is the optional number of header rows. In practice, always set headers to 1 to avoid inconsistent output.

How do I use CONTAINS in a QUERY formula?

Add the keyword contains to your WHERE clause followed by the text value in single quotes: =QUERY(A1:F,"SELECT * WHERE D contains 'NJ'",1). CONTAINS performs a partial match and is case-sensitive. For case-insensitive matching, convert the column to lowercase using LOWER before querying, or use the matches keyword with a regex pattern.

How do I combine two ranges in a QUERY formula?

Wrap both ranges in curly braces and separate them with a semicolon: =QUERY({Sheet1!A1:F6;Sheet2!A2:F4},"SELECT Col1,Col2",1). When using an array as the data source, reference columns as Col1, Col2, Col3 (by position) instead of A, B, C. All source ranges must have the same columns in the same order.

How do I make QUERY rows work in Google Sheets?

QUERY works with columns only. To query rows, wrap the formula with TRANSPOSE: =TRANSPOSE(QUERY(TRANSPOSE('sheet name'!A:Z),"WHERE Col1='Item x'",1)). This transposes the data before querying it, then transposes the result back to the original orientation.

How do I query data from another Google Sheet?

From a different tab in the same workbook: =QUERY(MyData!A:F,"SELECT A,B",1). To pull from a separate file, use IMPORTRANGE as the data argument: =QUERY(IMPORTRANGE("spreadsheet_url","Sheet1!A:F"),"SELECT Col1,Col2",1). With IMPORTRANGE, always use Col1, Col2, Col3 notation inside the query string.

How do I use a cell reference inside a QUERY formula?

Concatenate the cell reference into the query string using ampersands. For text: "SELECT * WHERE B = '"&A1&"'". For numbers: "SELECT * WHERE E > "&A1. Text values require single quotes inside the query string; numbers do not. Pair this with a data validation dropdown to build a dynamic filter that updates automatically.

How do I handle empty results in QUERY without showing an error?

Wrap the formula in IFERROR: =IFERROR(QUERY(...),"No results found"). To show nothing instead of an error message, use an empty string as the fallback: =IFERROR(QUERY(...),""). This keeps dashboards clean when a filter returns no matching rows.

How do I filter out blank cells in a QUERY formula?

Use IS NOT NULL in the WHERE clause: =QUERY(A1:F9,"SELECT * WHERE F IS NOT NULL",1). To find rows where a specific column is empty, use IS NULL: =QUERY(A1:F9,"SELECT * WHERE F IS NULL",1). Both work with text, number, and date columns.

What is the difference between SQL and the Google Sheets QUERY function?

SQL is a full programming language for managing relational databases. The Google Sheets QUERY function borrows SQL syntax but applies it only to flat tables within a spreadsheet. It does not support table joins, transactions, or large-scale data operations. For simple filtering, sorting, and aggregation on spreadsheet data, QUERY is excellent. For complex relational queries or large datasets, SQL or Python with Pandas is more appropriate.