How to use the Google Sheets QUERY function

The Google Sheets QUERY function is a very powerful and versatile tool. So much so that you can call it a one-stop-shop for all your logical, lookup, summation, counting, averaging, filtering and sorting requirements. The function fetches specific information from a data set through a query statement, much like fetching result sets from a database using queries. The syntax of the query language used in this function is similar to SQL.

Syntax

• data – is the reference to the range of cells on which we want to query upon.
• query – is the text using which the QUERY function churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.
• headers – is an optional parameter that indicates the number of header rows at the top of the data. If left out, Google Sheets guesses the value based on the content within the data.

Clauses and operators

The Query language used in Google Sheets QUERY function is a text-based language similar to SQL. The query uses clauses to perform actions. Below is a list of the possible clauses that you can use to perform a wide variety of actions on your data.

If you use the QUERY function a lot, you’ll also find that you make use of a number of logical operators to set conditions within the function. Below is a list of logical operators that you can use.

Basic QUERY functions

First of all, to understand how the function is put to use, consider the following sample data. It consists of information corresponding to a list of students who have enrolled in various courses at a university. In all the upcoming examples, I’ll enter the function in the cell H1. It will also be displayed in the function bar in the snapshots.

Example #1: Select columns based on a condition

I will start off with a very basic demonstration, and add more complexity throughout the article in order to help you understand the function step-by-step. To start with, I’ll use the QUERY function to fetch the names of the students who are residing on campus.

Before moving on, I’ll go over some of the basics of QUERY in this first example. To start with, notice that the data selected includes the headers – these will be used as part of the output from the function. You can see that the function returned not only the names of the students who are residing on campus, but also the Name header, which is very helpful for keeping track of what the results mean!

After selecting the data, the main part of the query is in quotation marks as a text string command. I’ve used two of the clauses listed in the chart above: Select and Where. The ‘select’ command is used to return columns – telling the function to “Select A” leads to an output of the entire column A.

The ‘where’ clause allows you to add a condition. Here, I’ve added the condition that the F column should equal “Yes”. For the rows that the F column is “No”, the function ignores that data and does not include it in the output.

Combining the two clauses into one query, I’ve typed “Select A where F = ‘Yes’”. The is essentially telling the function to return all the names (from column A) of students who live on campus (data in column F is ‘Yes’).

Tip: If you’re selecting all the columns in the data set, you can type Select * to quickly achieve this instead of listing out each column!

Example #2: Select columns based on a condition

Now I’ll show the same example, but finding the students who are NOT on campus.

The only difference here is that I’ve changed the logical operator in the ‘where’ clause to <>, which means “not equal”. So I am asking the function to give me the list of names of students that do NOT have a ‘Yes’ in the F column.

Example #3: Select multiple columns

Now I’ll fetch the names, ages, departments of the students whose have taken more than 7 courses.

Again, I start with the Select clause, but this time I select multiple columns by listing them separated by commas. I’ve chosen columns A, B, and C to be selected. I’ve also changed the Where clause to filter out the data of students who have less than 7 courses.

Example #4: Multiple WHERE conditions

I will now take this a step further. I’ll bring up the names, departments, and join dates of the students aged 25 or below who have joined the university between 25-Dec-2016 and 20-Jan-2017. Please note, in the query text, the dates always have to go with yyyy-mm-dd format, enclosed within single quotes.

As you can see, you can add multiple Where conditions simply by separating them with “and”. This allows you to filter data in any way you can imagine!

Example #5: Referencing cells

What if you need to reference the date from a cell? No problem there! You can do so with the help of concatenating operators and a text function. By doing this in the example below, you will get the names and join dates of the students who joined after 1-Jan-2017.

An important thing to note when using dates in your query is that dates can only be in the yyyy-mm-dd format, and must be enclosed in single quotation marks and preceded by the string “date”. So to denote January 1 2020 in your query, you’d need to type: date ‘2020-01-01’.

Arithmetic and Aggregation Functions

In addition to the various clauses listed above, you can use regular arithmetic functions within your queries. Operations like addition, subtraction, multiplication, and division, as well as aggregation function sum, average, count, max, and min are all fair game.

Example #6: Multiplicaction and addition

I’ll start off by demonstrating a few simple arithmetic functions – multiplication and addition. In this example, I want the age to be in months instead of years (so I’ll multiply by 12) and I want to add one credit for each student.

