Google Sheets QUERY Function: A Beginner’s Guide

The  Google Sheets QUERY function is one of the most useful functions. When used to its full potential, it can do some really amazing things in Google Sheets.

In this tutorial, I will explain the Google Sheets QUERY function and show you a lot of practical examples of how you can use it in Google Sheets.

Before I jump into the syntax of the function or the QUERY Google Sheets examples, let me try and explain the QUERY function’s utility.

What is the Google Sheets QUERY Function?

The QUERY function is a versatile but slightly complex function in Google Sheets and other spreadsheet programs. So what is QUERY in Google Sheets? It lets you return an output table based on the input data source.

It can be used to extract part of the data from your data source. The QUERY function is capable of doing things that many other functions, like the filter function in Google Sheets also do.

When to Use the Google Sheets QUERY Function

QUERY function allows you to retrieve specific data from a tabular dataset.

For example, suppose you have huge data – let’s say retail sales data of many stores. You can use QUERY function in Google Sheets to quickly get the following data:

• All the sales data of Store A.
• All the sales data of Item A from Store B
• All the sales in the month of January

You can also use the QUERY function to extract a subset of the data from the main dataset. For example, if your data has 10 columns, you can use the QUERY function in Google Sheets to extract only 3 or 4 columns that you want.

We will see how to get all these done in the Google Sheets QUERY examples.

Let’s first start with the syntax of the QUERY function in Google Sheets.

QUERY Function – Syntax

Here is the syntax of QUERY function in Google Sheets:

QUERY(data, QUERY, [headers])

• data – this is the data range in which you want to perform a QUERY. Taking the example of retail sales data, this will be the range of cells that have the entire sales data.
• QUERY – this is the QUERY expression that you will use to fetch the required data. This QUERY expression is written in the Google Visualization API QUERY Language. This is also very similar to the QUERY expression used in SQL. This is always written within double quotes. Don’t worry about it though. I will show you how to create these expressions later in this tutorial.
• [headers] – this is an optional argument. Here you specify how many header rows are there in your dataset. If omitted, Google Sheets would guess it based on the content of the dataset.

Understanding QUERY Function Expressions – With Examples

The best way to understand Google Sheets QUERY function is to see how to use expressions with a dataset.

Let’s go through some Google spreadsheet QUERY examples. For the purpose of this tutorial, I am using the dataset of highest-grossing Hollywood movies. I got the data for this from Wikipedia (of course!). Note that the gross collections are not adjusted for inflation (so don’t be appalled when you see ‘Spiderman 3’ and ‘Fate of the Furious’ in the list).

Click here to access the Data Sheet (you will have to make a copy to use it).

Here is a snapshot of how the data looks:

The dataset has data for 50 movies in 4 columns – Rank, Title, Gross Collection, and the Year of Release.

The first thing I would do with this dataset is to create a named range for it. Creating a named range makes it easy for me to refer to this data from other sheets as well. Note that in case you don’t create a named range, you will have to use the cell reference along with the sheet name.

For example, instead of using =Data!A1:D51 I can use =MovieData (as we will see in the examples).

Now let’s dive in and learn about the cool things you can do with the QUERY function Google Sheets.

Example 1 – Extract Some Columns from the Dataset (Select Clause)

If you want to extract a part of the dataset from the whole dataset, you can use the SELECT expression.

Here is the formula that will extract the Rank (in Column A) and the Movie name (in Column B)

=QUERY(MovieData,"Select A, B")

The column IDs used are usually the letter of the column. Note that every Google spreadsheets QUERY function needs to have the Select expression in it. ‘SELECT’ tells the QUERY function what columns in the data to consider when extracting the data.

The Select expression and any other expression within the QUERY argument are always within double quotes.

In this case, we specified column A and B to be extracted completely.

Here is the output of the formula:

Note that I have extracted the result in a different sheet.

Also, the formula would only return the values, not the formatting.

Similarly, if you want to get the movie names and the year of release, you can use the following formula:

=QUERY(MovieData,"Select B, D")

Click here to access the Data Sheet (you will have to make a copy to use it).

Example 2 – Extract Data in Ascending / Descending Order (Order Clause)

When extracting the data, you can specify the order in which you want the data set to be sorted using the Google Sheets QUERY order clause.

