Google Sheets QUERY Function: SQL Power Without SQL

If you’ve ever wished your spreadsheet could speak SQL, the Google Sheets QUERY function is your superpower. In a single formula, you can filter, sort, group, pivot, and label data—no scripts, no add‑ons, and no database required. this guide shows you how to write SQL‑style queries inside Google Sheets with copy‑paste snippets, real‑world patterns, and edge‑case fixes for dates, headers, and multi‑sheet data. By the end, you’ll turn raw tables into living reports that update themselves.

Why the Google Sheets QUERY function changes everything

QUERY combines the power of SQL with the flexibility of a spreadsheet. You write a mini SQL statement as a string—then Sheets returns exactly the rows and columns you want.

• One formula replaces FILTER, SORT, UNIQUE, and pivot logic.
• Readable statements: select, where, group by, order by, label, limit, offset, pivot.
• Works across tabs and even combined ranges—perfect for reports.

QUERY function basics (primary keyword)

Signature: =QUERY(data, query, [headers])

• data: your table, e.g., Sales!A1:G.
• query: SQL‑like string wrapped in quotes.
• headers: number of header rows in data. Use 1 if your first row has headers; otherwise 0.

Column references:

• If headers > 0, you can reference columns by their header names using Col1 style? Not exactly—QUERY always uses Col1, Col2 notation internally. For readability, keep a clear header row and map names to Col numbers once, then comment adjacent to your formula.

=QUERY(Sales!A1:G, "select Col1, Col3, Col7 where Col5 > 100 order by Col7 desc limit 10", 1)

Core patterns you’ll use daily (filter, sort, limit)

Filter and sort your pipeline

=QUERY(Pipeline!A1:F, "select Col1, Col2, Col5 where Col4 = 'Qualified' and Col5 >= 10000 order by Col5 desc", 1)

Tip: Use single quotes for string literals, double quotes for the whole query.

Return top N by metric

=QUERY(Products!A1:G, "select Col1, Col2, Col6 order by Col6 desc limit 20", 1)

Exclude blanks and errors safely

=QUERY(Leads!A1:E, "select * where Col2 is not null and Col3 is not null", 1)

Aggregation, GROUP BY, and LABEL (secondary keyword)

Summarize revenue by sales rep

=QUERY(Sales!A1:G, "select Col3, sum(Col7) where Col5 = 'Closed Won' group by Col3 order by sum(Col7) desc label sum(Col7) 'Revenue'", 1)

Monthly rollups (date bucketing)

Convert raw dates to month buckets in‑query using format.

=QUERY(Sales!A1:G, "select month(Col1)+1, sum(Col7) where Col5 = 'Closed Won' group by month(Col1)+1 label month(Col1)+1 'Month', sum(Col7) 'Revenue'", 1)

Note: month() returns 0–11. Add 1 for human‑friendly 1–12. For display control, wrap the final column with a TEXT function outside QUERY or use visualization formatting downstream.

Average deal size by industry

=QUERY(Accounts!A1:H, "select Col4, avg(Col7) where Col7 is not null group by Col4 order by avg(Col7) desc label avg(Col7) 'Avg Deal'", 1)

PIVOT tables via QUERY

Build a dynamic pivot without the Pivot Table UI.

=QUERY(Orders!A1:H, "select Col3, sum(Col7) where Col2 = '' group by Col3 pivot Col5 label sum(Col7) 'Revenue'", 1)

This creates columns per pivot key (e.g., regions or product lines) with sums by row group.

Text search, wildcards, and matches (LSI keyword)

Case‑insensitive contains

=QUERY(Customers!A1:F, "select * where lower(Col2) contains 'enterprise'", 1)

Regex matching with matches

=QUERY(Emails!A1:B, "select * where Col1 matches '.*@example.(com|io)$'", 1)

Escape backslashes inside the string. matches uses RE2‑style regex.

Dates and numbers that don’t misbehave

• Literal dates: where Col1 >= date '-01-01'
• Between: where Col1 >= date '-01-01' and Col1 < date '-01-01'
• Coerce numbers when text‑stored: pair with VALUE() in helper columns, or clean upstream.

=QUERY(Events!A1:F, "select Col2, count(Col2) where Col1 >= date '-01-01' group by Col2", 1)

Multi‑sheet analytics with array literals

You can union ranges before querying. Stack identical schemas vertically with ; or combine columns horizontally with ,.

/* Stack monthly exports and analyze in one go */
=QUERY( {
Jan!A1:G;
Feb!A1:G;
Mar!A1:G
}
, "select Col3, sum(Col7) group by Col3 order by sum(Col7) desc", 1)

Formatting, labels, and user‑friendly output

Use label to rename computed columns and format to change display.

=QUERY(Sales!A1:G, "select Col3, sum(Col7) group by Col3 label Col3 'Rep', sum(Col7) 'Revenue' format sum(Col7) '$#,##0'", 1)

Practical applications and templates

1) Live KPI summary

