The Google Sheets QUERY function allows you to efficiently manipulate data for a wide range of use cases. It helps extract meaningful information from large or complex datasets and makes it easier to build reports, summaries, charts, and more.

One of the key strengths of the QUERY function is that it uses the Google Visualization API Query language, which enables powerful data operations such as filtering, grouping, sorting, pivoting, and aggregation—all within a single formula.

In short, the QUERY function is the most powerful data-manipulation tool in Google Sheets. This tutorial covers everything you need to understand and confidently use the QUERY function through clear explanations and practical examples.

Google Sheets QUERY Function Syntax (With Examples)

Let’s begin with the syntax of the QUERY function in Google Sheets.

Unlike many other functions, the QUERY function is not easy to understand by simply looking at its arguments. Don’t worry—its logic becomes much clearer once you work through examples step by step, which is exactly what this guide focuses on.

Syntax

QUERY(data, query, [headers])

Arguments

data
The range of cells (table) on which the query is performed. For accurate results, each column should contain a single data type.

What is mixed-type data?
For example, if column A contains item codes such as 1111, 1112, 1113, and so on, all values should be formatted consistently—either as numbers or as text, but not a mix of both.
In general:

• A numeric column should contain only numbers
• A date column should contain only dates
• A text column should contain only text values

query
The query statement you want to run. It must be enclosed in double quotes, or you can store the query text in a cell and reference that cell in the formula.

headers
The number of header rows in the source data. Most datasets have either one header row or none.
If this argument is omitted or set to -1, Google Sheets automatically guesses the number of header rows based on the data.

Example

=QUERY(A1:F1000, "SELECT A, C", 1)

In this formula:

• A1:F1000 is the data range
• "SELECT A, C" is the query
• 1 indicates that the first row contains headers

The result returns only columns A and C from the dataset.

To truly master the Google Sheets QUERY function, the key is understanding the query argument, which is where most of the power—and complexity—lies. The following sections focus heavily on breaking this down with practical examples.

How the QUERY String Works (Simplifying the Query Argument)

When you read a QUERY formula written by someone else, the most confusing part is usually the query string enclosed in quotation marks. This section contains clauses (keywords), data-manipulation logic, and other language elements that can look intimidating at first.

Trying to learn every clause and function in a single tutorial isn’t practical. Instead, this guide focuses on helping you understand the essential data-manipulation techniques used in the Google Sheets QUERY function, step by step.

As you progress, I’ll also point you to more advanced tutorials on this blog so you can gradually master one of the most powerful functions available in Google Sheets.

Let’s get started.

Sample Dataset Used in All QUERY Function Examples

We’ll use a sample dataset arranged across six columns and sixteen rows, located in the range A1:F16 on the Sheet1 tab.

The header row (A1:F1) contains the following field names:

• Name
• DOB
• Age Group
• Gender
• Date of Joining
• Age

You can get a copy of the complete dataset by clicking the button below.

Sample Data

All QUERY function examples in this guide are based on this sample sheet. Before proceeding, it’s a good idea to scroll up and review the QUERY function syntax once more.

As a reminder, the QUERY function has three arguments:

• data
• query
• headers

Based on this sample sheet:

• The data argument can be A1:F (open range) or A1:F16 (closed range)
• The headers argument is 1, since the first row contains column labels

In the following sections, we’ll focus primarily on the query argument, where most of the QUERY function’s power lies.

SELECT Clause in Google Sheets QUERY (With Examples)

This section explains how to select columns in the QUERY function using the SELECT clause.

Basic SELECT Examples

Using the sample dataset (six columns in Sheet1!A1:F16), you can select and rearrange columns with the SELECT clause in the Google Sheets QUERY function.

=QUERY(Sheet1!A1:F16, "SELECT A, B, F, C, D, E", 1)

The formula above returns the selected columns in a different order.

=QUERY(Sheet1!A1:F16, "SELECT A, E", 1)

This formula returns only columns A and E.

