How to Use Google Sheets QUERY Function (11 Examples) – 2024

Google Sheets boasts a multitude of functions to analyze and organize complex datasets. To combine the functionalities of multiple functions, users can leverage the QUERY function in Google Sheets. It is a solid function with a simple syntax.

The QUERY function is an array formula that allows users to perform tens of different operations.

It is also popular as the perfect method to replicate the functionalities of a pivot table for grouping, comparing, and summarizing datasets.

A software expert with SQL knowledge may find this function easy to use. It allows them to run queries similar to SQL by combining arithmetic, lookup, and filter functions.

This is an in-depth guide about Google Sheets QUERY Function. We will discuss syntax and examples to demonstrate how this formula quickly replaces many other functions to let users run data commands in Google Sheets.

If you have ever seen a long-from QUERY formula in Google Sheets, I know it may have scared you. But don’t worry! In this article, I have explained all the bells and whistles of the QUERY function in Google Sheets for beginners as well as advanced users.

Download the Example Google Sheet

Here is the Google Sheet, which contains the spreadsheets with datasets used to demonstrate the QUERY function examples. Click on the following link to make a copy.

Download Here!

You will be able to master the QUERY function with practice quickly.

If you have your own Google Sheet ready with the required datasets, then skip downloading the above file.

Explained: The QUERY Function in Google Sheets

There are hundreds of functions in Google Sheets. You may have used a few of them to this date.

But now is the time to take your data analysis and reporting skills to the next level!

The QUERY function combines multiple functions to perform a specific task, such as filtering or sorting the datasets. It is similar to SQL (Structured Query Language), which lets users organize data stored in tabular form.

The function uses Google Visualization API Query Language to create queries and execute a specific task. You can search for a text string, filter, and sort datasets, extract data subsets, and much more.

Here’s the general syntax for the QUERY function,

=QUERY(data, query, [headers])

Each argument of the above function needs to be replaced with the proper information.

• “data” – It represents the table range that needs to be organized using the QUERY function.
• “query_string” – Here, you need to define the query that needs to be executed on the selected range of cells. It uses various clauses, arithmetic operators, and aggregate functions (discussed in the following section). 
• “headers” – This is an optional argument that lets users define the number of header rows for their data. You can enter “1” to tell the function to treat the first row of the table as a header.

The syntax of this function is pretty straightforward. You don’t need to have an expert knowledge of Google Sheets to understand it.

However, things may get overwhelming while defining the queries using a range of built-in functions and arithmetic operators. So, before we dive deep into the article, let us discuss them and understand their importance.

What are Clauses?

A clause is used to specify a particular action that needs to be performed by the QUERY function in Google Sheets.

Here is a list of Clauses which can be used in Google API Query Language:

• SELECT
• WHERE
• GROUP BY
• PIVOT
• ORDER BY
• LIMIT
• OFFSET
• LABEL
• FORMAT

The above clauses let users define the patterns in which the QUERY function should manipulate the given data. For example, users can display a specific number of columns using the SELECT clause.

Note that the clauses are not case sensitive; you can use them as “SELECT” or “select” while defining the query.

We will be discussing each clause in detail using examples in the following section of this article.

What are Arithmetic Operators?

Arithmetic operators are used to perform mathematical operations. It can also include comparison operators.

Here is a short list of Arithmetic operators for your reference:

• “–” – Subtraction
• “+” – Addition
• “/” – Division
• “*” – Multiplication
• “>” – Greater Than
• “<” – Less Than
• “=” – Equal To

For a more detailed list, you can check this link from Google Developers.

In contrast to the Aggregate and Scalar functions, these mathematical operators are compatible with all types of Clauses.

What are Aggregate Functions?

It includes the most common functions, such as the SUM, COUNT, AVG, etc., that you may have used in the past. They are used to perform simple calculations and return a single value.

Here are the Aggregate Functions which can be used to define a query:

• SUM – As from the name itself, you can use this function to add the cell values from the given table.
• COUNT – This function is used to count the cells that contain the numerical values.
• AVG – It finds the average of the cell values from the given table.
• MIN – You can search for the lowest value from a specific column of the given table.
• MAX – It is similar to the previous function and lets you find the highest value from a specific column of the table.

