Don’t forget to share this post

QUERY is perhaps the most versatile and powerful built-in function in Google Sheets. Essentially, it allows you to perform database searches on your Google Sheets data. Queries are written in the Google Visualization API Query language, which is similar to SQL. QUERY is a highly flexible function that combines the power of HLOOKUP, VLOOKUP, XLOOKUP, FILTER, SUM, AVERAGE, and many more. You can use it to find, filter, and format data however you want.

In this guide, you will learn about the QUERY function in Google Sheets. First, you will review the function’s syntax and six of the most frequently used clauses: select, where, group by, order by, limit, and label. Second, you will learn how to use QUERY to perform a simple select query. Finally, you will learn to use the six most common clauses to query your dataset, with step-by-step instructions for each.

What is the QUERY Function in Google Sheets?

The QUERY function allows you to search your Google Sheets data as if it were a database. You can extract subsets of your dataset by filtering, manipulating, and formatting the data in any way you want. Queries are written in an SQL-type language called Google Visualization API Query language.

QUERY Function Syntax

The Google Sheets QUERY function has three parameters, but only the first two are required. The syntax for the QUERY function is as follows:

=QUERY(data, query, headers*)
  • data: range of cells to be queried. This is where you can insert IMPORTRANGE to query data contained in a different spreadsheet.
  • query: written in the Google Visualization API Query language. Enclosed in quotations or a cell reference.
  • headers*: optional parameter (when header consists of or is split over more than one row).

Common Query Clauses

The language used for queries is similar to SQL and just as flexible. You can create anything from extremely simple to extremely complex queries. Below are some of the most common clauses or keywords, which you will learn to use in the following sections.

  • SELECT: selects which columns to return and in what order. If left blank, the function will return all columns in their default order.
  • WHERE: when used, the function will only return rows that match the condition.
  • GROUP BY: aggregates the row values and returns the result. Aggregate functions must be used with the ‘group by’ clause and include COUNT, SUM, MIN, MAX, and AVG.
  • ORDER BY: sorts the rows by the values in the column.
  • LIMIT: limits the number of results returned to the number you specify.
  • LABEL: renames column headers.

There are four additional clauses or keywords that are not covered in this article: PIVOT, OFFSET, FORMAT, and OPTIONS.

Keywords are not case-sensitive, but column letters must be in uppercase. If you use more than one of the keywords in your query, remember to respect the order given below:

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

In the next section, you have instructions on how to use QUERY in Google Sheets.

How to Use QUERY in Google Sheets?

To get you started using the QUERY function, let’s start with a simple query that selects all the columns in the dataset. For this example, I will write the function in the spreadsheet containing the data. However, you can easily query data in any other Google Sheet. To learn more, read this article on QUERY with IMPORTRANGE in Google Sheets.

  1. 1. Name the range containing your data to refer to it more easily.
Google Sheets QUERY Function Complete Guide Name Range
Google Sheets QUERY Function (Complete Guide) - Name Range
  1. 2. Type the QUERY function in the cell where you want the results.
Google Sheets QUERY Function Complete Guide QUERY Function
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the named range or the cells with the data as the ‘range’ parameter. In this case, I will simply type the name of the range.
Google Sheets QUERY Function Complete Guide Select Range
Google Sheets QUERY Function (Complete Guide) - Select Range
  1. 4. Type the query you want to use. I will select all columns in this case by using “SELECT *”. Since I am typing it directly into the formula, I must wrap the query in quotation marks.
Google Sheets QUERY Function Complete Guide Query Clause
Google Sheets QUERY Function (Complete Guide) - Query Clause
  1. 5. Since the headers occupy only one row, I’ll skip the last parameter. Close the parenthesis and press ‘Enter’.
Google Sheets QUERY Function Complete Guide Query Results
Google Sheets QUERY Function (Complete Guide) - Query Results

Google Sheets QUERY Examples

For the sake of clarity, the examples below are all based on the same dataset. Since some of the clauses are quite long, I will type them into cells so that I can use the cell references within the function. This makes things easier because you don’t need to worry about quotation marks, and it can help you avoid mistakes.

Use SELECT for Specific Columns

In this example, I want QUERY to return specific columns rather than all of them. After the SELECT keyword, type the column letters you want in uppercase, separated by commas.

  1. 1. In an empty cell, type in the query to select the columns containing the first name, last name, and email address.
Google Sheets QUERY Function Complete Guide SELECT Clause
Google Sheets QUERY Function (Complete Guide) - SELECT Clause
  1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.
Google Sheets QUERY Function Complete Guide QUERY Function 2
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the cell with the query as the second parameter.
Google Sheets QUERY Function Complete Guide 2nd Parameter
Google Sheets QUERY Function (Complete Guide) - 2nd Parameter
  1. 4. Skip the third parameter and close the parenthesis.
Google Sheets QUERY Function Complete Guide Close Parenthesis
Google Sheets QUERY Function (Complete Guide) - Close Parenthesis
  1. 5. Press ‘Enter’ to see the results.
Google Sheets QUERY Function Complete Guide Query Results 2
Google Sheets QUERY Function (Complete Guide) - Query Results

Use WHERE Clause to Filter by Value

For this example, I will select the customer’s last name, the amount, and the sales agent. However, I only want records for Agent 1, so I’ll need to use SELECT and WHERE.

  1. 1. In an empty cell, type in the SELECT and WHERE clause to specify the condition that must be met. In this case, I want to filter my results to Agent 1’s transactions.
