Google Sheets QUERY Function (Dead Easy Guide W/ 7 Examples)

The Google Sheets QUERY 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 Google Sheets QUERY function, its syntax and howto use it. To help you know how to apply the 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.

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 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 (Google Sheets QUERY Examples)

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

dataset for a Google Sheets QUERY

A Simple QUERY Using the SELECT Function

Let us start with a simple query formula in Google Sheets 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 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:

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 Google Sheets only returns the data table, but without the formatting.

You can access this example sheet here.

Google Sheet 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)

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 a whole word.

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)

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

Using the LIMIT Clause

If you were using a set of data that had 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 a Google Sheets Query 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 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 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)
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.         

Pivot Clause

The pivot clause is usually a bit more tricky to wrap your head around. The GROUP BY clause can be replaced by or used together 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 group by clause combined with the pivot clause gives a more comprehensive table of the hours worked in each department and each worker.

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 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 data source, we used column IDs 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 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):

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

Literals in the Google Sheets Query 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 does not contain any 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 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 the true or false logic expressions. These are common with the IF and the IFERROR functions.

The boolean literals are used the same way as 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 database that is relational. 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 Function Google Sheets 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, it could just 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 just have to use the syntax:

=QUERY(data, query_string, [headers])
  • Data is the range to query
  • query_string contains the query you would like to run on your data
  • headers is the optional input of the amount 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 just 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 following syntax:

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

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. Check out our other article to learn how to connect Google forms to sheets.

Related:

Most Popular Posts

Zoe Biehl

Zoe Biehl

Zoë Biehl is the senior editor for SpreadSheet Point. She is also Head of Content for VentureKite. Her goal is to ensure all content published on SpreadSheet Point is clear, understandable, and informative for our loyal readers.
Related Posts