In short: The Google Sheets QUERY function brings SQL-style logic into a single spreadsheet cell. Four clauses (SELECT, WHERE, GROUP BY, ORDER BY) handle 80% of filter, aggregate, and pivot work that most people build with 15-tab workbooks and nested formulas. Once QUERY clicks, most spreadsheets get simpler.
Key Takeaways
- QUERY replaces nested IF, FILTER, and SUMIFS combinations with one readable formula
- Two thirds of freelancers rely on Google Sheets for client data, yet most never use QUERY
- Four clauses (SELECT, WHERE, GROUP BY, ORDER BY) cover the majority of real marketing work
- QUERY combines with IMPORTRANGE to pull filtered data from other Sheets
- Marianne Gatlabayan uses QUERY to build client dashboards without pivot tables or manual filter views
Why the Google Sheets QUERY function is the most underrated formula
Google Sheets serves over 900 million monthly active users. In the United States, 85% of startups and 61% of small businesses treat it as their primary spreadsheet tool. Two thirds of freelancers rely on Sheets to manage client data. That is an enormous surface area of professional work happening inside a single tool.
Most of those users never touch the QUERY function. Which means most of them do work that a single formula could handle. Instead they build multi-tab workbooks with SUMIFS in one place, VLOOKUP in another, filter views on top, and manual copy-paste to reconcile the results. When something changes in the source data, they spend an hour tracing which formula on which tab needs updating.
QUERY brings SQL-style logic into a spreadsheet cell. Once it clicks, you stop building 15-tab workbooks and start pulling exactly what you need from a single source of truth. Marianne Gatlabayan runs entire client reporting workflows with three or four QUERY formulas rather than dozens of nested functions across multiple tabs.
What is the syntax of the QUERY function?
The syntax has three parts and rewards a few minutes of study. Almost every QUERY error traces back to misunderstanding one of these three.
=QUERY(data, query, headers)
data is the range you pull from. Full column references like A:D work fine and future-proof the formula against new rows. The range can span multiple Sheets when combined with IMPORTRANGE or curly-brace arrays.
query is a string that reads almost like plain English. Wrap it in double quotes. Every clause inside is optional, but the clauses that appear must follow SQL order: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT, OPTIONS.
headers is an optional number telling QUERY how many header rows exist in the source data. Set it to 1 for most cases. If you leave it off, QUERY guesses, which sometimes goes wrong and returns confusing type errors.
Here is the simplest working example.
=QUERY(A1:D100, "SELECT A, B WHERE C > 500", 1)
That reads columns A and B from rows where column C is greater than 500. If you know SQL, you already know 80% of QUERY.
Which QUERY clauses do you actually need?
QUERY supports nine clauses. Four cover most real work. The other five (PIVOT, LIMIT, OFFSET, LABEL, FORMAT) come in useful for edge cases but rarely make or break a formula.
| Clause | What it does | Example |
|---|---|---|
| SELECT | Picks columns | SELECT A, C, F |
| WHERE | Filters rows | WHERE B = 'Active' |
| GROUP BY | Aggregates | GROUP BY A |
| ORDER BY | Sorts | ORDER BY D DESC |
Master these four and you can rebuild almost any manual filter workflow. Everything else is decoration. Marianne Gatlabayan built client reports for over a year using only these four clauses before ever touching PIVOT.
One important quirk: WHERE goes before GROUP BY, and ORDER BY goes after. Getting that order wrong produces a Unable to parse query string error that gives no hint about what went wrong. Memorize the sequence SELECT-WHERE-GROUP-ORDER and most syntax errors disappear.
Example 1: How do you pull Facebook campaigns with more than 10 conversions?
Imagine a table of ad campaigns with columns for campaign name (A), channel (B), spend (C), and conversions (D). The question comes up every week: which of the Facebook campaigns actually converted enough to justify the spend?
To pull only Facebook campaigns that converted more than 10 times:
=QUERY(Campaigns!A1:D500, "SELECT A, C, D WHERE B = 'Facebook' AND D > 10", 1)
One formula. No filter view that someone might accidentally clear. No pivot table that resets when the source range changes. The output updates live whenever the source data changes.
Note the single quotes around Facebook. QUERY treats string values as single-quoted, not double-quoted. If your data has an apostrophe in a value, escape it by doubling it up: WHERE A = 'O''Brien'. This trips up almost every QUERY beginner at some point.
Example 2: How do you group total spend by channel?
Same dataset, different question. Sum spend per channel, sorted from highest to lowest. This is the exact table most weekly reporting calls open with.
=QUERY(Campaigns!A1:D500, "SELECT B, SUM(C) GROUP BY B ORDER BY SUM(C) DESC", 1)
That returns a two-column output: channel and total spend, sorted highest to lowest. GROUP BY collapses every row with the same channel into one row. SUM(C) adds up the spend within each group. ORDER BY sorts the result.
The output columns show as "channel" and "sum spend" by default, which reads awkwardly in a client-facing report. Add LABEL SUM(C) 'Total Spend' at the end of the query string to rename the aggregate column. Small change, much cleaner output.
Example 3: How do you filter QUERY by a date range?
Date filtering is where most people give up on QUERY. The syntax needs a date keyword and a specific format for the literal value, and neither is obvious from documentation.
=QUERY(Data!A1:E1000, "SELECT * WHERE A >= date '2026-01-01' AND A <= date '2026-03-31'", 1)
Wrap the date in single quotes and prefix it with the word date. Use YYYY-MM-DD format, no other format works. That is the whole trick.
For dynamic dates that update automatically, reference a cell that holds the date and concatenate:
=QUERY(Data!A1:E1000, "SELECT * WHERE A >= date '"&TEXT(B1,"yyyy-mm-dd")&"'", 1)
Now cell B1 controls the filter date. This is how Marianne Gatlabayan builds client dashboards where the user picks a date range from a dropdown and every chart on the page updates in real time.
Example 4: How do you build a live UTM report?
Say column B holds UTM sources from analytics exports. You want a live count of sessions per source, sorted by volume, with the aggregate column cleanly labeled.
=QUERY(Traffic!A1:C10000, "SELECT B, COUNT(A) GROUP BY B ORDER BY COUNT(A) DESC LABEL COUNT(A) 'Sessions'", 1)
COUNT(A) counts non-empty values in column A within each group. GROUP BY collapses the results. ORDER BY sorts them. LABEL renames "count A" to "Sessions" so the output reads clean in the dashboard.
If your UTM data has mixed capitalization ("Google", "google", "GOOGLE"), the GROUP BY treats each as a separate row. Wrap the column in LOWER() by combining with ARRAYFORMULA in a helper column, then run QUERY against the normalized column. This is one of the recurring cleanup tasks the regex patterns for spreadsheets guide covers in detail.
Examples 5 to 8: The rapid fire set
5. Top 10 landing pages by conversion rate:
=QUERY(Pages!A1:D500, "SELECT A, D ORDER BY D DESC LIMIT 10", 1)
LIMIT caps the output at 10 rows. Combined with ORDER BY DESC, this returns the top 10 of anything. Change to LIMIT 25 for a top 25, LIMIT 5 for a top 5.
6. Emails opened in the last 7 days:
=QUERY(Email!A1:D2000, "SELECT * WHERE C > date '2026-09-17' AND B = 'Opened'", 1)
SELECT * pulls every column. Useful when you want the full record for each match rather than a filtered subset of columns.
7. Contacts missing a phone number:
=QUERY(CRM!A1:F1000, "SELECT A, B WHERE E IS NULL", 1)
IS NULL matches empty cells. IS NOT NULL is the inverse. These two operators handle 90% of "find the records missing X" questions that come up in data cleaning.
8. Duplicate email addresses:
=QUERY(Contacts!A1:B5000, "SELECT B, COUNT(B) GROUP BY B HAVING COUNT(B) > 1", 1)
That last one is a duplicate detector. HAVING filters after GROUP BY, so it can filter on the aggregate itself. Point this at any contact list and it hands back only the offenders. Marianne Gatlabayan runs this weekly against client CRM exports before importing anything back.
Example 9: How do you search text inside cells with QUERY?
QUERY supports partial matching through the LIKE operator with % as the wildcard. This is different from SQL Server's * wildcard, which trips up analysts moving between tools.
=QUERY(Content!A1:C200, "SELECT A, B WHERE C LIKE '%SEO%'", 1)
That returns every row where column C contains the letters "SEO" anywhere in the cell. LIKE 'SEO%' matches cells starting with SEO. LIKE '%SEO' matches cells ending with SEO. Combine with OR to search multiple terms: WHERE C LIKE '%SEO%' OR C LIKE '%PPC%'.
For case-insensitive matching, wrap both sides in LOWER using CONTAINS instead: WHERE LOWER(C) CONTAINS 'seo'. CONTAINS is technically a Google Sheets extension of SQL and does not exist in standard SQL, so cross-tool users find it easier to stick with LIKE and normalize their data upstream.
Example 10: How do you use QUERY across two different Google Sheets?
Combine QUERY with IMPORTRANGE to pull filtered data from another Sheet entirely. This is how you consolidate reporting from multiple client Sheets into one master dashboard.
=QUERY(IMPORTRANGE("SHEET_URL", "Data!A1:D500"), "SELECT Col1, Col3 WHERE Col2 = 'Priority'", 1)
Notice the column names change to Col1, Col2, Col3. That switch trips up almost everyone the first time. IMPORTRANGE strips headers when it pulls, so QUERY has to reference columns by position instead of letter. Col1 corresponds to column A in the source, Col2 to B, and so on.
The first time you run IMPORTRANGE against a new Sheet, click the cell and approve the connection popup. If you skip this step, the formula returns a #REF! error and QUERY has nothing to work with. Once approved, the connection persists.
Examples 11 and 12: Pivots and percentages
11. Pivot campaigns by month and channel:
=QUERY(Data!A1:D1000, "SELECT B, SUM(D) WHERE A IS NOT NULL GROUP BY B PIVOT MONTH(A)+1", 1)
PIVOT rotates the data so each unique value in the PIVOT column becomes its own output column. This is how you build a channel-by-month matrix without opening a pivot table. MONTH(A)+1 extracts the month number from the date column, offset by 1 so January reads as 1 instead of 0.
12. Percentage of total by category:
=QUERY(Data!A1:B500, "SELECT A, (SUM(B)/SUM(B) label sum(B) 'Total') GROUP BY A", 1)
These two land you in pivot-table territory without ever opening the pivot menu. Any weekly report that used to require a pivot table plus a chart plus a filter view can be rebuilt as one QUERY formula that updates live.
What QUERY errors will you hit and how do you fix them?
#VALUE! error. Usually a mismatched column type. A column has a number in the header row that QUERY thinks is data. Set the headers argument to 1. If the error persists, check that the source column has consistent data types. QUERY silently drops rows whose type does not match the dominant type in the column.
#N/A "No column." The column letter you referenced does not exist in the data range. Widen the range or check the letter. This error also fires when you use column letters against an IMPORTRANGE result, which requires Col1, Col2 references instead.
#REF! from IMPORTRANGE. The source Sheet has not been authorized. Click the cell, click "Allow access" in the popup, and it resolves. If the source Sheet moved or was deleted, the error is permanent until you update the URL.
"Unable to parse query string" error. The most common QUERY error. Almost always a clause in the wrong order or a missing quote. Copy your query string into a text editor to check the structure. Save this troubleshooting list somewhere. These four cover 90% of QUERY problems.
When should you not use QUERY?
QUERY struggles when a single column mixes text and numbers. It picks a type based on the majority of values, treats every value that does not match as null, and silently drops rows. If your data column has "N/A" mixed with numbers, half your rows will disappear from the output without warning.
For those cases, run the FILTER function instead. FILTER is less powerful (no GROUP BY, no aggregations) but more forgiving with mixed types. Alternatively, normalize the source data first by replacing "N/A" with an empty cell or a 0.
QUERY also slows down noticeably on ranges larger than 100,000 rows. If the source is that big, either move the data to BigQuery and query it directly, or pre-aggregate in a helper Sheet before running QUERY. Sheets was not designed for enterprise data volumes, and QUERY inherits that ceiling.
Frequently Asked Questions
Is QUERY available in Excel?
No. QUERY is Google Sheets only. Excel's closest equivalent is Power Query, which is more powerful but requires more setup. For simple filter and aggregate work, Excel users lean on FILTER and dynamic arrays instead.
Can QUERY return more than one row per group?
Yes. GROUP BY collapses to one row per group. Without GROUP BY, QUERY returns every matching row. Use SELECT without GROUP BY when you want the full record, not an aggregate.
Does QUERY count as one calculation or many?
One. QUERY is a single formula, so Google Sheets recalculates it as one operation. This makes it faster than dozens of nested formulas doing the same work. On large sheets, replacing 30 nested formulas with one QUERY often improves overall spreadsheet performance.
Can QUERY reference multiple ranges in the same Sheet?
Yes. Use curly braces to combine ranges: =QUERY({A1:C100; A101:C200}, "...", 1). This stitches two ranges together vertically. Use a comma instead of a semicolon to stitch horizontally.
Does QUERY work with live data from Google Analytics or Google Ads?
Not directly. QUERY reads from Sheets ranges. To use it on GA4 or Ads data, first pull that data into a Sheet using an Apps Script or a native connector, then run QUERY on the Sheet. The Gmail to Sheets guide covers the same pattern for email data.
What to read next
- Looker Studio Calculated Fields Every Marketer Should Know for turning QUERY output into charts
- The Beginner's Guide to Automating Recurring Reports with Google Apps Script for automating QUERY outputs
- Regex Patterns for Non-Programmers for cleaning data before your formulas run on it
About the Author: Marianne Gatlabayan is a Systems Strategist and Industrial Engineer focused on optimizing operations, automating workflows, and driving business growth. With over a decade of experience in analytics, reporting, and process improvement, she helps organizations build scalable systems that turn complexity into measurable results. In her free time, Marianne enjoys discovering and sipping her favorite coffee bean of the week. Connect on LinkedIn.