QUERY Function Google Sheets
Master the QUERY function with SQL-like syntax to filter, sort, and analyze data in Google Sheets. Learn powerful examples and error solutions.
Excel
Google SheetsGoogle Sheets=QUERY(data, query, [headers])Quick Answer
QUERY function QUERY function performs SQL-like operations on data in Google Sheets using the syntax `=QUERY(data, query, [headers])`. Use it to filter (`WHERE C > 100`), sort (`ORDER BY A DESC`), and aggregate (`SELECT A, SUM(B) GROUP BY A`) data with a single formula, replacing complex nested formulas with readable queries.
=QUERY(data, query, [headers])Practical Examples
Basic Selection and Filtering
Filter sales data for amounts over $500
Text Filtering with CONTAINS
Find all products with 'Premium' in the name
Sorting and Limiting Results
Get top 5 best-selling products
Aggregation with GROUP BY
Sum sales by department
Multiple Conditions with AND/OR
Filter customers who spent over $1000 OR have Premium status
Date Filtering and Comparison
Find transactions from the last 30 days
Advanced: Calculated Columns and Aggregation
Calculate profit margin and group by category
Common Errors and Solutions
The query syntax is incorrect, often due to missing quotes, wrong column references, or invalid operators. Can also occur when WHERE conditions match no rows.
1. Check that your query string is enclosed in double quotes 2. Verify column letters are correct (A, B, C, not 1, 2, 3) 3. Ensure text values in WHERE clauses use single quotes ('Sales') 4. Test with simplified query first (just SELECT *) 5. Check for typos in operators (CONTAINS, not CONTAIN)
Example:
Syntax error in the query string, often from incorrect quote usage, invalid date format, or malformed clauses.
1. Check all quote marks are correct (double quotes for query, single for values) 2. For dates, use format: date 'YYYY-MM-DD' 3. Verify all clauses are spelled correctly 4. Check for unclosed parentheses or quotes 5. Ensure column references exist in data range
Example:
Query references a column letter that doesn't exist in the data range. For example, referencing column D when data only goes to column C.
1. Count the columns in your data range 2. Adjust query to only reference existing columns 3. Expand data range if you need more columns 4. Use SELECT * to see all available columns first
Example:
When using aggregate functions (SUM, AVG, COUNT), all non-aggregated columns must be listed in GROUP BY clause.
1. Identify which columns you're aggregating (SUM, AVG, etc.) 2. List all other columns in GROUP BY 3. Ensure GROUP BY column letters match SELECT clause 4. Can't mix aggregated and non-aggregated columns without GROUP BY
Example:
LABEL clause has incorrect syntax, often missing quotes around label text or wrong column reference.
1. LABEL syntax: LABEL column 'New Name' 2. Label text must be in single quotes 3. Column reference must match SELECT clause 4. For aggregates: LABEL SUM(B) 'Total' 5. Multiple labels: LABEL A 'Name', B 'Count'
Example:
Advanced Tips and Best Practices
Use Column Letters, Not Numbers
Always reference columns by their letter (A, B, C) not by position number. This makes queries more readable and prevents errors when columns are reordered. Column letters are case-insensitive (A and a both work).
Combine QUERY with IMPORTRANGE for Cross-Sheet Analysis
Nest IMPORTRANGE inside QUERY to analyze data from other spreadsheets. This creates powerful cross-document reporting capabilities without manual data copying. Example: =QUERY(IMPORTRANGE("sheet_url", "Sheet1!A:D"), "SELECT Col1, Col2 WHERE Col3 > 100")
Build Queries Incrementally
Start with SELECT *, verify it works, then add WHERE clause, test again, add ORDER BY, test, and so on. This incremental approach helps identify exactly where syntax errors occur and makes debugging much easier.
Performance Consideration for Large Datasets
QUERY functions can slow down with very large datasets (10,000+ rows). Consider filtering data before querying, using LIMIT to restrict results, or splitting into multiple smaller queries. Avoid SELECT * on huge ranges.
Dynamic Queries with Cell References
Make queries dynamic by concatenating cell values into the query string. Use ampersand (&) to insert cell references into WHERE clauses, allowing users to control filter values without editing formulas. Example: =QUERY(A1:C10, "SELECT * WHERE B > " & E1)
Use Named Ranges for Better Maintenance
Instead of hardcoding ranges like A1:D100, create named ranges (Data > Named ranges). Then use =QUERY(SalesData, ...) which is clearer and easier to update if your data structure changes.
Need Help with QUERY Function Google Sheets?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Google Sheets formula:
Related Formulas
Master IMPORTRANGE to pull data between Google Sheets. Learn syntax, permissions, examples, and solutions to common #REF! errors.
Sheets