Google Sheets QUERY Function Complete Guide SELECT WHERE Clause
Google Sheets QUERY Function (Complete Guide) - SELECT & WHERE Clause
  1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.
Google Sheets QUERY Function Complete Guide QUERY Function 3
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the cell with the query as the second parameter.
Google Sheets QUERY Function Complete Guide Add Query Clause
Google Sheets QUERY Function (Complete Guide) - Add Query Clause
  1. 4. Skip the third parameter and close the parenthesis.
Google Sheets QUERY Function Complete Guide Close Parenthesis 2
Google Sheets QUERY Function (Complete Guide) - Close Parenthesis
  1. 5. Press ‘Enter’ to see the results.
Google Sheets QUERY Function Complete Guide Query Results 3
Google Sheets QUERY Function (Complete Guide) - Query Results

Use GROUP BY Clause to Aggregate Results

In this example, I want to see the sales totals for each sales agent. In other words, I want to group the transactions by agent and get the sum for each. This will require the SELECT and GROUP BY clauses, as well as the SUM function.

  1. 1. First, type in the query to SELECT the columns: F and G. However, I actually want the sum of F, so I’ll wrap the SUM function around column F. Add the GROUP BY clause at the end so that the amounts are summed by sales agent.
Google Sheets QUERY Function Complete Guide SELECT GROUP BY Clause
Google Sheets QUERY Function (Complete Guide) - SELECT & GROUP BY Clause
  1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.
Google Sheets QUERY Function Complete Guide QUERY Function 4
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the cell with the query as the second parameter.
Google Sheets QUERY Function Complete Guide Add Query Clause 2
Google Sheets QUERY Function (Complete Guide) - Add Query Clause
  1. 4. Skip the third parameter and close the parenthesis.
Google Sheets QUERY Function Complete Guide Close Parenthesis 3
Google Sheets QUERY Function (Complete Guide) - Close Parenthesis
  1. 5. Press ‘Enter’ to see the results.
Google Sheets QUERY Function Complete Guide Query Results 4
Google Sheets QUERY Function (Complete Guide) - Query Results

Use ORDER BY Clause to Sort Results

Let’s add an ORDER BY clause to the previous query to get the results sorted by the sum in descending order.

  1. 1. Copy the query clause from the previous example and add the ORDER BY clause as shown below.
Google Sheets QUERY Function Complete Guide Add ORDER BY To Query
Google Sheets QUERY Function (Complete Guide) - Add ORDER BY To Query
  1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.
Google Sheets QUERY Function Complete Guide QUERY Function 5
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the cell with the query as the second parameter. Skip the third parameter and close the parenthesis.
Google Sheets QUERY Function Complete Guide Add Query
Google Sheets QUERY Function (Complete Guide) - Add Query
  1. 4. Press ‘Enter’ to see the results.
Google Sheets QUERY Function Complete Guide Query Results 5
Google Sheets QUERY Function (Complete Guide) - Query Results

Use LIMIT Clause to Restrict Number of Results

To limit the number of results returned, you can use the LIMIT clause followed by the number of results you want. For example, let’s add a clause to the previous query to limit the results to the top 2 agents.

  1. 1. Copy the query from the previous section and add the clause to limit the results to 2.
Google Sheets QUERY Function Complete Guide Add LIMIT Clause
Google Sheets QUERY Function (Complete Guide) - Add LIMIT Clause
  1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.
Google Sheets QUERY Function Complete Guide QUERY Function 6
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the cell with the query as the second parameter. Skip the third parameter and close the parenthesis.
Google Sheets QUERY Function Complete Guide Add Query 2
Google Sheets QUERY Function (Complete Guide) - Add Query
  1. 4. Press ‘Enter’ to see the results.
Google Sheets QUERY Function Complete Guide Query Results 6
Google Sheets QUERY Function (Complete Guide) - Query Results

Use LABEL Clause to Rename Columns

Finally, I will add another clause to the query that renames the return columns: LABEL.

  1. 1. Copy the formula from the previous section and add the clause to label the columns “sum amount” and “sales_agent” to ‘Total Sales’ and ‘Sales Agents’.
Google Sheets QUERY Function Complete Guide Add LABEL Clause
Google Sheets QUERY Function (Complete Guide) - Add LABEL Clause
  1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.
Google Sheets QUERY Function Complete Guide QUERY Function 7
Google Sheets QUERY Function (Complete Guide) - QUERY Function
  1. 3. Select the cell with the query as the second parameter. Skip the third parameter and close the parenthesis.
Google Sheets QUERY Function Complete Guide Add Query 3
Google Sheets QUERY Function (Complete Guide) - Add Query
  1. 4. Press ‘Enter’ to see the results.
Google Sheets QUERY Function Complete Guide Query Results 7
Google Sheets QUERY Function (Complete Guide) - Query Results

Conclusion

This guide provided an overview of the XLS file format, how to open and create XLS files, editing techniques, sharing and organizing XLS files, converting XLS files to other formats, best practices for using XLS files, and troubleshooting XLS files.

XLS files continue to be a popular format for storing and organizing data due to their simplicity and compatibility with many software applications. By following the best practices and troubleshooting tips outlined in this guide, you can ensure that your XLS files are accurate, secure, and free from corruption.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 13 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets