Google Sheets QUERY Function: Complete Guide for Excel Users
SheetHub8 min
You know Excel. You have been using VLOOKUP, SUMIFS, and Pivot Tables for years. Then someone puts you on a Google Sheets project, and you see a function called QUERY. The syntax looks like nothing you have seen before.
SQL inside a spreadsheet? It feels alien at first. But here is the thing — QUERY is one of the most powerful functions in Google Sheets, and once you understand the basics, you will wonder how you lived without it.
The hardest part about learning QUERY is unlearning what you know. In Excel, you split logic across multiple cells. With QUERY, you write one formula that does the work of a dozen.
Here is a comparison that clicked for me:
That last column is the same query language used by databases. If you ever work with SQL, you have a head start.
The magic is in the
This is the simplest query. It selects which columns to show and optionally renames them.
Show all columns:
Show specific columns by letter:
Column references use spreadsheet column letters (A, B, C...) or
Rename columns with LABEL:
Useful for generating clean reports without manually editing headers.
WHERE filters your data, similar to how you would use FILTER in Excel or a WHERE clause in SQL.
Numeric conditions:
Text conditions — use single quotes:
Date conditions — use
Multiple conditions with AND/OR:
NULL checks:
This is where QUERY really shines. Instead of writing SUMIFS for every category, you can do it in one line.
Total sales by region:
This replaces: a list of
Returns: region, total sales, average sale, number of orders.
With ORDER BY to sort results:
Best-selling region at the top.
PIVOT is the QUERY equivalent of columns in an Excel Pivot Table. It creates one column per unique value.
Sales by region, broken down by product category (columns):
Where:
QUERY inside other functions. QUERY output can be used as input to other functions:
This sums column C for "East" rows — like SUMIF but with QUERY's flexible filtering.
Use cell references in your query. Concatenate cell values into the query string:
Now changing F1 changes the criteria without editing the formula.
IMPORT the query for dashboard-style reports. QUERY combined with IMPORTRANGE pulls data from other sheets:
Format dates properly. QUERY expects dates in
Mixing data types in a column. QUERY expects consistent types. If a column has text in one row and numbers in another, QUERY either ignores the mixed values or errors. Clean your data first.
Forgetting the header argument. The third argument (headers) defaults to 1. If your data has no headers, use 0. If you have 2 header rows, use 2. Getting this wrong shifts your data by the wrong number of rows.
Using
Is QUERY faster than multiple SUMIF formulas?
For aggregated reports, yes. QUERY processes data in one pass, while multiple SUMIFs scan the data multiple times. On a dataset with 10,000 rows and 50 categories, QUERY can be 5-10x faster.
Can QUERY replace Pivot Tables?
For many cases, yes. QUERY gives you more control over the output format and can be updated instantly by changing a cell reference. Pivot Tables still win for interactive exploration (drag-and-drop fields).
Does QUERY work in Excel?
No. QUERY is exclusive to Google Sheets. Excel has Power Query, which is similar but uses a different language (M-language). The concepts transfer, but the syntax does not.
Why does my QUERY return a #VALUE! error?
Most likely a data type mismatch. Check that all values in the column are the same type. Also check that your query string syntax is valid — missing quotes or commas are the most common cause.
Can I use QUERY with multiple sheets?
Yes. Use
=QUERY(A1:D100, "SELECT B, SUM(D) WHERE C > 100 GROUP BY B LABEL SUM(D) ''")Excel Users, This Is for You
QUERY is not a replacement for everything you know. It is a new tool for tasks that are awkward or impossible with standard formulas. Think of it as a built-in database query engine inside your spreadsheet.
Why This Matters for Excel Refugees
| Task | Excel Approach | Google Sheets QUERY |
|---|---|---|
| Filter + sum by category | SUMIFS or Pivot Table | SELECT category, SUM(amount) GROUP BY category |
| Filter data to another sheet | Advanced Filter or FILTER | SELECT * WHERE status = 'Active' |
| Combine multiple conditions | Nested IFs or SUMPRODUCT | WHERE condition1 AND condition2 |
| Sort dynamically | SORT or manual sorting | ORDER BY amount DESC |
The Anatomy of a QUERY
=QUERY(data, query, [headers])| Argument | Required | Description |
|---|---|---|
data | Yes | The cell range to query (like a table in Excel) |
query | Yes | The query string written in Google Visualization API Query Language |
headers | No | The number of header rows (default is 1) |
query argument. Here is a quick reference:
| Keyword | What It Does | Excel Equivalent |
|---|---|---|
SELECT | Picks columns | Choosing which columns to show |
WHERE | Filters rows | Filter or IF condition |
GROUP BY | Aggregates by category | Pivot Table rows |
ORDER BY | Sorts results | Sort A-Z or Z-A |
LABEL | Renames columns | Custom headers |
LIMIT | Max rows returned | — |
OFFSET | Skip rows | — |
PIVOT | Cross-tabulation | Pivot Table columns |
SELECT — Picking Columns
=QUERY(A1:D100, "SELECT *", 1)=QUERY(A1:D100, "SELECT A, C, D", 1)Col1, Col2 notation:
=QUERY(A1:D100, "SELECT Col1, Col3", 1)Why Col1 Instead of A?
When you use
Col1, Col2 notation, the numbering is relative to the data range, not the spreadsheet. If your data starts in column D, Col1 refers to D, Col2 to E, and so on. This makes formulas more portable.=QUERY(A1:D100, "SELECT A, B, C LABEL A 'Product', B 'Category', C 'Price'", 1)WHERE — Filtering Rows
=QUERY(A1:D100, "SELECT A, B, C WHERE C > 500", 1)=QUERY(A1:D100, "SELECT A, B, C WHERE B = 'East'", 1)date keyword:
=QUERY(A1:D100, "SELECT A, B, C WHERE D > date '2026-01-01'", 1)=QUERY(A1:D100, "SELECT A, B, C WHERE B = 'East' AND C > 500", 1)=QUERY(A1:D100, "SELECT A, B, C WHERE B = 'East' OR B = 'West'", 1)=QUERY(A1:D100, "SELECT A, B, C WHERE C IS NOT NULL", 1)GROUP BY — Aggregation
=QUERY(A1:D100, "SELECT B, SUM(C) GROUP BY B", 1)=SUMIF(B:B, "East", C:C), =SUMIF(B:B, "West", C:C) — one per region. With QUERY, you get all regions automatically.
Multiple aggregations:
=QUERY(A1:D100, "SELECT B, SUM(C), AVG(C), COUNT(C) GROUP BY B", 1)=QUERY(A1:D100, "SELECT B, SUM(C) GROUP BY B ORDER BY SUM(C) DESC", 1)GROUP BY Rule
Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregation function (SUM, AVG, COUNT, MAX, MIN). If you SELECT a column without grouping or aggregating it, QUERY throws an error.
PIVOT — Cross-Tabulation
=QUERY(A1:D100, "SELECT B, SUM(D) GROUP BY B PIVOT C", 1)- A = Product ID
- B = Region
- C = Category
- D = Sales Amount
Pro Tips
=SUM(QUERY(A1:D100, "SELECT C WHERE B = 'East'", 0))=QUERY(A1:D100, "SELECT A, B, C WHERE B = '"&F1&"'", 1)=QUERY(IMPORTRANGE("sheet_url", "Sheet1!A1:D100"), "SELECT * WHERE C > 100", 1)yyyy-mm-dd format. If your dates look different, use TEXT to reformat:
=QUERY(A1:D100, "SELECT A, B WHERE D > date '"&TEXT(F1, "yyyy-mm-dd")&"'", 1)Common Mistakes
SELECT * on large datasets. It works, but it returns every column — including intermediate calculation columns you may not need. Be explicit: SELECT A, B, C instead of SELECT *.
SQL syntax errors. QUERY uses a custom language similar to SQL but not identical. Common gotchas:
- String values need single quotes, not double:
WHERE B = 'East'notWHERE B = "East" - Column identifiers use letters or Col1/Col2, not names
- The entire query must be a single string — use
&to concatenate dynamic values
Related Functions
- VLOOKUP vs XLOOKUP — Learn how lookups work in Excel vs Google Sheets
- SUMIF vs SUMIFS vs SUMPRODUCT — Conditional sums in both platforms
- INDEX-MATCH Guide — The other essential multi-condition tool
- GROUPBY Function — Excel's version of grouped aggregation
FAQ
{} to combine ranges: =QUERY({Sheet1!A1:D100; Sheet2!A1:D100}, "SELECT * WHERE Col3 IS NOT NULL", 1)
How do I sort by a column in descending order?
Use ORDER BY ColX DESC. For ascending, just ORDER BY ColX or add ASC.Topics
Topics in this article
Explore related topics and continue reading similar content.
Share this article
Discussion
Preparing the comments area...