=QUERY(data_range, "SELECT A, B, SUM(D) WHERE C = 'Closed Won' GROUP BY A, B ORDER BY SUM(D) DESC label SUM(D) 'Total Revenue'", 1) QUERY runs Google Visualization API Query Language (SQL-like) queries on a dataset. It replaces FILTER, SORT, UNIQUE, and Pivot Tables in a single dynamic formula: =QUERY(A1:E100, "SELECT A, SUM(D) WHERE C='Sales' GROUP BY A", 1).
Google Sheets QUERY Function: SQL-Like Power in Spreadsheets
The QUERY function is Google Sheets’ most powerful analytical tool. Using a syntax inspired by SQL, it performs filtering, column reordering, mathematical aggregation, grouping, and pivoting in a single formula.
1. Core Clause Order Reference
Google Sheets QUERY requires clauses to appear in a strict order:
SELECT(Columns to retrieve or aggregate)WHERE(Row filtering rules)GROUP BY(Aggregation grouping)PIVOT(Rotate column values into horizontal headers)ORDER BY(Sorting:ASCorDESC)LIMIT(Cap maximum rows returned)LABEL(Rename output column headers)FORMAT(Format numbers/dates)
2. Practical Business Examples
Example 1: Select Specific Columns and Filter by Status
=QUERY(A1:E100, "SELECT A, C, E WHERE D = 'Active' AND E > 50000 ORDER BY E DESC", 1)Example 2: Group By & Total Sales by Department
=QUERY(A1:E100, "SELECT B, COUNT(A), SUM(E), AVG(E) WHERE B IS NOT NULL GROUP BY B LABEL COUNT(A) 'Headcount', SUM(E) 'Total Payroll', AVG(E) 'Avg Salary'", 1)Example 3: Dynamic Date Filtering
=QUERY(A1:D100, "SELECT A, B, C WHERE A >= date '" & TEXT(TODAY()-30, "yyyy-mm-dd") & "'", 1)Frequently Asked Questions
Why does QUERY ignore some numbers or text in a mixed data column?
QUERY requires columns to have a single consistent data type (majority rule). If a column has 90% numbers and 10% text, QUERY treats text values as null/blank. Format the entire source column consistently.
How do I reference cell values inside a QUERY string?
For text cells: "WHERE A = '" & B1 & "'". For numbers: "WHERE C > " & B2. For dates: "WHERE D >= date '" & TEXT(B3, "yyyy-mm-dd") & "'".