If you replace "SELECT A, E" with "SELECT *", the QUERY function returns all columns from the source table in their original order.

Returning All Columns Without a SELECT Clause

An alternative way to return all columns in their original order is to omit the query argument entirely:

=QUERY(Sheet1!A1:F16,, 1)

Here, only the data and headers arguments are specified.

If you specify 16 instead of 1 for the headers argument, the formula returns a 1-row × 6-column result, treating all rows as headers.
For more details on this behavior, see: The Flexible Array Formula to Join Columns in Google Sheets.

Using Arithmetic, Scalar, and Aggregation Functions in SELECT

So far, we’ve selected existing columns without modifying their values. Let’s now look at how to process column data using functions and operators inside the SELECT clause.

Scalar Function: YEAR() in the SELECT Clause

=QUERY(Sheet1!A1:F16, "SELECT A, YEAR(E)", 1)

This formula returns:

• Column A (Name)
• The year of joining, extracted from column E (Date of Joining)

Common Scalar Functions Used in QUERY

Function	Purpose
year()	Extracts the year from a date or datetime
month()	Returns month (0–11) from a date or datetime
day()	Returns day of the month
hour()	Returns hour from a datetime
minute()	Returns minutes from a datetime
second()	Returns seconds from a datetime
millisecond()	Returns milliseconds
quarter()	Returns quarter of the year
dayOfWeek()	Returns day of week (Sun = 1, Sat = 7)
now()	Returns current datetime (GMT timezone). Typically used in the WHERE clause, not SELECT.
dateDiff()	Returns difference between two dates (days)
toDate()	Converts a value to a date
upper()	Converts text to uppercase
lower()	Converts text to lowercase

Further reading on scalar functions in QUERY:

• How to Use the DATEDIFF() Function in Google Sheets QUERY
• How to Use the toDate() Scalar Function in Google Sheets QUERY
• Use QUERY to Bulk Convert Text Cases in Google Sheets

Aggregation Function: AVG() in the SELECT Clause

To calculate the average age from column F:

=QUERY(Sheet1!A1:F16, "SELECT AVG(F)", 1)

This formula returns the average value of the Age column. If you want to perform additional calculations on QUERY results, see Sum, Multiply, Subtract & Divide in Google Sheets.

Combining Scalar Functions and Arithmetic Operators

To calculate age at the time of joining, subtract the year of birth from the year of joining:

=QUERY(Sheet1!A1:F16, "SELECT A, YEAR(E) - YEAR(B)", 1)

Note: This calculation considers only years, so the result may not be fully accurate due to month and day differences.

For more examples using +, -, *, and / inside QUERY, see How to Use Arithmetic Operators in Query in Google Sheets.

Column Identifiers Explained

In QUERY formulas, column identifiers are usually column letters such as A, B, C, … AA, AB, and so on.

When your data is a physical range like A1:F16, these standard column letters apply.

However, when QUERY works on the output of another formula (an expression), columns are identified as:

• Col1 → first column
• Col2 → second column
• Col3 → third column
  …and so on.

Inserting a Custom Column in SELECT

The following formula inserts a column containing hyphens between columns A and B:

=QUERY(Sheet1!A1:F16, "SELECT A, '-', B", 1)

This technique is useful when you want to add separators or placeholder values in the QUERY output.

Filtering Data Using QUERY WHERE Clause (Examples)

Above, we explored how to use the SELECT keyword in the Google Sheets QUERY function to choose specific columns.

Now let’s look at how to filter rows.

To filter rows based on conditions, you use the WHERE clause.

Common WHERE Clause Examples

In the WHERE clause, you use:

• Comparison operators (simple or complex), and/or
• Logical operators such as AND, OR, and NOT

Using the sample data, the following formula filters rows where the gender is “M”:

=QUERY(Sheet1!A1:F, "SELECT A, D WHERE D = 'M'", 1)

The next formula filters names where the age is 12:

=QUERY(Sheet1!A1:F, "SELECT A WHERE F = 12", 1)