=QUERY(Activity!A1:F, "select Col2, count(Col2) where Col1 >= date '-11-01' group by Col2 label count(Col2) 'Events'", 1)

2) Clean product catalog

=QUERY(Catalog!A1:H, "select Col1, Col2, Col6 where Col6 is not null and Col7 = 'Active' order by Col2", 1)

3) Revenue leaderboard (rolling 90 days)

=LET( start, TODAY()-90, data, Sales!A1:G, QUERY(data, "select Col3, sum(Col7) " & "where Col1 >= date '" & TEXT(start, "yyyy-mm-dd") & "' " & "group by Col3 order by sum(Col7) desc label sum(Col7) 'Revenue'", 1)
)

Expert insights and performance tips

• Keep ranges tight: Limit A1:Z to only needed columns to reduce recalculation cost.
• Clean once, use many: Put data cleaning (TRIM, VALUE, date coercion) in a prep tab. Query the clean output for speed.
• Document Col mappings: Next to your formula, add a note: Col1=Date, Col2=Owner, Col7=Revenue.
• Combine with Slicers: Slicers + QUERY let non‑technical teammates filter dashboards without touching formulas.

QUERY vs FILTER vs PIVOT vs SQL (alternatives comparison)

• FILTER: quick, great for simple one‑condition pulls. QUERY wins when you need sort/label/group/pivot in one place.
• Pivot Table UI: excellent for ad‑hoc summaries. QUERY builds repeatable, formula‑driven pivots in reports.
• External SQL: BigQuery/DBs are best for large datasets and joins. For in‑sheet analytics on small/medium tables, QUERY is faster to ship.

Implementation guide: build a live sales dashboard in 30 minutes

1. Prep data: Create a Clean_Sales tab with TRIM/VALUE and standardized headers.
2. KPIs: Add a KPIs tab; write a QUERY that rolls up revenue last 30/90 days.
3. Leaderboards: QUERY by rep and by product; add label and format.
4. Charts: Insert bar/line charts from the KPIs ranges.
5. Slicers: Add slicers connected to Clean_Sales (date range, region).
6. Ship: Protect formula ranges, share view‑only with stakeholders.

Common errors and how to fix them

• #VALUE! from query string: Check quotes and escaped characters. Use " for the outer string and single quotes inside.
• Type mismatches: Ensure date columns are true dates, numbers are numeric. Clean in a prep tab.
• Wrong columns after insertions: In your note, map Col numbers to headers and avoid inserting columns inside the queried range.
• Headers off by one: If output includes header labels or misses a row, adjust the headers argument (0 or 1).

Frequently asked questions

How do I write a basic Google Sheets QUERY?

Start with:

=QUERY(A1:G, "select Col1, Col3 where Col5 > 0 order by Col3 desc", 1)

. Replace Col numbers with your column positions.

Can I reference column names directly?

QUERY uses Col1, Col2, etc. Document your mapping next to the formula or in a helper note for clarity.

How do I filter by date range?

Use literals: where Col1 >= date '-01-01' and Col1 < date '-01-01'. Ensure Col1 is a true date.

What’s the difference between QUERY and FILTER?

FILTER returns rows matching conditions. QUERY can filter, sort, aggregate, pivot, and label in one step.

Can I pivot with QUERY?

Yes: add pivot ColX and group by. Example above shows a revenue pivot by region.

How do I combine multiple tabs?

Use array literals: {Tab1!A1:G; Tab2!A1:G} then QUERY the combined range.

Is regex supported?

Yes with matches and RE2 syntax. Escape backslashes in the string.

How do I format currency in the output?

Use format inside QUERY, e.g., format sum(Col7) '$#,##0'; or format the result range via cell formatting.

Why does my result include a header row?

Set the headers argument to match the header rows in your source. Try 0 if you don’t have headers.

Can QUERY join two tables?

Not directly. Use array literals to align schemas and stack, or do joins with QUERY over pre‑aligned data. For true joins, consider BigQuery or Apps Script.

Recommended tools & deals

• Find Sheet add‑ons and data tools: AppSumo — CSV cleaners, connectors, and dashboard kits that pair well with QUERY.
• Presentation/UI assets: Envato — charts, icons, and slide templates to present your QUERY dashboards.
• Host lightweight APIs for Sheets: Railway — deploy tiny endpoints your sheets can call.

Disclosure: Some links are affiliate links. If you click and purchase, we may earn a commission at no extra cost to you. We only recommend tools we’d use ourselves.

Go deeper: related internal guides

• VLOOKUP vs XLOOKUP — combine XLOOKUP with QUERY for rock‑solid reports.
• Zapier vs Make vs n8n — automate data imports/exports around your Sheets.
• GHL vs HubSpot vs Salesforce — sync CRM data into Sheets safely.
• Sentiment Analysis Tools — score feedback then roll up results with QUERY.

Official docs and trusted sources

• Google Sheets function reference: support.google.com/docs
• QUERY language (Visualization API): developers.google.com
• Spreadsheet tips (Help Center): support.google.com/docs