When analyzing large datasets in Google Sheets, chaining together multiple FILTER, SORT, and SUMIFS functions often results in lengthy, convoluted formulas that degrade spreadsheet calculation speeds. The QUERY function solves this by bringing the full power of Structured Query Language (SQL) directly into spreadsheet cells. Using Google Visualization API query syntax, users can filter rows, compute group aggregations, pivot matrices, and reformat datasets within a single concise formula.
Understanding the QUERY Function Syntax and Data Structure
The basic structure of the QUERY function is:
=QUERY(data, query_string, [headers])- data: The range of cells containing your source table (e.g.,
A1:G1000). Data within each column must be of consistent data type (numeric, text, or date). - query_string: The SQL-like instructions enclosed in double quotation marks (e.g.,
"SELECT A, B WHERE C > 50"). - headers: Optional integer specifying the number of header rows at the top of your dataset (typically
1).
Selecting Specific Columns and Renaming Headers
To extract only pertinent columns and customize output labels:
=QUERY(A1:F500, "SELECT A, C, E, F LABEL A 'Employee Name', F 'Sales Total'", 1)The SELECT clause identifies which columns to display, while LABEL overrides default table headers without altering source data.
Applying Compound Boolean Logic: WHERE, AND, OR, NOT
The WHERE clause filters records based on conditional criteria. Combine multiple conditions using standard Boolean operators:
1. AND Logic (All conditions must be met):
=QUERY(A1:E200, "SELECT A, B, C WHERE C = 'Marketing' AND D >= 5000", 1)2. OR Logic (Any condition matches):
=QUERY(A1:E200, "SELECT A, B, C WHERE C = 'Marketing' OR C = 'Sales'", 1)3. Grouped Compound Logic with Parentheses: When combining AND and OR clauses, use parentheses to govern evaluation order strictly:
=QUERY(A1:F200, "SELECT A, B, D WHERE (B = 'North' OR B = 'West') AND D > 10000", 1)Filtering Text with LIKE, CONTAINS, and STARTS WITH
For flexible text pattern matching:
WHERE A CONTAINS 'Tech': Matches any row where column A includes the substring “Tech” (case-sensitive).WHERE LOWER(A) CONTAINS 'tech': Converts text to lowercase for case-insensitive matching.WHERE B STARTS WITH 'PROJ-': Matches records beginning with a specific code prefix.WHERE C MATCHES '.*(Alpha|Beta).*': Utilizes full regular expressions for advanced string filtering.
Performing Grouped Aggregations and Sorting
The true power of QUERY lies in generating summary pivot tables directly in formula cells using GROUP BY and ORDER BY:
=QUERY(A1:E500, "SELECT B, COUNT(A), SUM(E), AVG(E) WHERE E IS NOT NULL GROUP BY B ORDER BY SUM(E) DESC", 1)This single statement groups data by region (Column B), tallies headcount, computes total revenue, calculates average transaction size, and sorts output descending by total sales.
Referencing External Cell Values Inside Query Strings
To build dynamic reports where filters react to user dropdowns or input cells (e.g., cell H1):
For Text Criteria: Concatenate using single quotes and double quotes:
=QUERY(A1:D200, "SELECT A, B, D WHERE B = '" & H1 & "'", 1)For Numeric Criteria: Omit single quotes:
=QUERY(A1:D200, "SELECT A, B, D WHERE D > " & H2, 1)