QUERYGoogle SheetsData AnalysisAdvancedSQL

Master the QUERY Function in Google Sheets

Learn how to use Google Sheets' powerful QUERY function to filter, sort, and analyze data like a database.

Introduction

The QUERY function is Google Sheets' most powerful data analysis tool. It allows you to write SQL-like queries to manipulate your data, making it possible to filter, sort, aggregate, and transform data in ways that would require multiple functions in Excel.

Prerequisites

Basic understanding of Google Sheets
Familiarity with spreadsheet ranges
Basic SQL knowledge is helpful but not required

1QUERY Function Basics

The QUERY function takes a data range and a query string, returning the filtered and transformed results.

Basic syntax

QUERY has a simple syntax but powerful capabilities.

=QUERY(data, query, [headers])

Understanding the query string

The query string uses a SQL-like syntax. Columns are referenced as Col1, Col2, Col3, etc.

Headers parameter (optional): Use 1 if your data has column headers.

Column references are case-insensitive in the query string.

2Filtering Data with WHERE

Use the WHERE clause to filter rows based on conditions.

Example

=QUERY(A1:C100, "SELECT * WHERE Col1 = 'Sales'", 1)

Result: All rows where Column A equals 'Sales'

Selects all columns where Column 1 (A) matches 'Sales' exactly.

Use single quotes for text values, no quotes for numbers.

Multiple conditions can be combined with AND/OR.

3Sorting with ORDER BY

Sort your results using ORDER BY with optional ASC (ascending) or DESC (descending).

Example

=QUERY(A1:C100, "SELECT Col1, Col3 ORDER BY Col3 DESC", 1)

Result: Columns A and C sorted by column C in descending order

Selects columns 1 and 3, then sorts by column 3 from highest to lowest.

ASC is the default if not specified.

You can sort by multiple columns.

4Aggregation with GROUP BY

Use GROUP BY with aggregate functions like SUM, COUNT, AVG to summarize data.

Example

=QUERY(A1:C100, "SELECT Col1, SUM(Col3) GROUP BY Col1", 1)

Result: Total sales by category

Groups by column 1 (category) and sums column 3 (sales) for each group.

All non-aggregated columns must be in GROUP BY.

Common aggregations: SUM, COUNT, AVG, MAX, MIN.

5Combining Multiple Conditions

Create complex queries by combining WHERE, ORDER BY, and GROUP BY.

Example

=QUERY(A1:C100, "SELECT Col1, SUM(Col3) WHERE Col2 >= DATE '2024-01-01' GROUP BY Col1 ORDER BY SUM(Col3) DESC", 1)

Result: 2024 sales by category, sorted from highest to lowest

Filters for dates in 2024, groups by category, sums sales, and sorts descending.

Use DATE keyword with YYYY-MM-DD format for date comparisons.

Parentheses can group conditions for complex logic.

Functions Used

QUERY

Runs a SQL-like query on your data

FILTER

Filters a range based on conditions

SUMIF

Sums values based on a single condition

Summary

The QUERY function is Google Sheets' most versatile tool for data analysis. By learning its SQL-like syntax, you can filter, sort, aggregate, and transform data in ways that would require multiple Excel functions. Mastering QUERY will significantly boost your data analysis capabilities.

Next Steps

Practice basic WHERE and ORDER BY queries
Try grouping data with GROUP BY
Explore pivot table alternatives with QUERY
Learn how to join multiple sheets with QUERY