Mastering the Google Sheets QUERY Function: Complete Syntax, Examples & Formulas

Introduction

The QUERY function is without question the single most versatile tool in Google Sheets. Combining filtering, sorting, column selection, arithmetic aggregation, and pivoting into a single concise formula, it replaces cumbersome combinations of VLOOKUP, FILTER, UNIQUE, and pivot tables. Built on Google Visualization API Query Language, mastering QUERY transforms raw spreadsheets into dynamic analytical dashboards.

Core Syntax Structure

The fundamental syntax of the QUERY function is straightforward:

=QUERY(data_range, "query_string", [headers])

• data_range: The dataset to query (e.g., A1:G1000).
• query_string: The SQL-like instruction enclosed in quotation marks.
• headers: Optional integer specifying how many header rows reside atop your range. Always declare 1 explicitly to prevent query errors.

Clause Execution Order

Clauses must follow an exact chronological hierarchy. Violating this sequence causes immediate #VALUE! parse errors:

1. SELECT (which columns to return)
2. WHERE (row condition filters)
3. GROUP BY (aggregation dimensions)
4. PIVOT (transform row values into columns)
5. ORDER BY (sorting direction)
6. LIMIT (maximum number of records)
7. OFFSET (skip initial rows)
8. LABEL (rename column headers)
9. FORMAT (number, currency, and date formatting)

Practical Pattern 1: Selecting & Filtering Data

To extract specific customer columns where order revenue exceeds $500 in the North region:

=QUERY(SalesData!A1:G5000, 
  "SELECT B, D, F 
   WHERE C = 'North' AND F > 500 
   ORDER BY F DESC", 1)

Practical Pattern 2: Text Matching with CONTAINS & STARTS WITH

Search operations in QUERY are case-sensitive by default. Use lower() or boolean operators for resilient searches:

=QUERY(Inventory!A1:F1000, 
  "SELECT A, B, D 
   WHERE lower(B) CONTAINS 'wireless' OR lower(B) STARTS WITH 'bt-'", 1)

Practical Pattern 3: Date Filtering Essentials

Filtering dates in QUERY requires the strict date 'yyyy-mm-dd' literal format:

=QUERY(Transactions!A1:H2000, 
  "SELECT A, C, E 
   WHERE D >= date '" & TEXT(DATE(2026,1,1), "yyyy-mm-dd") & "'", 1)

Practical Pattern 4: Grouping, Aggregation & Pivoting

To generate a monthly regional revenue breakdown directly inside your sheet:

=QUERY(Orders!A1:M10000, 
  "SELECT B, SUM(G), AVG(G) 
   WHERE G IS NOT NULL 
   GROUP BY B 
   PIVOT E 
   LABEL SUM(G) 'Total Revenue', AVG(G) 'Average Order'", 1)

Troubleshooting Common QUERY Errors

• Mixed Data Types in a Single Column: The QUERY engine inspects the first several rows of a column to determine its data type (text vs number). If a column contains mostly numbers with occasional text comments, the text cells will return blank. Standardize data types using TO_TEXT() or VALUE() before querying.
• Array Inputs (Col1, Col2 notation): When querying arrays created with curly braces {} or IMPORTRANGE(), refer to columns using Col1, Col2 instead of letter labels A, B.