# QUERY Function – Google Sheets

The QUERY function in Google Sheets uses SQL-like syntax to analyze your spreadsheet data and return it in a new table. While the addition of the SQL syntax provides this function with flexibility, it also makes it harder to learn.

The family of FILTER functions in Google Sheets offers a simpler alternative to the QUERY function. But they don’t recognize a header row, and they are limited in their ability to combine filters, sorts, limits, etc.

Go to this spreadsheet for examples of the QUERY function shown above that you can study and use anywhere you would like.

## Syntax

`=QUERY(data, query, [headers])`

• `data` – Location of the data
• `query` – The query statement. The query is composed of optional clauses that you must use in order.
• `SELECT` – The columns to return. If omitted, the query returns all columns.
• `WHERE` – Conditions that rows must meet
• `GROUP` BY – Aggregates values
• `PIVOT` – Aggregates values into new columns
• `ORDER BY` – Sorts rows by values
• `LIMIT` – Limits the number of rows returned
• `OFFSET` – Skips a specified number of rows
• `LABEL` – Creates column labels
• `FORMAT` – Formats outputs
• `OPTIONS` – Sets additional options
• `[headers]` – The number of header rows at the top of the `data`

## Examples

### Example 1 – Return Values with Matching Words

This table lists inventory held at three different warehouses. You want to list the items that are in the `North` `Warehouse`. We’ll use the following QUERY function.

`=QUERY(A1:D13,"SELECT * WHERE D='North'",1)`

The output of this QUERY function is a list of only the rows that specify North as the warehouse location.

Let’s break this function down into its parts.

The first part of the function, `A1:D13`, is the `data` value. The value given for `data` is the table’s location on which we want to perform the query. Additionally, you could create a named range for the table, but we’re just using the simple cell reference of A1:D13 to tell Sheets where to look.

Next, notice that double quotes surround the entire query section, and single quotes surround the string value of North since it is a word instead of a number. In other spreadsheet formulas, you should wrap strings with double quotes, but double quotes are used around the entire query leaving you to work with single quotes. Be sure to capitalize the N in North as the input for the clauses is case-sensitive.

The `SELECT *` clause tells the QUERY function which columns to return. The `*` lets Sheets know to return all of the columns. If you wanted to identify specific columns, the equivalent SELECT clause would be `SELECT A, B, C, D` with the column letters used as their identifiers. Note that you do not use the names of the column headers such as `Item` or `Cases`.

The `WHERE D='North'` clause limits the output to only rows that have the value of `North` in column `D`.

The `1` at the end of the function uses the optional `[headers]` argument to tell Sheets that there is only one header row. Since `1` is the default, this function would operate the same if this argument were left off.

## Example 2 – Return Values Less Than a Certain Amount

Next, we will be using the same inventory listing, and we want to return all inventory items with less than 140 items at any warehouse.

`=QUERY(A1:D13,"SELECT * WHERE (B*C<140)'",1)`

We will still use `SELECT *` as we want to return all of the columns from the source table.

This query will introduce two new concepts.

First, we will still use the `WHERE` clause, but we will use it for a number value instead of a string. Therefore, we will not surround `140` with `'`s as we did for the `Wharehouse` name since `140` is a number value.

Second, there is no column for the total amount of units in the source table. Because we need the total amount of units for our QUERY, we will calculate it by multiplying the `Cases` by the `Amt. per Case` using the formula `B*C` as `Cases` is in column `B` and `Amt. per Case` is in column `C`.

## Example 3 – Return Calculated and Labeled Column

You may want to see the result of the `B*C` calculations performed in example 2 in your table. We limited our results to items with a total count of less than 140. However, the output was not showing the total values in the source or output tables. Now we are going to show that value. Also, as the table is getting more crowded, we will exclude the Warehouse column to simplify the table.

To do this, we will change the select portion of the query from SELECT * to SELECT A, B, C, (B*C) and then add in the new LABEL clause. Changing the clause will give the new column with (B*C) a readable label of Total. As you write the formula, notice the word Total will need to be surrounded in single quotes since it is a string.

`=QUERY(A1:D13, "SELECT A, B, C, (B*C) LABEL (B*C) 'Total'",1)`

Now that we can see the `Total` column, we will add the `WHERE` clause back to the query. Adding the `Total` column enables us to see there are no values left in the `Total` column over `140`.

`=QUERY(A1:D13,"SELECT A, B, C, (B*C) WHERE (B*C<140) LABEL (B*C) 'Total'",1)`

As you add more criteria to your queries, you will notice that, while the query becomes longer, it does not become a jumble of nested formulas like it would if you were combining other spreadsheet functions such as FILTER and SORTN for an equivalent formula.

## Example 4 – Return Values Less Than a Certain Amount and Matching Words

We will remove the `Total` column from the previous example and its label to keep the function easy to read.

Now we will combine the concepts from the first two examples. We will query the same inventory and return all `North` warehouse items with a quantity greater than `140`. Add both criteria to the `WHERE` clause with the word `AND` between them to combine these concepts.

`=QUERY(A1:D13,"SELECT * WHERE (B*C<140) AND D='North'",1)`

## Example 5 – Sort and Limit the QUERY Responses

Let’s look at a few more clauses that can change the query function’s output. We will use the same source table of inventory and remove the `WHERE` or `LABEL` clauses for simplicity.

We want to return the five `Item` values with the greatest `Amt. per Case` for this query. To do this, we will sort the list by `Amt. per Case` in descending order and limit the output to five rows. The `ORDER BY` clause for columns `C` will sort the data by the contents of column `C`, and the specification of `DESC` tells the query to sort in descending order. The default sort order, which you don’t need to specify, is ascending.

`=QUERY(A1:D13,"SELECT * ORDER BY C DESC LIMIT 5",1)`

## Example 6 – Grouping the QUERY Data

Now that we have covered filtering and rearranging the data, we will summarize it. Using the `SUM` aggregation function combined with the `GROUP BY` clause, we can arrive at the number of `Cases` for each `Item`.

We will use the same source data as the previous exercises.

Column `A` will be passed to the `SELECT` and the `GROUP BY` clauses. Every column listed in the `SELECT` clause must be used in either the `GROUP BY` clause or the aggregation function, which is `SUM` in this case.

`=QUERY(A1:D13,"SELECT A, SUM(B) GROUP BY A",1)`

## Example 7 – Pivoting the QUERY Data

Similar to Example 6, we will be using another clause that groups the data, but this one, called `PIVOT`, spreads the values into new columns for an additional layer of grouping like a pivot table.

`=QUERY(A1:D13,"SELECT A, SUM(B) GROUP BY A PIVOT D",1)`

## Conclusion

The query function is potent, and we have only scratched the surface of its capabilities. For help with your queries, Google’s query language documentation is an excellent place to start.

## Live QUERY Examples in Sheets

Go to this spreadsheet for examples of the QUERY function shown above that you can study and use anywhere you would like.

### 2 thoughts on “QUERY Function – Google Sheets”

1. Hello, I was trying to get data from sheets, data from a google form, organized in a certain way. I want the data to go to a new sheet if it contains a response value. I have been trying to get this to work but I am not sure how to do this or if it is a possible option. Would you be able to help me get this figured out?

• 