Master Google Sheets QUERY Function
Unlock SQL-like data manipulation in Google Sheets. Learn QUERY syntax to filter, sort, pivot, and aggregate data with powerful formulas.
The QUERY function is the most powerful data manipulation tool in Google Sheets—but most users never learn it. If you know SQL, QUERY lets you write familiar SELECT, WHERE, GROUP BY, and ORDER BY statements directly in spreadsheet formulas. If you do not know SQL, this guide will teach you everything you need. QUERY eliminates the need for complex nested formulas, pivot tables, and manual filtering. A single QUERY formula can replace 10+ traditional formulas, making your sheets faster, cleaner, and easier to maintain. This is the skill that separates beginner spreadsheet users from professional analysts. In this comprehensive guide, you will learn QUERY syntax from basics to advanced techniques. By the end, you will be able to filter millions of rows, create dynamic reports, and build automated dashboards—all with one powerful function.
What You'll Need
- Intermediate knowledge of Google Sheets
- Understanding of basic formulas and cell references
- Dataset with at least 3 columns to practice on (we will provide examples)
Step-by-Step Guide
Understand QUERY Syntax Basics
Learn the fundamental structure of QUERY formulas.
- Basic syntax: =QUERY(data, query, [headers])
- data = the range to query (A1:D100)
- query = SQL-like query in quotes ("SELECT A, B WHERE C > 100")
- headers = optional number of header rows (usually 1)
- Column references: Use letters (A, B, C) or Col1, Col2, Col3
- Example: =QUERY(A1:D100, "SELECT A, B, C", 1)
Pro Tip
Always include header rows in your data range. QUERY works best when column names are in the first row.
SELECT Specific Columns
Choose which columns to display in your results.
- Select all columns: =QUERY(A1:D100, "SELECT *")
- Select specific columns: =QUERY(A1:D100, "SELECT A, C, D")
- Reorder columns: =QUERY(A1:D100, "SELECT D, A, B") puts column D first
- Use Col notation: =QUERY(A1:D100, "SELECT Col1, Col3, Col4")
- Rename columns with LABEL: =QUERY(A1:D100, "SELECT A, B LABEL A 'Name', B 'Revenue'")
Pro Tip
Col notation (Col1, Col2) is better than letters when your data range might change—it adapts automatically.
Filter Data with WHERE Clause
Use WHERE to filter rows based on conditions.
- Basic filter: =QUERY(A1:D100, "SELECT * WHERE B > 1000")
- Text filter: =QUERY(A1:D100, "SELECT * WHERE A = 'Product A'")
- Multiple conditions (AND): =QUERY(A1:D100, "SELECT * WHERE B > 1000 AND C < 5000")
- Multiple conditions (OR): =QUERY(A1:D100, "SELECT * WHERE A = 'Product A' OR A = 'Product B'")
- Text contains: =QUERY(A1:D100, "SELECT * WHERE A CONTAINS 'Manager'")
- Date filters: =QUERY(A1:D100, "SELECT * WHERE B > DATE '2024-01-01'")
Pro Tip
Text values in WHERE clauses must use single quotes inside double quotes: "WHERE A = 'Text'"
Sort Results with ORDER BY
Sort your query results in ascending or descending order.
- Sort ascending: =QUERY(A1:D100, "SELECT * ORDER BY B")
- Sort descending: =QUERY(A1:D100, "SELECT * ORDER BY B DESC")
- Sort by multiple columns: =QUERY(A1:D100, "SELECT * ORDER BY A ASC, B DESC")
- Combine WHERE and ORDER BY: =QUERY(A1:D100, "SELECT * WHERE C > 100 ORDER BY B DESC")
- Sort by column not in SELECT: =QUERY(A1:D100, "SELECT A, B ORDER BY D DESC")
Pro Tip
The order of clauses matters: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT. Follow this sequence or queries fail.
Aggregate Data with GROUP BY
Summarize data by categories like pivot tables.
- Sum by category: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A")
- Count by category: =QUERY(A1:D100, "SELECT A, COUNT(B) GROUP BY A")
- Average by category: =QUERY(A1:D100, "SELECT A, AVG(B) GROUP BY A")
- Multiple aggregations: =QUERY(A1:D100, "SELECT A, SUM(B), AVG(C), COUNT(D) GROUP BY A")
- Group by multiple columns: =QUERY(A1:D100, "SELECT A, B, SUM(C) GROUP BY A, B")
- Add LABEL for clarity: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Total Revenue'")
Pro Tip
When using GROUP BY, every column in SELECT must either be in GROUP BY or be an aggregation (SUM, AVG, COUNT, etc.).
Use Aggregate Functions
Master SUM, COUNT, AVG, MIN, MAX for data analysis.
- SUM: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A") totals numeric values
- COUNT: =QUERY(A1:D100, "SELECT A, COUNT(B) GROUP BY A") counts non-empty cells
- AVG: =QUERY(A1:D100, "SELECT A, AVG(B) GROUP BY A") calculates averages
- MIN/MAX: =QUERY(A1:D100, "SELECT MIN(B), MAX(B)")
- Combine functions: =QUERY(A1:D100, "SELECT A, SUM(B), AVG(C), COUNT(*) GROUP BY A")
Pro Tip
Use COUNT(*) to count all rows including blanks, COUNT(B) only counts non-blank values in column B.
Limit and Format Results
Control how many rows return and format output.
- Limit results: =QUERY(A1:D100, "SELECT * ORDER BY B DESC LIMIT 10") shows top 10
- Skip rows with OFFSET: =QUERY(A1:D100, "SELECT * LIMIT 10 OFFSET 5") skips first 5 rows
- Format numbers: =QUERY(A1:D100, "SELECT A, B FORMAT B '$#,##0.00'")
- Format dates: =QUERY(A1:D100, "SELECT A, B FORMAT B 'yyyy-mm-dd'")
- Format percentages: =QUERY(A1:D100, "SELECT A, B FORMAT B '0.00%'")
Pro Tip
LIMIT is perfect for "Top 10" reports. Combine with ORDER BY DESC to get highest values first.
Advanced Techniques and Best Practices
Use dynamic queries, PIVOT, and query across sheets.
- Dynamic WHERE with cell reference: =QUERY(A1:D100, "SELECT * WHERE B > "&E2) uses value in E2
- Query another sheet: =QUERY(Sheet2!A1:D100, "SELECT * WHERE B > 1000")
- PIVOT data: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A PIVOT C")
- Combine multiple queries with curly braces: ={QUERY(A1:D10, "SELECT *"); QUERY(A20:D30, "SELECT *")}
- Handle errors: =IFERROR(QUERY(A1:D100, "SELECT * WHERE B > 1000"), "No results")
Pro Tip
When building dynamic queries with cell references, be careful with quotes. Text needs: "WHERE A = '"&E2&"'"
Wrapping Up
Frequently Asked Questions
What is the Google Sheets QUERY function?
QUERY is a powerful function that uses SQL-like syntax to filter, sort, and aggregate data in Google Sheets. Syntax: =QUERY(data, query, headers). It supports SELECT, WHERE, GROUP BY, ORDER BY, PIVOT, and LIMIT clauses, making it more flexible than pivot tables or traditional formulas for complex data manipulation.
How do I use WHERE in Google Sheets QUERY?
WHERE filters rows based on conditions. Syntax: =QUERY(A1:D100, "SELECT * WHERE B > 1000"). For text, use single quotes: "WHERE A = 'Product'". Combine conditions with AND/OR: "WHERE B > 1000 AND C < 5000". Use CONTAINS for partial matches: "WHERE A CONTAINS 'Manager'".
How do I group data in QUERY like a pivot table?
Use GROUP BY with aggregate functions. Syntax: =QUERY(A1:D100, "SELECT A, SUM(B) GROUP BY A"). This sums column B for each unique value in column A. Every column in SELECT must be in GROUP BY or be an aggregation (SUM, AVG, COUNT, MIN, MAX). Add LABEL to rename result columns.
What is the difference between QUERY and FILTER in Google Sheets?
FILTER returns rows matching conditions but cannot aggregate, sort within the formula, or select specific columns. QUERY does all of this with SQL syntax. Use FILTER for simple row filtering, QUERY for complex analysis with WHERE, GROUP BY, ORDER BY, and aggregations. QUERY is more powerful but has steeper learning curve.
How do I make QUERY formulas dynamic with cell references?
Use concatenation to insert cell values into query strings: =QUERY(A1:D100, "SELECT * WHERE B > "&E2). For text values, add quotes: =QUERY(A1:D100, "SELECT * WHERE A = '"&E2&"'"). For dates: "WHERE B > DATE '"&TEXT(E2, "yyyy-mm-dd")&"'". Dynamic queries update automatically when referenced cells change.