Note that the Aggregate functions can be used only with the SELECT, ORDER BY, LABEL, and FORMAT clauses.

What are Scalar Functions?

Scalar functions are used to convert the given value into another parameter using formulas such as the YEAR, MONTH, and HOUR.

Similar to the Aggregate functions, you cannot use the Scalar functions with all of the available clauses. It is compatible with the SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LABEL, and FORMAT clauses.

Here is a short list of Scalar functions that can be used for the QUERY function:

• YEAR – This function returns the year from the given date.
• MONTH – It is used to display the month from the given date.
• DAY – It is used to extract the day from the given date.
• DAYOFWEEK – You can extract the day of the week from the given date or datetime value.
• NOW – This popular function is used to display the current date and time using the timestamp.
• LOWER – It is used to convert the given text string into lowercase letters.
• UPPER – It converts the given text string into uppercase letters.

These are only a few of the Scalar functions. For a more comprehensive list, visit this official link from Google Developers.

Google Sheets QUERY Function Examples

Now that we know all the things associated with the QUERY function in Google Sheets let’s discuss a few real-life examples.

I hope you have downloaded the example Google Sheet as discussed at the beginning of this article and are ready to follow up.

We will discuss these examples by referring to the Clauses discussed in the previous sections.

SELECT Clause – Google Sheets QUERY Function

As from the name itself, this clause lets the user select a subset of data from the given table. You can use it for scenarios such as displaying a specific number of columns from the given table.

EXAMPLE #1 – Display the entire table using the QUERY function

Consider the following table where we have the employee name, department, age, hire date, and city.

Let’s use the QUERY function and create a new dynamic table, including all the columns. We need an asterisk (*) symbol, which tells the formula to include all the columns.

• Select the cell “G1“
• Type “=query”
• Choose the first option from the popup or press the “Tab” key
• For the data argument, provide the cell reference as “A1:E21
• Press “,” to move to the following argument
• Now, let us use the SELECT clause and Asterisk (*) symbol as the Arithmetic Operator to define a query that selects all the columns of the table
• Replace the query argument with the “select *“
  (Make sure to use the double quotation marks)
• Press “,“
• Type “1” for the headers argument
• Complete the parentheses using “)“
• Press “Enter” on your keyboard

Our final formula should look like the following,

=QUERY(A1:E21,"select *",1)

It is a simple example where we have displayed a dynamic table using the QUERY function.

EXAMPLE #2 – Display specific columns using the QUERY function

Let us consider the table we used in the previous example.

Now, instead of displaying all the columns or the entire table, let us just display the employee name and their department and age.

Here are the steps,

• Select the cell “G1“
• Type “=query” and choose the first option from the popup
• Provide the cell reference as “A1:E21” for the data argument of the function
• Press “,” to move to the following argument
• Replace the query argument with the “select A, B, C“
  (Make sure to use the double quotation marks)
• Press “,“
• Type “1” to tell the function that the first row of the table should be treated as the header
• Complete the bracket using “)“
• Press “Enter” key

Our final formula after following the above steps,

=QUERY(A1:E21,"select A, B, C",1)

It is a method for creating a dynamic table with a limited number of columns from a huge dataset.

WHERE Clause – Google Sheets QUERY Function

This clause is used to create criteria and conditions. For example, you can tell the function to display the employees whose salary is greater than $10,000.

You can define the criteria using the WHERE clause and ask the function to display a specific number of columns from the table using the SELECT clause.

Note that this clause needs to be combined with the Arithmetic operators to create the criteria or conditions.

EXAMPLE #3 – Display the Rows that Matches the Given Criteria (Numbers)

Consider the following example where we have employee names in the first columns and their department, hire date, and annual salary in the remaining columns.

Our task is to find the employees whose annual salary is above $100,000. The final table should contain the employee’s name in one column and their salary in the next column.

In this case, we need to use two clauses as below,

