ixr.dhr.mybluehost.me Logo

Google Sheets QUERY Function (2025): SQL Power Without SQL

by

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 2025 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.

Google Sheets QUERY function 2025: SQL-style analytics inside spreadsheets
QUERY gives you SQL‑style analytics directly in Sheets—fast, readable, and maintenance‑friendly.

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.
Anatomy of Google Sheets QUERY: range, query string, headers
Anatomy: =QUERY(range, "SQL-like string", headers). Keep queries human‑readable and versionable.

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)
QUERY select where order by in Google Sheets with practical examples
Filter → sort → limit in one readable statement—no chained helper formulas.

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 = '2025'
   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 '2025-01-01'
  • Between: where Col1 >= date '2025-01-01' and Col1 < date '2026-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 '2025-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)
Union multiple tabs with array literals and query the combined range
Unify data from multiple tabs with array literals, then QUERY once.

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 '2025-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.
Interactive dashboard using QUERY with slicers and clean labels
Pair QUERY with slicers and charts for interactive dashboards.

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 '2025-01-01' and Col1 < date '2026-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

Official docs and trusted sources

QUERY vs FILTER vs Pivot: which to use when in Google Sheets
Pick the right tool: FILTER for simple pulls, QUERY for reports, Pivot UI for ad‑hoc analysis.

all_in_one_marketing_tool