For example, in this movie data example, let’s see how to QUERY the data and fetch the columns for ‘Title’ and the ‘Year of Release’ and then sort it in descending order (so that movies released latest are shown first).

Here is the formula to fetch the data in descending order:

=QUERY(MovieData,”Select B, D Order by D Desc”)

In the above formula, apart from the Select clause, there is a new clause – Order.

The Order clause tells the QUERY to sort the data in ascending/descending order, based on the specified column (which is column D in this case).

If I wanted to sort the data in ascending order, the formula would be:

=QUERY(MovieData,"Select B, D Order by D Asc")

This will make ‘Jurrasic Park’ to show at the top that was released in 1993.

Click here to access the Data Sheet (you will have to make a copy to use it).

Example 3 – Extract and Filter Data using Conditions (Where Clause)

So far, we have extracted the whole columns using the QUERY function.

Now let’s see how to extract data based on a condition.

For example, suppose I only want to extract the data for only those movies that got released after the year 2000.

Here is the formula I can use to do this:

=QUERY(MovieData,"Select B, D where D>2000")

This QUERY formula Google Sheets would extract the Movie name and the year and only show the movies that have a release date after 2000.

This is the ‘Where’ clause, which allows you to filter the data.

If you want to get the data of movie released after 2010 or before 1995, you can use the below formula:

=QUERY(MovieData,"Select B, D where D>2010 or D<1995")

If you want to arrange the data in ascending order based on the year, the formula becomes:

=QUERY(MovieData,"Select B, D where D>2010 or D<1995 order by D Asc")

Note that we have used all the three clauses covered so far – Select, Order, and Where.

Here is the output of the last formula we used:

Where clauses can be useful in cases when you have a category that you want to filter. For example, if I also have the movie genre in this list and I want to filter the data based on genre, I can specify it using the ‘Where’ clause.

Similarly, I can filter the movie names based on the gross collection. For example, get all the movies that did a collection of over a billion dollars.

Another great way to use the ‘Where’ clause is using it with drop down lists.

For example, if I have genres, I can have these genres in a drop-down list in a cell, and use the cell reference with the where clause.

Click here to access the Data Sheet (you will have to make a copy to use it).

Example 4 – Group Data Using QUERY Function (Group Clause)

You can also group data using the Group Clause in the QUERY function.

For example, suppose I want to get the total number of movies released each year, I can use the Group clause as shown below:

=QUERY(MovieData,"Select D, Count(C) group by D")

This will give the output as shown below:

If you’ve worked with Excel Pivot Tables before, you can see that the output has a similar logic and format.

You can use the ‘Order’ clause (as shown below) to sort the data and get the latest year at the top.

=QUERY(MovieData,"Select D, Count(C) group by D Order by D Desc")

I hope the examples covered above will help you get started with the QUERY Function in Google Sheets.

There is a lot more that can be done with this function. I wrote this tutorial to give you a good starting point. You can now explore the Google Sheet QUERY function and get more advanced in it.

Google Sheets QUERY from Multiple Tabs

You can also use the QUERY function across multiple tabs. It works almost like the IMPORTRANGE function. All you need is the name of the sheet tab you want to QUERY from. Let’s say we have the following tabs, and you want to combine them into one master sheet.

Our first sheet tab has data with the number of shoes sold.

Our second sheet has data with the price for each pair of shoes:

1. The first step is to create a new tab using the plus sign at the bottom left.
2. You can rename the new tab as Master sheet.
3. Since we have two tabs then we need to use an array by adding curly brackets into the formula. Therefore we would use this formula:

=QUERY({Sheet1!A1:D11;Sheet2!A1:D11},)

4. This will return the data from the two sheets into the master sheet in the order that is in the formula.

If you want to go a step further and only return data for the Nike brand, then add a criterion to the formula. We will use a select and where clause to instruct Google Sheets to select rows with Nike.

Since we are using an array as the data range, we can’t refer to the columns by their column letters. Instead, we use numbers eg. Col1.

If you use column letters, you will get an #Value! error.

Therefore our new formula would be:

 =QUERY({Sheet1!$A$1:$D$11;Sheet2!$A$1:$D$11},"SELECT * WHERE Col2 = 'Nike'",1)

This formula returns data from the two sheets for the Nike brand only as shown below:

How to QUERY Multiple Sheets / Ranges at the Same Time