• The SELECT clause tells the function to return the employee name (column A) and their salary (column D)
• The WHERE clause tells the function to find the rows where the value from the column D is equal to or greater than 100000)

Now, let’s begin.

• Select the cell “F1“
• Type “=query”
• Choose the first option from the popup or press the “Tab” key
• For the data argument, put the cell range as “A1:D21“
• Press “,” to move to the next argument
• Replace the query argument with the “select A, D where D>=100000“
  (Make sure to use the double quotation marks; otherwise, the formula will return an error at the end)
• Press “,“
• Type “1″ to tell the function that the first row of the table is the header
• Complete the bracket using the “)“
• Press the “Enter” key

Our final formula should be as follows,

=QUERY(A1:D21,"select A, D where D>=100000",1)

You must avoid separating the two clauses using the comma (,) or any other symbol. Doing so will return a long error, as shown below.

EXAMPLE #4 – Display the Rows that Matches the Given Criteria (Dates)

In the previous example, we created a condition or criteria using the numbers.

Now, let us create a condition using the dates. You need to make a few special arrangements while putting the dates in the QUERY function.

• Start by entering the keyword “date“
• Then, enter the actual date by following the format as “yyyy-mm-dd“

It should look as follows,

date ‘2023-1-1’

Note that using the single quotation mark is compulsory while entering the date in the QUERY formula.

Consider the following example where we have employee names in the first column and their department, hire date, and city in the following columns.

Let us find the employees who were hired before 2010.

• Click on the cell “F1“
• Type “=query” and select the first option from the popup
• Provide the cell reference “A1:D21” for the data argument
• Press “,” to move to the following argument
• Now, let us type “select A, C where C<date ‘2010-1-1’“
  (Note that we are using two clauses here: SELECT and WHERE. Also, make sure to use the double quotation marks)
• Press “,“
• Type “1” to tell the function that the first row of the table is the header
• Complete the bracket using the “)“
• Press the “Enter” key

Here’s how our final formula should look,

=QUERY(A1:D21,"select A, C where C<date '2010-1-1'",1)

As discussed earlier, to enter the date within the QUERY formula, you must use the keyword “date” and follow the format “yyyy-mm-dd”. Without these two things, the function won’t work properly.

You can also combine various conditions defined using the WHERE clause with the help of logical operators such as AND and OR.

GROUP BY Clause – Google Sheets QUERY Function

The GROUP BY clause uses Aggregate functions to concatenate rows.

Simply put, it allows you to summarize the dataset into groups. For example, you can count the sales reps handling a particular region for your company.

EXAMPLE #5 – Summarize the dataset into groups by referring to a specific column

Consider the following data where we have employee names in the first column and their department, gender, and city in the following columns.

Let’s find the count of male and female employees using the QUERY function.

• Select the cell “F1“
• Type “=query”
• Select the first option from the popup or press the “Tab” key
• Provide the cell range “A1:D21” in the place of the data argument
• Press “,” to move to the following argument
• Now, for the query argument, we use the SELECT and GROUP BY clauses.
• Put “select C, count(A) group by C“
  (Here, we are telling the function to display column C, which contains the gender information. Further, count the cells from column A, which holds the employee names, and group them by referring to the entries from column C)
• Press “,“
• Type “1” in the place of the headers argument
• Complete the parentheses using the “)“
• Press “Enter” on your keyboard

Our formula after following the above steps:

=QUERY(A1:D21,"select C, count(A) group by C",1)

You may be using the Pivot tables to summarize or group the datasets by referring to the specific column.

Please make sure that the columns selected using the SELECT and GROUP BY clause are similar.

Also, you must use one of the Aggregate functions to create a query using the GROUP BY clause.

PIVOT Clause – Google Sheets QUERY Function

The PIVOT clause converts rows into columns and vice versa. It offers functionality similar to the GROUP BY function discussed in the previous example.

EXAMPLE #6 – Summarize the values from the given column and transpose it

Consider the following table, where we have employee data such as their names, department, gender, and city.

Our task is to find and display the number of female and male employees in separate columns using the QUERY function.

