Introduction
The QUERY function allows you to transform, filter, and analyze data using a single string of text. It acts like SQL inside your spreadsheet.
To demonstrate these examples, we will use the following Sales Data Table (Range: A1:E11).
| Row | Date (A) | Rep (B) | Region (C) | Units (D) | Total (E) |
|---|---|---|---|---|---|
| 1 | Date | Rep | Region | Units | Total |
| 2 | 2026-01-05 | Sarah | North | 10 | 500 |
| 3 | 2026-01-06 | Mike | West | 5 | 250 |
| 4 | 2026-01-07 | Sarah | North | 8 | 400 |
| 5 | 2026-01-08 | Elena | East | 12 | 1200 |
| 6 | 2026-01-09 | Mike | West | 2 | 100 |
| 7 | 2026-01-10 | Elena | East | 15 | 1500 |
| 8 | 2026-01-11 | Sarah | North | 4 | 200 |
| 9 | 2026-01-12 | Kevin | South | 20 | 2000 |
| 10 | 2026-01-13 | Kevin | South | 10 | 1000 |
| 11 | 2026-01-14 | Elena | East | 3 | 300 |
1. Basic Selection & Filtering
Basic Selection (All Data)
Fetches every column and every row.
=QUERY(A1:E11, "SELECT *", 1)
Specific Columns
Only shows the Rep, Region, and Total columns.
=QUERY(A1:E11, "SELECT B, C, E", 1)
Basic Filtering (Numbers)
Shows only sales where the Total is $1,000 or more.
=QUERY(A1:E11, "SELECT * WHERE E >= 1000", 1)
Text Filtering (String)
Shows only the sales from the 'North' region. Note the single quotes around text.
=QUERY(A1:E11, "SELECT * WHERE C = 'North'", 1)
2. Logic and Sorting
Multiple Conditions (AND)
Filters for Sarah's sales that are also over $400.
=QUERY(A1:E11, "SELECT * WHERE B = 'Sarah' AND E > 400", 1)
Sorting Data (ORDER BY)
Shows all data, but sorts it by Total from highest to lowest.
=QUERY(A1:E11, "SELECT * ORDER BY E DESC", 1)
3. Aggregation and Analysis
Aggregation (Grouping)
Calculates the total sum of sales for each region.
=QUERY(A1:E11, "SELECT C, SUM(E) GROUP BY C", 1)
Multi-Function Aggregation
Shows Region, the average units sold, and the number of transactions per region.
=QUERY(A1:E11, "SELECT C, AVG(D), COUNT(A) GROUP BY C", 1)
Custom Labeling
Renames the system-generated header (like "sum Total") to something cleaner.
=QUERY(A1:E11, "SELECT C, SUM(E) GROUP BY C LABEL SUM(E) 'Grand Total'", 1)
4. Advanced Techniques
Limiting Results
Shows only the top 3 largest transactions.
=QUERY(A1:E11, "SELECT * ORDER BY E DESC LIMIT 3", 1)
Filtering Out Nulls
Ensures that empty rows in your range don't break the query or show up as "blank."
=QUERY(A1:E11, "SELECT * WHERE A IS NOT NULL", 1)
Dynamic Cell Reference
Reads a region name from cell H1 (e.g., if you type "West" in H1, the list updates).
=QUERY(A1:E11, "SELECT * WHERE C = '"&H1&"'", 1)
Pro Tip
Remember that A, B, C refer to the position in your range. If your range was B1:Z100, then SELECT A would actually pull data from Column B of the spreadsheet!