What if we wanted to get data from a completely different sheet? This is still possible with the QUERY function if you combine it with the IMPORTRANGE function. A tip would be to run the IMPORTRANGE formula first so that you can give the sheets access. Then you can do the QUERY formula with the IMPORTRANGE formula easily.

Let’s say we have the exact same data as before, but instead of being in different tabs, they are in completely different worksheets. We want to return the data from the Nike brand from the two sheets to our Master sheet.

The formula for this will follow this format:

=QUERY({IMPORTRANGE("url_of_sheet1","data_range");IMPORTRANGE("url_of_sheet2","data_range")}, "sql_QUERY_commands")

1. The first thing we’ll do is input our import range formula. This is the IMPORTRANGE formula for our sheets: =IMPORTRANGE(“1gyUpGQxceGaN3AsRlTRlNbqtGfMf7ekEfzmrBLssz-E”,”Sheet1!A1:D11″)
2. Once you have allowed access we can now input our QUERY Google Sheet function.
3. You can cut the IMPORTRANGE that you have used earlier and paste it into the QUERY formula.
4. Now we need to add the import range for our second sheet. Put a semicolon after the first IMPORTRANGE and add this formula: IMPORTRANGE(“1i8UgLJfeHdnFB9-oPq6B4IgqgnuTqs44hHhSDrfZla8″,”Sheet1!A1:D11”)

Once you have put your formula it will show an error for a while. Just give it some time to load, and it will return the data from the sheets.

Advanced Google Sheets QUERY Function Techniques

Part of the QUERY function’s sophistication and versatility is that it is able to do complex tasks. You can add a total row to your output table or use dates as filters among other things. There are a number of additional advanced you can use with QUERY, such as:

Offset–  Used to skip a given number of first rows.

Label-         Used to set column labels.

Format– Formats the values in certain columns using given formatting rules.

Options- Lets you set additional options.

Building Pivot Tables with QUERY

The QUERY sheets function also can summarize data in almost the same way that a pivot table does. Therefore you can use a QUERY Google Sheets function to create a pivot table.

In order to summarize the data, we would use the Google Sheets QUERY GROUP BY clause and a pivot argument.

Normally the GROUP BY clause would just summarize and return an ordinary table with the values aligned the same as the data set. However, when you add the pivot clause, it moves the row to the column and vice versa, ‘Pivoting’ the table.

To make a pivot table with QUERY we would use the following formula:

=QUERY(Dataset, "Select D, SUM(C) GROUP BY D PIVOT B")

In our example sheets below, if we group by the number of shoes sold the QUERY returns the following table:

If we wanted a pivot table for the number of shoes sold we would use this formula: =QUERY(C1:F11,”select C,sum(D) group by C Pivot E”).

By pivoting column E we will get a different table with column E as the row header grouping the shoes by name and price.

You can even use Pivot with multiple columns instead of one. The pivot table may appear plain so you can format it however you desire.

The Difference Between SQL and QUERY

SQL and QUERY Google spreadsheet function may look pretty similar on the outside, but they are actually two different things. SQL stands for Standard QUERY Language and it is the language that lets you manipulate database-type commands.

On the other hand, a QUERY in Google Sheets,  is a function used to retrieve data based on specified criteria.

SQL Google Sheets also has the ability to retrieve data by communicating with databases. However, Google Sheets does not store data in a database therefore we use the QUERY function instead to write instructions like an SQL QUERY.

What to Consider Before Running a QUERY Function

The QUERY function is a sophisticated function that takes time to get used to. Here are some considerations to keep in mind when using it:

• If you are querying multiple sheets, then you need to make sure that the data from the sheets correspond with each other.
• QUERY is not case sensitive.
• When using the keywords, make sure that they are in order. The correct order is SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, then LABEL.

Conclusion

In this article, we’ve shown you how to use QUERY in Google Sheets. We have also shown you some advanced uses of queries in Google Sheets, like making a pivot table and more. Hopefully, you now understand how the Google Sheets QUERY function works.

Related:

• Using IMPORTFEED function in Google Sheets
• Using IMPORTDATA Function in Google Sheets
• Using VLOOKUP in Google Sheets
• How to VLOOKUP From Another Sheet
• Google Sheets Weekday Function – Explained with Examples
• How to Create and Use Filter Views in Google Sheets
• How to Insert a Pivot Table in Google Sheets
• How to Use SWITCH function in Google Sheets