The QUERY Google Sheets function is one of the most powerful functions in Google Sheets. It is highly versatile and can be applied to simple and complex problems. However, to a newbie, it might come across as slightly complicated.
The good news is that if you know the function’s rules and look at some examples, you will definitely find a hundred ways to use it.
This tutorial will help you understand the QUERY Google Sheets function, its syntax, and how to use it. To help you know how to apply the QUERY Google Sheets function in different scenarios, we will take it one step at a time and explain with some Google spreadsheet query examples, starting from simpler to more complex queries.
Table of Contents
What Does the QUERY Google Sheets Function do?
The QUERY Google Sheets function helps you apply a query to a data table in Google Sheets. You can use it to extract a subset of data from your main dataset, which can help you look at areas of interest in your data, thereby giving you a better insight.
A query can be considered a filter, pivot table, and more put together. If you’ve worked with databases using SQL before, you will find that the format of the QUERY function is not very different. In fact, the QUERY Google Sheets function mainly involves using SQL-style queries and applying them to a given dataset.
Syntax for the QUERY Google Sheets Function
The syntax for the QUERY function is as follows:
=QUERY(data, query_string, [headers])
Here,
- data is the range of cells containing your data
- query_string is a string containing the query that you want to run on your data
- headers is the number of header rows on top of your dataset. This parameter is optional.
The QUERY Google Sheets function reads the query given in the query_string and applies it to the given data, thereby returning the resultant table obtained after running the query.
The query_string contains nothing but a query. Interestingly, the Google Spreadsheets query format is quite 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.
Before we move further, let us first understand what we mean by clauses, aggregate functions, and arithmetic operations in a query string.
Clauses
A clause is a part of a query that lets you filter the given data. Some clauses also let you customize how you want your data to be queried. For example, the SELECT clause lets you select a subset of columns from your dataset. The Google Sheets query WHERE clause, on the other hand, supplements the SELECT clause by filtering the selected columns according to a condition.
Other examples of query clauses include GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, and more.
Aggregate Functions
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 — This function adds up all the values in a given column (or a subset of a column)
- COUNT — This function counts the number of rows in a given column (or a subset of a column)
- AVG — This function finds the average of values in a given column (or a subset of a column)
- MIN — This function finds the lowest value in a given column (or a subset of a column)
- MAX — This function 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 will look at later in this tutorial).
Note: Except for the COUNT function, all the aggregation functions ignore NULL values.
Arithmetic Operations
Arithmetic operations are basically expressions made up of a constant, a variable, or a 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 selected from your main dataset.
How To Use the QUERY Function in Google Sheets (QUERY Google Sheets Examples)
To demonstrate how to use the QUERY Google Sheets Function, we are going to apply different queries to the following dataset sample:
A Simple QUERY Using the SELECT Function
Let us start with a simple query formula in Google Sheets that selects and displays a single column from the data.
=QUERY(A:F,"SELECT A",1)
The above formula applies the query “SELECT A” on 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).
As we discussed before, the SELECT clause tells the query function what 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)
You could also use Named ranges in the query. So 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.
You can access the example sheet below.
How To Use Query Google Sheets to SELECT Multiple Columns
You can use the SELECT clause to display more than one column too. For example, if you want 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)
Filtering or Extracting Data using SELECT with WHERE Clause
So far, we have been extracting only entire columns using the QUERY function. 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)
The WHERE clause, when added to the SELECT 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, then you can use the QUERY function as follows:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing'",1)
Notice that we put the string ‘Manufacturing’ in single quotes.
Similarly, you can also use comparison operators in the WHERE clause. For example, if you want 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)
Notice that since 30 is a number, we did not enclose it in single quotes.
You can also use the query function together with wildcards if you want the conditions to be characters instead of whole words.
Query Using SELECT WHERE Contains
The combination of the SELECT and WHERE clauses is quite powerful and versatile. So, you can combine them in many ways to create a wide variety of queries. For example, you can use this combination to not just extract data with exact matches but also those that 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 all employees whose address contains the string ‘NJ’ as follows:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ'",1)
Note that 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 and OR
You can add multiple search criteria to your query using logical operators like OR and AND. For example, if you want to expand the above formula’s search to include all employees from both ‘NY’ and ‘NJ,’ you could write your QUERY function as follows:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' or D contains 'NY'",1)
Similarly, if you want to insert two search criteria that both need to be satisfied in order for a row to be considered, then you can use the AND operator. So, you might want to display data for only those employees who live in ‘NJ’ and are from the ‘Manufacturing’ department. Your query would then be as follows:
=QUERY(Dataset,"SELECT * WHERE D contains 'NJ' and B='Manufacturing'",1)
Ordering Your Selected Data Using the QUERY Google Sheets ORDER BY Clause
The ORDER BY clause is usually used in addition to the SELECT and WHERE clauses. It helps sort your selected data in either ascending or descending order. You can specify which column you want to order your data by, as well as the direction in which the ordering should be done (ascending or descending).
For example, if you want to display data of all employees from the ‘Manufacturing’ department in alphabetical order of name, then you can write your QUERY function as follows:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY A ASC",1)
In the same way, if you want to display the data in descending order of, say, ‘Hours Worked,’ your QUERY function can be as follows:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC",1)
Using the LIMIT Clause
If you were using a set of data with several points of the same data, you could also use the LIMIT clause to only show a certain number of entries. For example:
=QUERY(Dataset,"SELECT * WHERE B='Manufacturing' ORDER BY E DESC LIMIT 5",1)
Grouping Selected Data in QUERY Google Sheets using the GROUP BY Clause (Make a Pivot Table)
The GROUP BY clause is one of the most challenging query concepts to get the hang of, and the best and only way to master this clause is by practice. The GROUP BY clause lets you query your data into groups, quite similar to a pivot table.
The GROUP BY clause is usually combined with aggregate functions like SUM, MAX, MIN, etc., in addition to the SELECT and WHERE clauses. For example, a common application of this function is in counting the number of rows that match a given category. Here, the data rows are grouped by category, and then the number of rows for each group or category is counted.
Say you want to group your employee data by ‘Department’ and see how many employees are present in your dataset from each department. In that case, your query would look like this:
=QUERY(A1:F9,"SELECT B, COUNT(B) GROUP BY B",1)
Here, we are saying that we want to display column B (Department), and we want to group the column by ‘Department’ too. We also specified that for each department group, we want to display the number of rows (by using the aggregate function COUNT(B)).
Similarly, if you want to display the total number of hours worked by employees in each department, then you can use the following query:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")
As can be seen from the above examples, the GROUP BY function provides a great way to summarize data into groups.
Pivot Clause
The pivot clause is usually a bit more tricky to wrap your head around. 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:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B")
returned the sum of the hours worked by each department. If we 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 useful when you want an in-depth look into a specific portion of a larger table.
How To Create a Column Label with QUERY Google Sheets Function and LABEL Clause
In the screenshot shown above, you can see that the heading for the second column, ‘sum Hours Worked,’ has been created automatically by the QUERY function. However, this does not really look good 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 as follows:
=QUERY(A1:F9,"SELECT B,SUM(E) GROUP BY B LABEL SUM(E) 'Total Hours Worked'",1)
Here, we specified that we want the label for the column containing SUM(E) to be renamed to ‘Total Hours Worked.’
How To Use QUERY Google Sheets From Another Tab
So far, we have been querying from a dataset that is on the same sheet. But what if you wanted to query a dataset that is 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, followed by the reference to the cells you need.
For example, if you want to refer to cell A2:B10 of a sheet named ‘MyData,’ you would need to write the cell reference as: MyData!A2:B10.
Imagine you have the following dataset in a sheet named ‘MyData,’ and you want to display columns A and B (Name and Department) from this dataset into a different sheet.
In that case, your query formula (in the new sheet) would be as follows:
=QUERY(MyData!A:F,"SELECT A,B",1)
The above formula queries the dataset in columns A:F in the ‘MyData’ sheet and selects only columns A and B to be displayed in the new sheet.
How To Use QUERY Google Sheets From Multiple Tabs
Now, imagine that you have your data in different sheets within the same workbook. For example, say you have the following data in Sheet1:
Besides that, say you have the following data in Sheet2:
If you had data spread out like this over multiple sheets, it would be really tiring to put all the data from all the sheets into a single tab just for the sake of making a query.
A wiser approach would be to combine each of these individual datasets into an array by stacking the rows one on top of 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 then be:
=QUERY({Sheet1!A1:F6;Sheet2!A2:F4},"SELECT Col1,Col2",1)
Note that in order for this query to work, all 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 by enclosing both inside curly brackets { } and separating 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.
This is why the columns must be consistent across all your individual datasets. This means Col1 must refer to the ‘Name’ column in all the datasets, while Col2 must refer to the ‘Department’ column in all the datasets, and so on.
The above query will display the following result in the new sheet:
How To Use QUERY Google Sheets From Another Worksheet
Now, let us look at a situation where you want to query data that is in a different workbook. For example, say you have the following dataset in a different workbook’s Sheet1:
If you want to display all columns from this dataset, then you can use the QUERY function as follows:
=QUERY(IMPORTRANGE(sheet_url,sheet_name!cell_range_reference),"SELECT *",1)
Note that 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 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)
Both parameters need to be enclosed in quotes individually.
You can find the sheet URL of your source data from the location bar of your browser (as shown below):
If you want to display the first two columns from this dataset in a separate Google Sheets workbook, then you can use 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 in the above 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 the queries in Google Sheets to perform for assigning or making comparisons with data types like dates, time, booleans, strings, and numbers.
String literals
Each literal has its own variation of the query formulas. Strings are data in the form of texts, and 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 values that are numbers. They are simpler to use in the query function compared to other literals. The query formula with a number literal in our example sheet would look like this:
=QUERY(A1:F,"Select * where E=30",1)
If you were to use the cell reference instead of the number, then the formula would change to look like this:
=QUERY(A1:F,"Select * where E="&E2&"",1)
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 are in the format HH:mm:ss[.SSS]
Time literals can also be combined with the date where we will have the following:
- 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.
The 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 meaning Structured Query Language is a programming language that is used to store, retrieve, and manipulate data in a relational database. SQL in Google Sheets supports commands that are database-type.
On the other hand, a query is an operation that is carried out in that database.
Common QUERY Google Sheets Function Errors and How to Fix Them
- #N/A — This means there are no results to display from the query. Perhaps the conditions are too restrictive.
- #REF — This means there is an error with the reference. Perhaps the sheet you specified doesn’t exist, or there is already data in the cell you wish to display the results.
- PARSE_ERROR — This error means that there’s a wrong or missing query string. The error message should tell you where you need to look to fix it, or it could be a typo.
- NO_COLUMN — This error occurs when you select a column that is outside the range. You need to expand the range to include the specified column.
Frequently Asked Questions
Using QUERY in Google Sheets can be pretty confusing. Check out these FAQs to help you out, or let us know in the comments if you have any other questions.
How Do I Do a QUERY in Google Sheets?
You can add a ton of extra clauses, aggregate functions, and arithmetic to QUERY functions. But, in their simplest form, you have to use the 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 QUERY Rows in Google Sheets?
The QUERY function only works with columns, but you can use the TRANSPOSE function alongside QUERY to query rows. Like so:
=TRANSPOSE(QUERY(TRANSPOSE('sheet name'!A:Z), " where Col1='Item x' ",1))
How Do I Query Another Sheet in Google Sheets?
From another sheet in the same spreadsheet, you 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)
Conclusion
In this tutorial, we showed you how to use the QUERY Google Sheets function. Using a multitude of examples, we showed you step-by-step how to use this function to query data and extract different types of information from a given dataset. We hope you found the tutorial helpful. Check out our other article to learn how to connect Google Forms to Sheets.
Related:
