Google Sheets QUERY is one of the most powerful functions in the GSuite universe. It’s highly versatile and can be applied to simple and complex problems.
My tutorial walks you through the Google Sheets QUERY, where to use it, its basic syntax, and much more. Don’t worry: We’ll take it one step at a time, starting from simple to complex examples.
Table of Contents
What Does Google Sheets QUERY Do?
The QUERY Google Sheets function helps you apply a query to a data table within Google Sheets. For example, I often use it to extract subsets of data from main datasets. In turn, this helps me review areas of interest, giving me better analytical insight.
A query can be considered a filter, pivot table, and much more. If you’ve worked with databases using SQL before, you will find that the format of the QUERY function is fairly similar. In fact, the QUERY Google Sheets function mainly involves using SQL-style queries in order to apply them to a given dataset.
Google Sheets QUERY Syntax
The QUERY syntax function is as follows:
=QUERY(data, query_string, [headers])
- Data – range of cells containing your data
- Query_string – contains the query that you want to run on your data
- Headers – number of header rows on top of your dataset. This parameter is optional.
The QUERY function reads the query that’s provided in the query_string, applying it to the given data, and returning the resultant table.
Note: The query_string contains nothing but a query.
Interestingly, the Google Spreadsheets query format is similar to the format of a regular SQL query. A query usually consists of one or more clauses and can also contain optional aggregate functions and/or arithmetic operations.
Clauses & QUERY
A clause is a part of a query that lets you filter the given data. Some clauses allow you to customize how your data will be queried.
For example, the SELECT clause lets you select a subset of columns from your dataset. The Google Sheets query WHERE clause supplements the SELECT clause by filtering the selected columns according to a condition.
Here are the possible query clauses for your data. Note that these must be used in this order:
- Select
- Where
- Group by
- Pivot
- Order By
- Limit
- Offset
- Label
- Format
- Options
Remember, you cannot specify “group by” before “where” or “select”. The order above is the order you’ll need to use with the Query function.
Aggregate Functions & QUERY
Aggregate functions are functions that perform a calculation of values. These functions generally return a single value after completing the calculation. Some examples of aggregation functions include:
- SUM – Adds up all the values in a specific column (or a subset of a column)
- COUNT – Counts the number of rows in a given column (or a subset of a column)
- AVG – Finds the average of values in a given column (or a subset of a column)
- MIN – Finds the lowest value in a given column (or a subset of a column)
- MAX – Finds the highest value in a given column (or a subset of a column)
Aggregate functions are usually used in conjunction with the GROUP BY clause (which we’ll review a bit later in this tutorial).
Note: Except for the COUNT function, all the aggregation functions ignore NULL values.
Arithmetic Operations & QUERY
Arithmetic operations are expressions made up of a constant, variable, or scalar function, along with operators like addition (+), subtraction (-), multiplication (*), and division (/) or modulus (%). They can also include comparison operators like >,<,=,<=,>=.
These operators are used to perform mathematical operations on data that’s selected from your main dataset.
10 Google Sheets Query Examples
To demonstrate how to use the QUERY Google Sheets Function, we are going to apply different queries to the following dataset sample:
1. A Simple QUERY Using the SELECT Function
Let us start with a simple query formula in Google Sheets. We want to select and display a single column from the data.
=QUERY(A:F,"SELECT A",1)
The above formula applies the query “SELECT A” to the dataset table A:F.
In SQL, this formula would be equivalent to the query “SELECT Name FROM Dataset” (if the reference columns A:F were named as Dataset).
The SELECT clause tells the function which columns from the main dataset to consider when extracting the data. The query expression is written in the Google Visualization API Query Language (which is quite similar to query expressions used in SQL).
Here’s the result that the above formula returns:
The formula returned the ‘Name’ column from the dataset because this is what column A refers to.
Similarly, if you only want to see the ‘Address’ column, then your QUERY formula would be:
=QUERY(A:F,"SELECT D",1)
Using Named Ranges in QUERY
You can also use Named ranges in your query. For example, you can give the data range A:F the name ‘Dataset’ by selecting the columns A to F and typing the name ‘Dataset’ into the ‘Name’ box (shown below), followed by the return key:
You can then use the name of the range in the QUERY function as follows:
=QUERY(Dataset,"SELECT D",1)
Note: The QUERY Google Sheets formula only returns the data table – but without the formatting.
Access the example sheet by clicking on the following link:
2. Using Query Google Sheets to SELECT Multiple Columns
You can also use the SELECT clause to display more than one column. For example, if you wanted to display both the ‘Name’ and ‘Address’ columns, your QUERY function would look as follows:
=QUERY(Dataset,"SELECT A,D",1)
The asterisk symbol (*) can be used in your SELECT clause to display all columns. For example, you can use the SELECT clause to display all the columns in your dataset as follows:
=QUERY(Dataset,"SELECT *",1)
3. Filtering & Extracting Data Using SELECT with WHERE Clause
If you want to specify certain conditions that a row needs to satisfy to be displayed in a query, then you can add a WHERE clause to your query as follows:
=QUERY(Dataset,"SELECT columns WHERE condition(s)",1)
When added to the SELECT clause, the WHERE clause lets you filter the returned data using one or more conditions. For example, if you want to extract only data for employees of the ‘Manufacturing’ department, use the QUERY function as follows:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)
Note: We put the string ‘Manufacturing’ in single quotes.
You can also use “comparison operators” in the WHERE clause. For example, to extract data for employees who have worked more than 30 hours, you can write a query as follows:
=QUERY(Dataset,"SELECT * WHERE E>30",1)
Note: Since 30 is a number, we did not enclose it in single quotes.
You can also use the QUERY function together with Google Sheets wildcards if you want the conditions to be characters rather than whole words.
Query Using SELECT WHERE Contains
The combination of the SELECT and WHERE clauses is quite powerful and versatile. Combine them in multiple ways to create a wide variety of queries. For example, you can use this combination to not only extract data with exact matches but to also partially match a search string. This is achieved by adding the keyword ‘contains’ to the condition part of the WHERE clause.
For example, you can find data of each employee with an address that contains the string ‘NJ’:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ'",1)
Note: The clause is case-sensitive, so the above formula will only return addresses where the string ‘NJ’ is in capital letters.
Using SELECT WHERE with AND & OR
Add multiple search criteria to your query via logical operators like OR and AND. For example, you can expand the above formula’s search to include all employees from both ‘NY’ and ‘NJ’ as follows:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' or D contains 'NY'",1)
Similarly, to insert two search criteria that both need to be satisfied in order for a row to be considered, you can use the AND operator. If you want to display data for employees who live in ‘NJ’ and are from the ‘Manufacturing’ department, you could use the following formula:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' and B='Manufacturing'",1)
4. Using the QUERY Google Sheets ORDER BY Clause
The ORDER BY clause is typically used in addition to the SELECT and WHERE clauses. It helps sort selected data in either ascending or descending order. You can specify which column you want to order your data by, as well as selecting an ascending or descending order.
For example, say that you want to display data of all employees from the ‘Manufacturing’ department in alphabetical order by name. You can write your QUERY function as follows:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY A ASC",1)
In the same way, to display the data in descending order of ‘‘Hours Worked,’ your QUERY function would be:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC",1)
Using the LIMIT Clause
For a set of data with several points of the same data, you can use the LIMIT clause to show a certain number of entries. For example:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC LIMIT 5",1)
5. Using QUERY to Group Selected Data using the GROUP BY Clause
The GROUP BY clause is one of the most challenging query concepts to get the hang of. The only way to master this clause is with practice. The GROUP BY clause lets you query your data into groups, similar to a pivot table.
The GROUP BY clause is usually combined with aggregate functions like SUM, MAX, and MIN, in addition to the SELECT and WHERE clauses.
For example, a common application of this function is counting the number of rows that match a given category. Here, the data rows are grouped by category, then the number of rows for each group or category is counted.
Say you want to group your employee data by ‘Department’ in order to see how many employees are present in your dataset from each department. In this case, your query would look like this:
=QUERY(A1:F9,"SELECT B, COUNT(B) GROUP BY B",1)
Say that I want to display column B (Department) and also group the column by Department. I also specified that for each department group and want to display the number of rows (by using the aggregate function COUNT(B)).
Similarly, if I display the total number of hours worked by employees in each department, then I could use the following query:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")
6. Pivot Clause
The pivot clause can be a bit tricker.
The GROUP BY clause can be replaced or used with the PIVOT clause. The combination of the two gives a more comprehensive rundown of the data. For example, our GROUP BY formula returns the sum of the hours worked by each department:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")
If I were to add the PIVOT clause for names, the resulting formula would be:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B PIVOT A")
This formula will return a table that looks like this:
The PIVOT clause column is used as the header row for the new table. This is particularly useful for a more in-depth look into a specific portion of a larger table.
7. Column Labels with QUERY Google Sheets Function and LABEL Clause
In the screenshot shown above, the heading for the second column, ‘sum Hours Worked’ has automatically been created by the QUERY function. However, this doesn’t look great as a column heading.
The QUERY Google Sheets function lets you rename the heading for a column by using the LABEL clause. This clause usually comes at the end of the query string.
For example, if you want to rename the second column (obtained by finding the sum of hours worked for each department), you can use the LABEL clause in the QUERY function:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B LABEL SUM(E) 'Total Hours Worked'",1)
Here, I specified that I wanted the label for the column containing SUM(E) to be renamed to ‘Total Hours Worked.’
8. Using QUERY Google Sheets From Another Tab
What if you wanted to query a dataset that’s in another tab of the same workbook?
In Google Sheets, you can refer to cells in a different tab by using the tab’s name, followed by an exclamation mark and a reference to the desired cells.
Say that you want to refer to cell A2:B10 of a sheet named ‘MyData.’ You’d need to write the cell reference as:
MyData!A2:B10.
Now, imagine you have the following dataset in a sheet named ‘MyData.’ You want to display columns A and B (Name and Department) from this dataset into a different sheet.
In this case, your query formula (in the new sheet) would be:
=QUERY(MyData!A:F,"SELECT A,B",1)
This formula queries the dataset in columns A:F in the ‘MyData’ sheet, then selects only columns A and B to be displayed in the new sheet.
9. How To Use QUERY Google Sheets from Multiple Tabs
Imagine that you have data recorded in different sheets within the same workbook. You have the following data in Sheet1:
You have the following data in Sheet2:
If data is spread out like this over multiple sheets, it would be exhausting to arrange all of it into a single tab, especially if you only need one QUERY.
I recommend combining each individual dataset into an array by stacking the rows on top of one another.
To query the data from both Sheet1 and Sheet2 (shown in the above two screenshots) and display just the ‘Name’ and ‘Department’ columns, your QUERY function would be:
=QUERY({Sheet1!A1:F6;Sheet2!A2:F4},"SELECT Col1,Col2",1)
To work, all of your individual datasets need to have the same columns in the same order.
In the above formula, we did two things:
- We combined the datasets from the two individual tabs into a single array. We enclosed both inside curly brackets { } and separated the references with a semicolon (to stack one dataset on top of another).
- Instead of using references A and B to select the first and second columns from the data source, we used column IDs Col1 and Col2, depending on their order.
Again, columns must be consistent across all of your individual datasets. This means Col1 must refer to the ‘Name’ column in all the datasets, Col2 must refer to the ‘Department’ column in all the datasets, and so on.
This query will display the following result in the new sheet:
10. Using QUERY Google Sheets from Another Worksheet
Say that you want to query data that’s located in a different workbook. I have the following dataset in a different workbook’s Sheet1:
To display all columns from this dataset, I could use the QUERY function as follows:
=QUERY(IMPORTRANGE(sheet_url,sheet_name!cell_range_reference),"SELECT *",1)
Note: This is just the general QUERY Google Sheets format.
Here, the IMPORTRANGE function in the first parameter is used to import a range of data from a different workbook. Inside the IMPORTRANGE function, we’d pass two parameters:
- The sheet URL or spreadsheet ID (sheet_url) of the workbook you want to import data from
- The name of the sheet (sheet_name) and reference to the range of cells that you want to import (cell_range_reference)
Note: Both parameters need to be individually enclosed in quotes.
You can find the sheet URL of your source data from the location bar of your browser (as shown below):
To display the first two columns from this dataset in a separate Google Sheets workbook, consider using the QUERY function as follows:
=QUERY(IMPORTRANGE("19-M6-FBRLHUlHa8eYxug41vXXJvoRMHT0OmgfvXqmaU","Sheet1!A:F"),"SELECT Col1,Col2",1)
In this example, the ID displayed in the red box (shown in the above screenshot) is the spreadsheet ID of the Google Sheets worksheet that we want to import. The cells that we want to import are in Sheet1!A:F.
To import the data and execute the QUERY on it, type this formula in a new workbook and press the return key. You will be asked to allow access to the referenced sheet. Click on the “Allow access” button to import the data.
Here’s the output you will see in the new worksheet after allowing access to the source dataset:
Using Literals in the QUERY Google Sheets Function
Literals are values that we can use in Google Sheets queries. These can assign or make comparisons with data types (like dates, time, booleans, strings, and numbers). Each literal has its own variation of the QUERY functions.
String Literals
Strings are data in the form of texts. The literals used for strings are usually enclosed in single or double quotation marks. They are also case-sensitive. For example, in our sheet, the formula would be:
=QUERY(A1:F,"Select * where A='Cierra'",1)
You can also use the cell reference instead of the actual text:
=QUERY(A1:D,"Select * where C='"&A6&"'",1)
Number Literals
Number literals are simply number values. Compared to other literals, they’re simpler to use in a QUERY function. A QUERY formula with a number literal might look like this:
=QUERY(A1:F,"Select * where E=30",1)
To use the cell reference instead of the number, then the formula would change to:
=QUERY(A1:F,"Select * where E="&E2&"",1)
Note: If your data contains no numbers, you can still use number literals in the limit clause.
Date Literals
This literal contains the following section:
- The word DATE
- The actual dates in the format yyyy-mm-dd
For example, if we were looking at Hans’ DOB in our spreadsheet, we would use this formula:
=Query(A1:F,"Select * where C <= date '1999-06-25'",1)
Time Literals
This literal contains the following section:
- The word TIMEOFDAY
- The actual dates in the format hh:mm:ss[.SSS]
Time literals can also be combined with the date:
- The word DATETIME
- The value in the format yyyy-mm-dd hh:mm:ss[.SSS]
Boolean Literals
Boolean literals are values that use true or false logic expressions. These are common with the IF and the IFERROR functions.
Boolean literals are used like number literals in the QUERY formula. You can also use the cell reference in place of the value true or false in the QUERY formula.
Difference Between SQL and Query
Google Sheets SQL (Structured Query Language) is a programming language used to store, retrieve, and manipulate data in a relational database. SQL in Google Sheets supports commands that are of a database type.
Conversely, a QUERY is an operation that’s carried out in that database.
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 Google Sheets Function Errors
- #N/A – There are no results to display from the query. Perhaps the conditions are too restrictive.
- #REF – There is an error with the reference. Perhaps the sheet being specified doesn’t exist, or there is already data in the cell you wish to display the results.
- PARSE_ERROR – There’s a wrong or missing query string. The error message should tell you where you need to look to fix it. It may also be a typo.
- NO_COLUMN – A column has been selected outside the range. You’ll need to expand the range to include the specified column.
Frequently Asked Questions
How Do I Use a QUERY in Google Sheets?
You can add a ton of extra clauses, aggregate functions, and arithmetic to QUERY functions. In their simplest form, you need to use the following syntax:
=QUERY(data, query_string, [headers])
- data refers to the range of the query
- query_string contains the query you would like to run on your data
- headers are the optional input of the number of header rows to include
How Do I Make QUERY Rows in Google Sheets?
The QUERY function only works with columns. That said, you can use the TRANSPOSE function alongside QUERY to query rows:
=TRANSPOSE(QUERY(TRANSPOSE('sheet name'!A:Z), " where Col1='Item x' ",1))
How Do I Query Other Google Sheets?
From another sheet in the same spreadsheet, you’ll have to specify the sheet you’re pulling the data from (like in this sample formula):
=QUERY({Sheet1!A1:F6;Sheet2!A2:F4},"SELECT Col1,Col2",1)
If you need to pull from a different source, you can use the IMPORTRANGE function with the following syntax:
=QUERY(IMPORTRANGE(sheet_url,sheet_name!cell_range_reference),"SELECT *",1)
Wrapping up
Thanks to our step-by-step examples, you should have a better idea of how to use Google Sheets QUERY to extract information from a given dataset. I hope you found this tutorial helpful.
If you have any questions or comments about the article you’ve just read, leave me a note in the comments section!
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 Insert Google Docs Code Blocks [2 Easy Ways]
References
- Google Developers. Interactive charts and data tools [Internet]. [cited 2024 Jan 9]. Available from: https://developers.google.com/chart/interactive/docs/querylanguage
- MIT. Introduction to Scalar and Vector [Internet]. MIT; [cited 2024 Jan 9]. Available from: https://web.mit.edu/wwmath/vectorc/scalar/intro.html
- Rutgers University Department of Computer Science. Notes on Java: Boolean Data Type [Internet]. Rutgers University; [cited 2024 Jan 9]. Available from: https://www.cs.rutgers.edu/courses/111/classes/fall_2011_venugopal/texts/notes-java/data/basic_types/26boolean.html