Let’s start,

• Select the cell “F1“
• Type “=query” and choose the first option option from the popup
• Now, replace the data argument with the cell range “A1:D21“
• Press “,” to move to the following argument
• Now, we will use the SELECT clause and COUNT function along with the PIVOT clause to display the number of male and female employees in separate columns.
• Replace the query argument with the “select count(A) pivot C“
  (Make sure to use the double quotation marks)
• Press “,“
• Type “1” in the place of headers arguments so that the function will understand that the first row of the table needs to be treated as the header
• Complete the bracket using “)“
• Press the “Enter” key

Here’s how our formula should look after following the above steps:

=QUERY(A1:D21,"select count(A) pivot C",1)

You can combine the GROUP BY and PIVOT clauses to create a powerful formula. But make sure to use the Aggregate functions, without which both these clauses will return an error.

ORDER BY Clause – Google Sheets QUERY Function

This clause is for the users who wish to sort the datasets by the values from the given column. They can sort the column in both ascending and descending order.

EXAMPLE #7 – Sort the table by the values from a specific column

Let’s consider the following table, which includes employee data with their names, departments, ages, and annual salaries.

Now, our task is to sort the table by column D, which holds the annual salary.

Here are the steps:

• Click on the cell “F1“
• Type “=query”
• Select the first option from the popup or press the “Tab” key
• Replace the data argument with the cell range “A1:D21“
• Press “,” to move to the next argument
• Now, let us use the SELECT clause to display the columns and the ORDER BY clause to sort the table by the annual salary.
• Replace the query argument with the “select A, B, C, D order by D“
  (Make sure to use the double quotation marks. Also, to display all the columns of the table, we have entered A, B, C, and D after the SELECT clause)
• Press “,“
• Next, type “1” so that the function will treat the first row of the table as the header
• Press the “Enter” key

Our final formula to sort the table by the annual salary will be,

=QUERY(A1:D21,"select A, B, C, D order by D",1)

The function will instantly sort the table, as shown in the following image. It is one of the quickest methods to sort a table.

The main benefit of using the QUERY function to sort the table is that it allows you to create a dynamic table that updates automatically whenever the main table is updated.

In the case of a huge table with tens and hundreds of columns, you need to use:

=QUERY(A1:D21,"select * order by D",1)

The above formula uses the Asterisk (*) symbol to tell the function to display all the columns after sorting the table by a specific column.

LIMIT Clause – Google Sheets QUERY Function

The LIMIT clause is helpful when you wish to display a limited number of rows.

In other words, suppose you wish to obtain only ten employee names whose annual salary is greater than $100,000.

Let’s understand it through an example.

EXAMPLE #8 – Display a limited number of rows that satisfies the given condition

Consider the following table, where we have employee data with their annual salary.

We need to find five employees whose annual salary is greater than $100,000.

• Select the cell “E1“
• Type “=query” and choose the first option from the popup
• Replace the data argument with the cell range “A1:C21“
• Press “,” to move to the next argument
• Now, for the query argument, let us type “select A, B, C where C>100000 limit 5“
  (Here, we are telling the function to display the columns A, B, and C using the SELECT clause. Also, return only those rows where the respective cells from column C are greater than 100000. Finally, regardless of the results, we are telling the function to display only five employee names using the LIMIT clause)
• Press “,“
• Type “1” so that the QUERY function will understand that the first row of the selected table is the header
• Complete the parentheses using “)“
• Press “Enter” key

Here’s how the final formula looks:

=QUERY(A1:C21,"select A, B, C where C>100000 limit 5")

It’s a simple example where we have created a single condition using the WHERE clause and comparison operator. In your case, you can define multiple criteria using the WHERE, GROUP BY, and ORDER BY clauses.

OFFSET Clause – Google Sheets QUERY Function

This clause is used to skip the first rows depending on the user input.

For example, if the QUERY function is supposed to return ten results (rows), then using the OFFSET clause, we can skip the first 2, 3, or 5 results as per our preference.

EXAMPLE #9 – Skip the first three entries from the total results obtained by the QUERY function

