Google Sheets QUERY Function: SQL-Style Data Analysis Guide

Introduction to the QUERY Function

If you’ve ever wished Google Sheets could behave more like a database, the QUERY function is your answer. It allows you to analyze, filter, and transform data using a syntax similar to SQL (Structured Query Language). Instead of stacking multiple formulas like FILTER, SORT, and UNIQUE, you can often accomplish everything in one clean, readable formula.

This guide walks you through how the QUERY function works, how to write queries, and how to apply it to real-world data analysis tasks.

What Is the QUERY Function?

The QUERY function retrieves and manipulates data from a dataset using a query string. Its syntax is:

=QUERY(data, query, [headers])

• data: The range of cells to analyze
• query: A text string written in a SQL-like language
• headers (optional): Number of header rows

Example:

=QUERY(A1:D10, "SELECT A, B WHERE C > 100", 1)

This selects columns A and B where column C is greater than 100.

Understanding QUERY Syntax

The QUERY language uses familiar SQL keywords. Here are the most important ones:

• SELECT: Choose columns
• WHERE: Filter rows
• ORDER BY: Sort results
• GROUP BY: Aggregate data
• LIMIT: Restrict number of rows
• LABEL: Rename columns

Column Referencing

You can reference columns in two ways:

• By letter: A, B, C
• By Col notation: Col1, Col2, Col3

Example:

=QUERY(A1:C10, "SELECT Col1, Col3", 1)

Basic Examples

1. Filter Data with WHERE

Suppose you have sales data with columns: Date, Product, Revenue.

=QUERY(A1:C100, "SELECT A, B WHERE C > 500", 1)

This returns rows where revenue exceeds 500.

2. Sort Data with ORDER BY

=QUERY(A1:C100, "SELECT A, B, C ORDER BY C DESC", 1)

This sorts results by revenue in descending order.

3. Limit Results

=QUERY(A1:C100, "SELECT A, B LIMIT 5", 1)

This shows only the first five rows.

Working with Text and Dates

Text Conditions

Text values must be wrapped in single quotes:

=QUERY(A1:C100, "SELECT A, B WHERE B = 'Laptop'", 1)

Contains and Matches

• CONTAINS: partial match
• MATCHES: regular expressions

Example:

=QUERY(A1:C100, "SELECT A WHERE B CONTAINS 'Pro'", 1)

Date Filtering

Dates require a specific format:

=QUERY(A1:C100, "SELECT A WHERE A > date '2024-01-01'", 1)

Aggregation with GROUP BY

The QUERY function can summarize data using aggregation functions:

• SUM()
• AVG()
• COUNT()
• MAX()
• MIN()

Example: Total Revenue by Product

=QUERY(A1:C100, "SELECT B, SUM(C) GROUP BY B", 1)

This groups data by product and calculates total revenue.

Adding Labels

=QUERY(A1:C100, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Revenue'", 1)

Combining Multiple Conditions

You can combine filters using AND and OR:

=QUERY(A1:C100, "SELECT A, B WHERE C > 500 AND B = 'Laptop'", 1)

Real-World Use Cases

1. Sales Dashboard

Use QUERY to build a dynamic summary table:

=QUERY(A1:D500, "SELECT B, SUM(D) GROUP BY B ORDER BY SUM(D) DESC", 1)

This creates a ranked list of products by revenue.

2. Employee Performance Tracking

=QUERY(A1:E200, "SELECT C, AVG(E) GROUP BY C", 1)

This calculates average performance scores by department.

3. Data Cleaning

Remove duplicates using grouping:

=QUERY(A1:B100, "SELECT A, B GROUP BY A, B", 1)

Tips for Writing Better QUERY Formulas

• Start simple: Build your query step by step
• Use line breaks: Write queries in a separate cell for readability
• Check headers: Incorrect header count causes errors
• Use Col notation: Helps when column letters change
• Test conditions: Validate filters before combining them

Common Errors and Fixes

1. “Unable to parse query string”

Usually caused by:

• Missing quotes
• Incorrect syntax
• Wrong column names

2. Incorrect Results

Check:

• Data types (text vs numbers)
• Date formatting
• Header row count

3. Blank Output

This often means your filter conditions returned no matches.

QUERY vs Other Functions

Here’s when to use QUERY instead of other tools:

• Use QUERY for multi-step transformations in one formula
• Use FILTER for simple row filtering
• Use SORT for basic sorting
• Use PIVOT TABLES for visual summaries

QUERY shines when you want SQL-like control without leaving Sheets.

Advanced Example: Full Analysis in One Formula

Let’s combine multiple concepts:

=QUERY(A1:D500, "SELECT B, SUM(D) WHERE D > 100 GROUP BY B ORDER BY SUM(D) DESC LIMIT 10 LABEL SUM(D) 'Top Revenue'", 1)

This formula:

• Filters rows where revenue is greater than 100
• Groups by product
• Calculates total revenue
• Sorts results in descending order
• Limits output to top 10
• Renames the column

When QUERY Might Not Be Ideal

While powerful, QUERY has limitations:

• Learning curve for SQL-like syntax
• Less intuitive for beginners
• Error messages can be vague

In simple cases, combining FILTER and SORT may be easier.

Final Thoughts

The Google Sheets QUERY function is one of the most powerful tools available for data analysis. It brings database-style querying into a familiar spreadsheet environment, letting you clean, filter, and summarize data efficiently.

Once you understand the core structure—SELECT, WHERE, GROUP BY—you can replace complex multi-formula setups with a single, elegant solution. Start with small examples, build confidence, and gradually apply it to real datasets.

With practice, QUERY becomes not just a function, but a productivity multiplier.