How to Use Google Sheet: Query in GoogleSheet, How Does It Work?

5 min readMar 4, 2023

Google Sheets is a powerful tool for organising, analysing, and presenting data in a spreadsheet format. For instance, a teacher may want to organise students score in a spreadsheet.

One of the most useful features of Google Sheets is the ability to use queries to filter and sort data within a sheet. For instance, in our teacher — students example, teachers may want to sort students name in alphabetical order.

In this blog post, we’ll explore how queries work in Google Sheets and how you can use them to manipulate your data more effectively.

What is a Query?

In Google Sheets, a query is a command that allows you to filter, sort, and manipulate data within a sheet. Queries use a language called Structured Query Language (SQL), which is the same language used to interact with databases.

You can use queries to perform a variety of tasks, such as:

Filtering data based on specific criteria
Sorting data by one or more columns
Grouping data by a specific column
Calculating the average, sum, or other statistics for a set of data.

Let’s explore how to use Queries in Google Sheets.

Query Syntax

The syntax for queries in Google Sheets follows the same basic structure as SQL queries.

QUERY(data, query, [headers])

Data: Data is an A1 notation of the range of cells to perform the query on.
Query: This consists of the query to perform. It is written in the Google Visualization API Query Language. This format is similar to the SQL queries structures. The value for Query must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
Headers: This is optional. It refers to the number of header rows of the top data. That is, the row to begin query. If omitted or set to -1, the value is guessed based on the content of data.

Here are some of the most commonly used query clauses in a Google Sheets query:

SELECT: Specifies the columns you want to include in the query. Use an asterisk (*) to select all columns.
WHERE: Filters the data based on specific criteria.
ORDER BY: Sorts the data by one or more columns.
GROUP BY: Groups the data by a specific column.
HAVING: Filters the grouped data based on specific criteria.
LIMIT: Limits the number of rows returned by the query.

Using Queries in Google Sheets

In order to use queries in Google Sheets, you first need to select the data you want to work with. Once you’ve selected your data, you can create a query by using the QUERY function.

Here’s an example of how to use the QUERY function:

Select the data you want to work with, to get its range.

2. In a new cell, enter the QUERY function, followed by the data range you want to query in parentheses. For example: =QUERY($A:$B,

3. After the data range, enter the query itself in quotes. For example: ”select A, B where B > ‘90’”.

4. Next, we’ll include the optional header value, because …

In this example, the query is filtering the data to only include rows A, B where the value in column B is greater than 90.

Here are a few other examples of how you can use queries in Google Sheets:

Selecting Specific Columns

You can use the query function, to select a specific column that matches a criteria.

For instance, the example below, selects only columns A, C, and E from the data range A1:E10.

=QUERY(A1:E10, "SELECT A,C,E")

When you hit the enter button, you’ll see that the three rows are selected.

Sorting Data

The query function can be used to sort data. For example, we can use query to sort data in descending or ascending order.

=QUERY(A1:E10, "SELECT * ORDER BY B DESC")

This query sorts the data by column B in descending order.

Grouping Data

Another example of how we can use the query formula, is to group data. For instance, this query groups the data by column A and calculates the sum of column B for each group.

=QUERY(A1:E10, "SELECT A, SUM(B) GROUP BY A")

Conclusion

In this article, we have explored queries, what it is and how to use queries in Google Sheet. While it may seem frugal using the Query function to sort data in the same sheet, Query can be used to transfer and sort data across sheets.

For instance, we can transfer data from sheet one to two by including the sheet name in the range. For example, if we want to transfer data from the sheet ‘Transactions’ to our present sheet, our range will look like this:

=QUERY('Transactions!A1:E10', "SELECT A")

Queries are a powerful tool for working with data in Google Sheets. When you use queries, you can filter, sort, and manipulate data to get the information you need quickly and easily. With a basic understanding of SQL syntax and the QUERY function, you can use queries to perform a wide variety of tasks within your spreadsheets.

If you’d like to streamline your accounting and invoicing activities, tryout PayTrack. We have built an addon for freelancers and small businesses. Also, we have released an middleware to integrate your checkout flow to PayTrack. This way, you don’t need to write extra codes as your receipts and invoices are generated from the checkout point, tracked until they’re paid and recorded automatically in our bookkeeping tool.

Please, send a message to peppubooks@gmail.com if you have any questions or need help using PayTrack.

Interested in more about GoogleSheet? Check our previous articles: