How to Use the Google Sheets QUERY Function (Step-by-Step)

The QUERY function is perhaps one of the most powerful functions in Google Sheets. It is highly versatile and can be applied to problems both simple and complex. However, to a newbie, it might come across as slightly complicated.

The good news is that if you know the rules of the function and take a look at some examples, you will definitely find a hundred ways to use it.

In this tutorial, we will help you understand the Google Sheets QUERY function, its syntax and how it is used. To help you know how to apply the function in different scenarios, we will take it one step at a time and explain with examples, starting from simpler to more complex queries.

What Does the Google Sheets QUERY Function do?

The QUERY function helps you apply a query to a table of data 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 thought of as 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 function mainly involves using SQL style queries and applying them to a given dataset.

Syntax for the QUERY 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 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 Sheets 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 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 on values. These functions generally return a single value, after performing the calculation. Some examples of aggregate 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 subset of a column)
  • MAX – This function finds the highest value in a given column (or 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 aggregate 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

To demonstrate how to use the QUERY Function, we are going to apply different queries on the following dataset sample:

dataset

A Simple QUERY Using the SELECT Function

Let us start with a simple query that simply 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 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:

query formula                                                                 

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)

 query function for address

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:

dataset query

You can then use the name of the range in the QUERY function as follows:

=QUERY(Dataset,"SELECT D",1)
query function example

Note that the QUERY formula only returns the data table, but without the formatting.

Google Sheets Query 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)
query formula for name and address

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)
select clause

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.

manufacturing query formula

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)
=QUERY(Dataset,"SELECT * WHERE E>30",1)

Notice that since 30 is a number, we did not enclose it in single quotes.

Query Using SELECT WHERE Contains

The combination of the SELECT and WHERE clauses is quite powerful and versatile. So you can combine them in a multitude of 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 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.

How to find Employees from NJ

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)
=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)
Query formula

Ordering your Selected Data using the 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)
Very specific query function

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)
hours worked query

Grouping Selected Data in a Google Sheets Query using the GROUP BY Clause

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 used in combination 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 are 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)
department query

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")
group by function

As can be seen from the above examples, the GROUP BY function provides a great way to summarize data into groups.

How to Create a Column Label with QUERY 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 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’.

total hours worked

Google Sheets Query 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’, then 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.

Google Sheets Query from Another Tab

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 sheet named MyData, and selects only columns A and B to be displayed in the new sheet.

query formula

Google Sheets Query 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:

 sheet1

Besides that, say you have the following data in Sheet2:

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 datasets, we used column references Col1 and Col2, depending on their order.

This is why the columns need to be consistent across all your individual datasets. This means Col1 must refer to the Name column in all the datasets and 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:

Google Sheets Query from Multiple Tabs

Google Sheets Query 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:

 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 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):

sheet URL

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 button to import the data.

allow access

Here’s the output you will see in the new worksheet after allowing access to the source dataset:

Google Sheets Query from Another Worksheet

Conclusion

In this tutorial, we showed you how to use the Google Sheets QUERY 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.