The following QUERY filters names whose date of joining is 01-01-2015:

=QUERY(Sheet1!A1:F, "SELECT A WHERE E = DATE '2015-01-01'", 1)

These examples serve two purposes:

• They demonstrate how to use the WHERE keyword in the QUERY function
• They show how to compare different literal types—text, numbers, and dates

Filtering Resources

For more comprehensive data-filtering techniques, refer to the tutorials below:

• Literals in Google Sheets QUERY – How to Use Them
• WHERE Clause in Google Sheets QUERY: Logical Conditions Explained (Hub)
• String Matching in Google Sheets QUERY (Hub)

Advanced Filtering Tips

In the examples above, filtering a Boolean (TRUE / FALSE) column is not shown because the sample dataset does not include one.

If column G contains Boolean values, you can filter it as follows:

WHERE G = TRUE

or

WHERE G = FALSE

Case Sensitivity in QUERY

String comparisons in the QUERY function are case-sensitive. To perform a case-insensitive match, use the UPPER() or LOWER() scalar functions.

Example:

=QUERY(Sheet1!A1:F, "SELECT A WHERE LOWER(D) = 'm'", 1)

Open vs. Closed Ranges in WHERE Filtering

You may have noticed that the examples above use an open range (Sheet1!A1:F) instead of a closed range (Sheet1!A1:F16).

This approach is useful when filtering conditions may remove blank rows from the result.

To explicitly exclude blank rows, you can use IS NOT NULL, as shown below:

=QUERY(Sheet1!A1:F, "SELECT A, E, F WHERE A IS NOT NULL", 1)

GROUP BY and Aggregation in Google Sheets QUERY (Examples)

So far, we’ve learned how to select columns and filter rows. This section focuses on how to group and aggregate data using the QUERY function in Google Sheets.

To do this, we use the GROUP BY clause. Like the WHERE clause, GROUP BY is a broad topic, so we’ll begin with a few basic examples before pointing you to more advanced resources.

Key Points About GROUP BY in QUERY

Keep the following rules in mind when using the GROUP BY clause:

• GROUP BY aggregates values across multiple rows
• One output row is created for each distinct group
• Results are automatically sorted by the grouping columns
• Every non-aggregated column in the SELECT clause must appear in the GROUP BY clause
  • Aggregated columns (e.g., SUM(), COUNT(), AVG()) do not need to be grouped

GROUP BY with SUM, COUNT, and AVG

The following formula groups data by Age (column F) and returns the count for each age:

=QUERY(Sheet1!A1:F16, "SELECT F, COUNT(F) GROUP BY F", 1)

This formula groups data by Age and Date of Joining, then returns the count:

=QUERY(Sheet1!A1:F16, "SELECT F, E, COUNT(F) GROUP BY F, E", 1)

Using GROUP BY with Open Ranges

When using an open range, it’s important to exclude blank rows to avoid incorrect aggregation results.

=QUERY(Sheet1!A1:F, "SELECT F, COUNT(F) WHERE F IS NOT NULL GROUP BY F", 1)

Scalar Functions with GROUP BY

The following example groups data by the year of joining and returns the count:

=QUERY(A1:F, "SELECT YEAR(E), COUNT(E) WHERE E IS NOT NULL GROUP BY YEAR(E)", 1)

Here:

• YEAR(E) extracts the year from the Date of Joining column
• GROUP BY is applied to the computed value, not the original column

Arithmetic Operations with GROUP BY

You can also group by calculated expressions, as shown below:

=QUERY(A1:F, "SELECT YEAR(NOW()) - YEAR(E), COUNT(E) WHERE E IS NOT NULL GROUP BY YEAR(NOW()) - YEAR(E)", 1)

This formula:

• Calculates the number of years since joining
• Groups rows by that calculated value
• Returns the count for each group

Note: The NOW() function is used here only to demonstrate arithmetic grouping. In practice, it’s more commonly used in the WHERE clause for time-based filtering.

