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.

1. 2. Type the QUERY function in the cell where you want the results.

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.

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.

1. 5. Since the headers occupy only one row, I’ll skip the last parameter. Close the parenthesis and press ‘Enter’.

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.

1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.

1. 3. Select the cell with the query as the second parameter.

1. 4. Skip the third parameter and close the parenthesis.

1. 5. Press ‘Enter’ to see the 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.

1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.

1. 3. Select the cell with the query as the second parameter.

1. 4. Skip the third parameter and close the parenthesis.

1. 5. Press ‘Enter’ to see the 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.

1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.

1. 3. Select the cell with the query as the second parameter.

1. 4. Skip the third parameter and close the parenthesis.

1. 5. Press ‘Enter’ to see the 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.

1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.

1. 3. Select the cell with the query as the second parameter. Skip the third parameter and close the parenthesis.

1. 4. Press ‘Enter’ to see the 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.

1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.

1. 3. Select the cell with the query as the second parameter. Skip the third parameter and close the parenthesis.

1. 4. Press ‘Enter’ to see the 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’.

1. 2. In the cell where you want the results, type in the QUERY function and add the range as the first parameter.

1. 3. Select the cell with the query as the second parameter. Skip the third parameter and close the parenthesis.

1. 4. Press ‘Enter’ to see the 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.