Open tasks only
=QUERY(Tasks!A1:D, "select A, B, C where D = 'Open' order by B asc", 1) returns assignee, title, and due date for open items.
What is QUERY in Google Sheets?
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
When to use it
Use QUERY when you need repeatable reports from a flat table: top sellers by month, rows where status is Open, grouped totals, or a sorted slice you refresh from one formula.
When to skip it
Skip QUERY for one-off edits, tiny lists where FILTER is simpler, or when editors must change logic without touching query strings. Heavy text parsing is often easier with REGEX functions.
How it works
-
1
Pick a rectangular data range with headers in the first row, for example Data!A1:F500.
-
2
Write a query string starting with SELECT and list the columns or aggregates you need.
-
3
Add WHERE to filter rows, ORDER BY to sort, LIMIT to cap rows, and GROUP BY for summaries.
-
4
Set the optional headers argument to 1 when the first row of the range contains column labels.
-
5
Paste the formula in a clean output area because QUERY spills its result and overwrites cells below and to the right.
-
6
Refresh IMPORTRANGE or source data first so QUERY reads current values on dashboard tabs.
Examples in Google Sheets
Monthly revenue total
=QUERY(Sales!A1:C, "select B, sum(C) where A is not null group by B label sum(C) 'Revenue'", 1) groups revenue by month.
Top ten products
=QUERY(Products!A1:C, "select A, B order by C desc limit 10", 1) lists the ten highest-scoring SKUs.
Build this without starting from a blank cell
Use a Better Sheets tool for QUERY, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Forgetting quotes around text literals in the query string, which breaks WHERE clauses on status or name columns.
- Pointing QUERY at a range without a header row while leaving the headers argument at 1.
- Placing the formula over existing data so the spill range wipes formulas you still need.
- Using column letters in SELECT when you meant Col1, Col2 labels required by QUERY syntax.
- Expecting QUERY to auto-refresh IMPORTRANGE on a schedule without opening the file or using a trigger.
Frequently asked questions
- Can QUERY replace pivot tables?
- Often yes for simple groupings. Pivot tables are better when non-technical editors need drag-and-drop layout changes without editing a formula.
- What is Col1 in QUERY?
- Col1 is the first column in your data range, Col2 the second, and so on. Use these names inside the query string instead of A, B, C.
- Does QUERY work with IMPORTRANGE?
- Yes. Wrap IMPORTRANGE as the data argument so QUERY filters data pulled from another workbook.
- Why does QUERY return parse error?
- Check quote escaping, column labels, and that your SELECT list only references columns that exist in the range.
- Can QUERY output headers?
- Yes. With headers set to 1, QUERY uses the first row of the range as labels in the result.
- QUERY vs FILTER?
- FILTER is simpler for one condition. QUERY handles sorting, grouping, limits, and multiple clauses in one string.
- Is QUERY case sensitive?
- String comparisons follow QUERY rules. Use upper() or lower() in the query when you need case-insensitive matches.
- Can QUERY run across sheets?
- Yes. Reference another tab in the range argument, for example Summary!A1:E1000.
Related Tutorials
Watch how QUERY works
Search Every Function in Google Sheets
Scrape Google Maps
How do I reference a different spreadsheet in Apps Script?
Search for Photos in a Google Sheet by Keyword
Related blog posts
Guides that explain QUERY in more depth.
Quick Google Sheets Tips
Boost productivity and streamline your workflow in Google Sheets with our quick tips and free tools. Breathe new life into your spreadsheets today.
Read post →What's Better Than Google Sheets?
Nothing is better than Google Sheets. You just need to learn more about the unlimited power of Google Sheets. Functions, Formulas, Apps Script and more.
Read post →Getting Started in Google Sheets Spreadsheet Management
Users can also customize the appearance of their spreadsheets with different fonts, colors, and themes.
Read post →Related glossary terms
ARRAYFORMULA
ARRAYFORMULA tells Google Sheets to treat a formula as an array operation so one expression fills many cells at once. It is common with IF, VLOOKUP, and math on full columns before dynamic arrays made many patterns automatic. It still matters for legacy sheets and explicit control.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →IMPORTRANGE
IMPORTRANGE loads values from a range in another Google spreadsheet into your current file. You pass the spreadsheet URL or key plus a range string with an optional sheet name. After you grant access once, the link stays live and updates when the source changes.
Read guide →UNIQUE
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
Read guide →Done reading about QUERY?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.