Create Pivot Tables Using QUERY PIVOT Clause in Google Sheets

Let’s understand the PIVOT clause in the QUERY function with a few practical examples in Google Sheets.

Example 1: GROUP BY Without PIVOT (Baseline)

The following QUERY formula groups data by Date of Joining and Gender, then returns the count:

=QUERY(Sheet1!A1:F16, "SELECT E, D, COUNT(E) GROUP BY E, D", 1)

The result contains three columns:

• Date of Joining
• Gender
• Count of Date of Joining

This output is useful, but it’s still in a flat, row-based format.

Example 2: GROUP BY with PIVOT

Now, let’s use the PIVOT clause:

=QUERY(Sheet1!A1:F16, "SELECT E, COUNT(E) GROUP BY E PIVOT D", 1)

In this formula:

• Column D (Gender) is removed from the SELECT and GROUP BY clauses
• Instead, it is used in the PIVOT clause

As a result:

• The first column remains Date of Joining (column E)
• Additional columns are created for each distinct value in Gender
• Since Gender has two values (“M” and “F”), two pivoted columns appear
• The aggregated counts are displayed under these pivoted columns

This transforms row-based data into a cross-tabulated (pivot) layout.

Why Use PIVOT?

Pivoted QUERY results are especially useful for:

• Creating bar, column, line, or pie charts
• Building quick summary reports
• Preparing clean datasets for data visualization in Google Sheets

Can You Use PIVOT Without GROUP BY?

Yes—you can.

Here’s an example:

=QUERY(Sheet1!A1:F16, "SELECT COUNT(A) PIVOT E", 1)

In this case:

• The pivoted column (E – Date of Joining) creates one column per distinct date
• The count is calculated across the entire dataset

Try this formula yourself to see how QUERY behaves without an explicit GROUP BY clause.

Learn More About QUERY PIVOT

For advanced pivoting and reporting techniques, see this hub guide: QUERY Pivot & Reporting in Google Sheets

Sort QUERY Results Using ORDER BY Clause

The ORDER BY clause allows you to sort QUERY results based on the values in one or more columns in Google Sheets.

You can control:

• Which column(s) to sort by
• Sort direction (ASC or DESC)
• Sorting priority when using multiple columns

Example 1: Sorting by Column Identifiers

The following formula sorts the output by Name (column A) in ascending order:

=QUERY(Sheet1!A1:F16, "SELECT A, B, F ORDER BY A ASC", 1)

This formula sorts:

• Date of Birth (column B) in descending order, then
• Name (column A) in ascending order

=QUERY(Sheet1!A1:F16, "SELECT A, B, F ORDER BY B DESC, A ASC", 1)

When multiple columns are used in ORDER BY, sorting is applied from left to right.

Example 2: Sorting by Aggregated Results

You can also sort QUERY results using the output of an aggregation function.

=QUERY(Sheet1!A1:F, "SELECT F, COUNT(F) WHERE F IS NOT NULL GROUP BY F ORDER BY COUNT(F) DESC", 1)

This formula:

• Groups data by Age (column F)
• Counts the occurrences
• Sorts the result by the count in descending order

What Can Be Used in ORDER BY?

In the ORDER BY clause, you can sort by:

1. Column identifiers (e.g., A, B, F)
2. Computed values, such as:
   • Aggregation functions (COUNT(), SUM(), AVG())
   • Scalar functions
   • Arithmetic expressions

Limit and Offset Rows in QUERY Function (LIMIT & OFFSET)

The purpose of the LIMIT clause (keyword) in the Google Sheets QUERY function is to restrict the number of rows returned by a query.

In the formula below, LIMIT 3 constrains the output to three rows, excluding the header row.

Example (LIMIT):

=QUERY(Sheet1!A1:F, "SELECT F, COUNT(F) WHERE F IS NOT NULL GROUP BY F ORDER BY COUNT(F) DESC LIMIT 3", 1)

The purpose of the OFFSET clause (keyword) is to skip a specified number of rows from the beginning of the result set.

