Using Query Function in Google Sheets – Examples

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

In this tutorial, I will explain the 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 examples, let me try and explain query function’s utility.

When to Use the Query Function in Google Sheets

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

For example, suppose you have a 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 examples.

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

QUERY Function – Syntax

Here is the syntax of Query function in Google Sheets:

QUERY(dataquery[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 Query function in Google Sheets is to see how to use expressions with a dataset.

For the purpose of this tutorial, I am using the dataset of highest-grossing Hollywood movies. I got the 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:

Query Function in Google Sheets - Dataset

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.

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

Note that every 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:

Google Sheets Query Function - Select Expression Explained

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.

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.

Data arranged in ascending order in Query Function in Google Sheets

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

Where clause 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 ‘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 in 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:

Group Data using group clause in Query Function

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 Query function in Google Sheets and get more advanced in it.

You can find more reading on Query Function on the below links:

You May Also Like the following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X