If there is one formula that separates beginners from power users, it is the QUERY function in Google Sheets. Instead of stringing together multiple messy VLOOKUPs, FILTERs, and SUMIFs, QUERY allows you to write a single, elegant string of text (similar to SQL) to pull, sort, filter, and summarize massive datasets instantly.
Whether you are building a dashboard or just trying to quickly slice data without creating a Pivot Table, mastering the QUERY function will easily save you hours of work every week. Below is the ultimate guide on how to use it, broken down into the 5 most important clauses, complete with practical, real-world examples.
1. Basic Filtering: SELECT & WHERE
The foundation of any QUERY is deciding which columns you want to see (SELECT) and what specific criteria the rows must meet to be included (WHERE).
Syntax Reference
=QUERY(data_range, "SELECT Col1, Col2 WHERE Col3 = 'Condition'", headers)
Practical Example 1: Selecting Specific Columns
Suppose you have a massive raw data dump spanning columns A through F, but you only want to extract the Employee Names (Col A) and their Salaries (Col D).
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:F, "SELECT A, D", 1) |
Pulls only Column A and Column D from the entire dataset, maintaining the original row order and keeping the 1 header row. |
Practical Example 2: Filtering Rows by Condition
Now, let’s extract the Names (A) and Salaries (D), but only for employees in the “Sales” department (Col B).
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:F, "SELECT A, D WHERE B = 'Sales'", 1) |
Filters the dataset to only show rows where the value in Column B is exactly “Sales”. Note that text strings in QUERY must be wrapped in single quotes (' '). |
Pro Tip
You can chain multiple conditions using AND / OR. For example: WHERE B = 'Sales' AND D > 50000 will only return Sales employees making over $50k.
2. Sorting & Trimming: ORDER BY & LIMIT
Once you have extracted your data, you rarely want it displayed in a random order. ORDER BY allows you to sort alphabetically or numerically, and LIMIT restricts the total number of rows returned—perfect for creating “Top 10” lists.
Practical Example 1: Sorting Highest to Lowest
Let’s take our previous Sales team query and sort them by their salary (Col D) from highest to lowest.
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:F, "SELECT A, D WHERE B = 'Sales' ORDER BY D DESC", 1) |
DESC stands for Descending (highest to lowest). Use ASC for Ascending (A-Z or lowest to highest). |
Practical Example 2: Getting the Top 3 Results
If you are building a dashboard and only have space to show the Top 3 highest-paid salespeople, you can use the LIMIT clause.
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:F, "SELECT A, D WHERE B = 'Sales' ORDER BY D DESC LIMIT 3", 1) |
Filters for Sales, sorts by highest salary, and literally chops the result off after the first 3 rows. |
3. Summarizing: GROUP BY & Aggregations
This is where QUERY replaces the need for a traditional Pivot Table. You can mathematically summarize data using functions like SUM(), AVG(), COUNT(), and MAX() by grouping rows together.
Practical Example 1: Summing Sales by Category
You have a list of individual transactions. You want to see the total revenue (Col C) generated by each unique product category (Col A).
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:D, "SELECT A, SUM(C) GROUP BY A", 1) |
Finds every unique category in Column A and adds up all corresponding revenue in Column C. |
Practical Example 2: Counting Items by Region
You want to know exactly how many customers (Col B) are located in each specific state (Col D).
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:E, "SELECT D, COUNT(B) GROUP BY D", 1) |
Counts the number of non-empty customer entries grouped by the State column. |
4. Transforming: The PIVOT Clause
The PIVOT clause is a Google Sheets exclusive (it doesn’t exist in standard SQL). It allows you to take row values and turn them into new column headers, creating a dynamic 2D matrix directly via a formula.
Practical Example 1: Simple Matrix Pivot
You want a table showing total revenue (Col C), where rows are Product Categories (Col A) and columns are the Months (Col B).
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:D, "SELECT A, SUM(C) GROUP BY A PIVOT B", 1) |
Creates a summary grid. Each unique Month in Column B becomes a brand new header across the top of your sheet. |
Practical Example 2: Filtering Before Pivoting
Pivot clauses must go at the end of your query. Here is how you filter out blank data before pivoting.
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:D, "SELECT A, COUNT(C) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1) |
Ensures that completely blank rows aren’t calculated as an “empty” category in your final pivot table. |
5. Advanced Filtering: Dates & LIKE
Working with text strings is easy, but querying dates and partial text matches requires a specific syntax to prevent #VALUE! errors.
Practical Example 1: Filtering by a Specific Date
Google Sheets requires the word date followed by the date strictly formatted in 'yyyy-mm-dd'.
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:C, "SELECT * WHERE A >= date '2026-01-01'", 1) |
Returns all columns (using the * wildcard) but only for records starting from January 1st, 2026 onwards. |
Practical Example 2: Partial Text Matching using LIKE
If you are searching for a specific word inside a longer string (e.g., finding any product containing the word “Pro”).
| Formula | What It Does | |
|---|---|---|
| 1 | =QUERY(A:D, "SELECT A, B WHERE A LIKE '%Pro%'", 1) |
The % symbol acts as a wildcard. This will match “iPad Pro”, “Proxima”, or “MacBook Pro M2”. Use CONTAINS as an alternative if you don’t want to use wildcards. |
Conclusion
Learning how to use the QUERY function in Google Sheets fundamentally changes how you approach data. While the syntax might feel a little intimidating at first, practicing these 5 core clauses (SELECT, WHERE, ORDER BY, GROUP BY, and PIVOT) will grant you the ability to build automated, highly sophisticated reports without writing complex, nested array formulas.
Start small by pulling basic columns, and slowly layer in conditions and aggregations as you get more comfortable. Want more tips on conquering your spreadsheets? Check out our other advanced guides at Learn Excel and Sheets!