Example #1 (OFFSET only):

=QUERY(Sheet1!A1:F, "SELECT * OFFSET 3", 1)

This formula skips the first three rows and returns the remaining rows.

Example #2 (LIMIT + OFFSET):

=QUERY(Sheet1!A1:F, "SELECT F, COUNT(F) WHERE F IS NOT NULL GROUP BY F ORDER BY COUNT(F) DESC LIMIT 3 OFFSET 1", 1)

In this example:

• The query first offsets (skips) one row
• Then applies LIMIT 3 to the remaining result set

So, OFFSET is evaluated before LIMIT in the QUERY function.

For practical use cases such as Top-N results, skipping ranked rows, and row-limiting strategies, see the hub: Top N and Row Limiting Techniques in Google Sheets QUERY.

Label and Format QUERY Output (LABEL & FORMAT Clauses)

Sometimes, a QUERY formula may return unfriendly or “ugly” column headers—especially when using expressions, scalar functions, or arithmetic operations.

For example, the formula below produces an auto-generated header such as
difference(year(Date of Joining), year(DOB)):

=QUERY(Sheet1!A1:F16, "SELECT A, YEAR(E) - YEAR(B)", 1)

As a result, the output may look like this:

Name | difference(year(Date of Joining), year(DOB))
Kelvin | 5
Andy   | 5
Chad   | 11
...

Let’s rename this header to something more readable.

Renaming Labels in the Header (LABEL Clause)

The LABEL clause allows you to define custom headers for one or more columns in the QUERY output.

Example:

=QUERY(Sheet1!A1:F16, "SELECT A, YEAR(E)-YEAR(B) LABEL YEAR(E)-YEAR(B) 'Age at the Time of Joining'", 1)

This formula replaces the default label
difference(year(Date of Joining), year(DOB))
with the more readable header Age at the Time of Joining.

Related: Understand the LABEL Clause in Google Sheets QUERY.

Formatting Values in QUERY Output (FORMAT Clause)

The FORMAT clause is used to format dates, times, and numbers in QUERY results, similar to applying a Custom Number Format in Google Sheets.

Example:

=QUERY(Sheet1!A1:F16, "SELECT * FORMAT B 'YYYY-MM-DD'", 1)

This formats the date values in column B using the specified date pattern.

Related: Formatting Date, Time, and Numbers in Google Sheets QUERY.

Common QUERY Function Mistakes and Fixes

When using the Google Sheets QUERY function, you may encounter a few common issues. Most of these errors are easy to fix once you understand how QUERY interprets data.

1. Incorrect Header Argument (0 vs 1)

One of the most frequent mistakes is specifying the wrong header value in the QUERY function.

• Use 1 if the data range includes a header row.
• Use 0 if the data range does not include headers.

Using the wrong header value can cause incorrect results or unexpected column references.

2. Incorrect Clause Order

The QUERY function requires clauses to follow a specific order. If the order is incorrect, the formula will return an error.

Always follow the correct clause sequence, such as:

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

3. Using Incorrect Literals

Another common issue is not using the correct literals in QUERY statements.

Examples:

• Text values must be enclosed in single quotes ('Text')
• Dates must use the proper DATE 'YYYY-MM-DD' format
• Numbers should not be enclosed in quotes

Incorrect literal usage often results in errors or incorrect filtering.

These issues and their solutions have already been explained in detail in the sections above.

Conclusion: When and Why to Use Google Sheets QUERY Function

This guide has comprehensively covered everything you need to understand and confidently use the QUERY function in Google Sheets—from selecting and filtering data to grouping, pivoting, sorting, limiting results, and formatting output.

The QUERY function is especially powerful when you need:

• SQL-like data analysis without helper columns
• Dynamic reports and dashboards
• Clean, scalable formulas for large datasets

If you have any questions, edge cases, or use-case scenarios, feel free to drop them in the comments below.

Reference: Query Language Reference (Version 0.7)

Thanks for your time—happy querying!