As you can see, all you have to do is perform the operation on the column of your choosing. The output isn’t very pretty, but we’ll discuss how to change the headers and formatting later on in this guide.

Example #7: Average

Now I’ll show an aggregation operation – the average function. The average of a column is accessed with the abbreviation ‘avg’. You also need to tell the function which items to average together – i.e. how you want the data grouped. That’s why I’ve also told the function to group the data by column C (dept), using the Group by clause.

You’ve now seen the QUERY function use basic arithmetic functions like multiplication and addition, as well as aggregation operations such as average.

Complex QUERY functions

Now that you’ve seen some basic examples using the Select and Where clauses to filter a data set, as well as some arithmetic functions, I’ll show some more complex examples using a variety of clauses and operations.

Example #8: Select, Sum, and Group by

You can use a combination of Select, Sum, and Group By to list out all the departments and the display the number of courses taken from each department. The Group By clause is used alongside aggregation functions (such as Sum) to tell the function how the data should be grouped and summed – otherwise the aggregation function doesn’t work.

Example #9: Label and Sort

In the previous example, you’ll notice that the QUERY function returned the second column with the header “sum Courses”. Honestly, this is a bit awkward – luckily, you can fix that by renaming it. In addition to that, I will also use the second column (now renamed to ‘Courses Taken’) to sort in ascending order. Here’s how to do it.

Example #10: Count

Can you display the number of instances in which student is taking courses from each department? Of course! The QUERY function has you covered here as well – you can use the count operator to achieve this.

Instead of using the Sum operator to add up all the courses taken, I’m using the Count operator to count the number of occurrences of a student taking any number of credits within each department. I’m also again using the Select, Group by, and Label clauses to get the output I want.

Example #11: Limit and Order by

The Limit clause limits the results to a specified number. For example, in the screenshot below I limit the output to 10 rows. This clause is often used in conjunction with the Order by clause, which puts the data into ascending or descending order.

Here, I’ve used the Order by clause to sort the data from lowest age to highest age, then used the Limit clause to limit the output to 10 – giving me the 10 youngest students.

Example #12: Pivot

The Pivot clause essentially allows you to create your own pivot tables using the QUERY function. In the example below, I’ve taken a previous example and pivoted it by column A. The result is that the names of the students are now the headers, with the data I’ve selected in the rows below each name.

Using the Pivot clause is a somewhat more advanced use of queries, but if you play around with it yourself, you’ll discover the many uses of it!

Using ‘OR’ in queries

So far, you’ve seen a lot queries that use ‘and’ to add additional criteria to the query. But you can also use ‘or’, just like in other functions in Google Sheets such as IF statements.

Example #13: Using OR in a query

This example is a replica of an earlier example, but instead of specifying that the age needs to be 25 or younger AND the join date must be between the dates specified, I am specifying that any data point that meets EITHER of these criteria, will pass the filter and appear in the output.

Headers

So far, all the examples have been with a single row of headers in the data, and a ‘1’ in the header parameter within the QUERY function. Now, I’ll show you what to do if you have headers spanning multiple rows.

Example #14: Multiple rows of headers

The header parameter is an optional input that comes in handy when your headers span across multiple rows. In such cases, this parameter helps you easily combine the headers in one single row, as shown below.

All you have to do is put a ‘2’ as the header parameter after the query! However many rows your headers span across, just put that number in the header parameter – it’s as simple as that!

Query multiple tabs or sheets

What happens when your data is spread across multiple tabs on your spreadsheet, or even on a different sheet? You can still use the QUERY function across these multiple tabs and sheets!

Example #15: Query across tabs or sheets

In this example, you can see how to use the QUERY function across multiple tabs. The key is to have the data in the same format in all sheets – so in this example, the ages need to in the second column in both sheets. Then, the format changes a bit – instead of referring to columns by their letter (column A, B, etc.) you’ll need to refer to them by number (Col1, Col2, etc.).

Lastly, you’ll also need to enclose the data ranges in curly braces {} and separate them with a semicolon. Different tabs or sheets are identified with the sheet name followed by an exclamation mark, then the range of cells within the sheet. All this makes more sense when you look at the example below!

Without a doubt, this is one of the complex functions to master in Google Sheets. It could be well worth your time to get a good handle on this one, since it is without a doubt one of the most potent tools in Google Sheets. We encourage you to further explore the QUERY function here.

Make sure to check out this relevant blog post about How to create pivot tables in Google Sheets.