The following table contains the employee data with their names, department, and age.

Let’s find the employees whose age is greater than 30 using the SELECT and WHERE clause. We will also limit the entries by omitting the first ten results.

• Select the cell “E1“
• Type “=query”
• Choose the first option from the popup or press the “Tab” key
• Now, for the data argument, let us provide the cell range as “A1:C21“
• Press “,” to move the query argument of the function
• Type “select A, B, C where C>=30 offset 10“
  (Note that the SELECT clause will tell the function to include columns A, B, and C in the final table. Meanwhile, the WHERE clause lets the function choose only those rows where the cell values from column C [employee age] are greater than 30. , the OFFSET clause is set to 10 and omits the first ten results obtained using the QUERY function)
• Press “,” to move to the headers argument
• Type “1” to tell the function that the first row should be treated as the header
• Complete the bracket using the “)“
• Press “Enter” on your keyboard

Our final formula should look like the following,

=QUERY(A1:C21,"select A, B, C where C>=30 offset 10",1)

Without the OFFSET clause, the function should have returned 19 results as there are a total of 19 employees whose age is greater than 30. But, due to the OFFSET clause, the function has returned only nine employees.

LABEL Clause – Google Sheets QUERY Function

The LABEL clause lets users rename column headers. It is a simple clause where you need to define the column letter and desired name in the following format.

LABEL A ‘Desired Name’

EXAMPLE #10 – Change the column headers using the QUERY function

Here is a table that contains the employee’s full name and their department.

As shown in the above image, the first column is named Full Name, and the second column is Department.

Our task is to change the column headers as follows:

• “Full Name” to “Employee Name“
• “Department” to “Section“

For this purpose, let us use the SELECT and LABEL clauses in the QUERY function.

• Click on the cell “D1“
• Type “=query”
• Select the first option from the popup or press the “Tab” key
• Replace the data argument with the cell range “A1:B21“
• Press “,” to move to the next argument
• Type “select A, B label A ‘Employee Name’, B ‘Section’“
  (Make sure to use the double quotation marks to avoid the error)
• Press “,“
• Type “1” to tell the function to treat the first row of the table as the header
• Complete the parentheses using the “)“
• Press “Enter” on your keyboard

Here’s how the final formula should look,

=QUERY(A1:B21, "select A, B label A 'Employee Name', B 'Section'",1)

FORMAT Clause – Google Sheets QUERY Function

This is the 9th and final clause compatible with the QUERY function. It allows you to change the formatting of the cell values from a specific column.

Similar to the LABEL clause, you need to use the following format.

FORMAT A ‘Desired Cell Format’

You can change the cell formatting to date, currency, percent, and much more.

EXAMPLE #11 – Change the column formatting to date using the QUERY function

Consider the following table where we have employee names in the first column and their department, hiring date, and annual salary in the following columns.

Let us use the FORMAT clause to change the hiring date format from “mm-dd-yyyy” to “yyyy”. In short, we will display only the year in the Hire Date column.

• Click on the cell “F1“
• Type “=query” and choose the first option from the popup
• For the data argument, let us provide the cell range as “A1:D21“
• Press “,” to move to the query argument of the function
• Type “select A, B, C, D format C ‘yyyy'”
  (Make sure to use the double quotation marks to avoid the error)
• Press “,“
• Type “1” in the place of the headers argument
• Complete the bracket using the “)“
• Press “Enter” on your keyboard

Here’s how the final formula looks,

=QUERY(A1:D21,"select A, B, C, D format C 'yyyy'",1)

Conclusion

The QUERY is a powerful function by Google Sheets. It replicates the functionalities of a Pivot table and lets users modify or organize massive datasets.

I hope you learned everything when it comes to syntax, compatible functions and operators, and clauses of the QUERY function.

You can combine multiple clauses to create a robust query. However, make sure to follow the sequence as SELECT, WHERE, GROUP BY, PIVOT, etc., while including multiple clauses in the QUERY formula.

For more such helpful tips and tricks to use Google Sheets, please refer to our blog.