How to use the Google Sheets QUERY formula
The Google Sheets QUERY formula is quite a powerful and versatile function. So much so that, we can call it a one-stop-shop for all the logical, lookup, summation, counting, averaging, filtering and sorting requirements. The formula helps us fetch 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 formula is similar to SQL.
QUERY(data, query, [headers])
- data – is the reference to the range of cells on which we want to query upon.
- query – is the text using which the QUERY formula 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.
Usage: Google Sheets Query Formula
First of all, to understand how the formula is put to use, let us consider the following sample data. It consists of information corresponding to a list of students who have enrolled
Example # 1:
We will start off with a very fundamental demonstration. So, we use the QUERY formula to fetch the names of the students who are residing on campus.
Example # 2:
Having dealt with the basic example, let us now try fetching the names of the students who are NOT residing on campus.
Example # 3:
Now, we will fetch the names, ages, departments of the students whose have taken more than 7 courses.
Example # 4:
We will now attempt taking this a step further. We bring up the names, departments, join dates of the students aged 25 or below, and 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
Example # 5:
What if we need to reference the date from a cell? No problem there! We will get around with the help
Example # 6:
Is there a way to list out all the departments and the display number of courses taken from the respective department? Yes, there is! And, we might as well understand the power and versatility that the QUERY formula offers.
You will notice that the QUERY formula returned the second column with the header “sum Courses”. Honestly, it is a bit awkward to have that for a header. But, we can fix that and rename it. Not only that
Example # 7:
Can we display the number of instances of each of the departments? Of course, we can! The QUERY formula got us covered here as well.
Example # 8:
Consequently, we will now experiment with the third parameter. While this is an optional input, it might come handy when we come across headers that span across multiple rows. In such cases, this parameter helps us combine the headers in one single row, as shown below.
Without a doubt, this is one of the complex formulas to master in Google Sheets. Get a good hang on this one and it could serve you as one of the most potent tools. We encourage you to further explore the QUERY formula here.
Make sure to check out this relevant blog post about How to create pivot tables in Google Sheets?