The Google Sheets QUERY function turns a table into a live report using one formula. Point it at a range, write a simple query such as select B, E where D = 'Open', and specify how many header rows the range contains. You can filter tasks, sort priorities, count records, and summarize hours without repeatedly copying rows into a second sheet.
This walkthrough uses one small office task tracker and six copy-ready formulas. You will see the exact input rows, expected results, common errors, and a practical way to hand the report to a colleague.
What does the Google Sheets QUERY function do?
QUERY reads a rectangular range and returns the rows or grouped results described by a query string. Its syntax is =QUERY(data, query, [headers]). The first argument identifies the source cells, the second contains instructions in the Google Visualization query language, and the optional third tells Sheets how many header rows are present. For a table with labels in row 1, use 1 rather than relying on automatic guessing.
Think of QUERY as a reusable report definition, not a replacement for accurate source data. It can choose columns, keep matching rows, sort output, and aggregate numeric values. It does not correct misspelled statuses, decide which tasks are truly finished, or preserve manual edits typed into its output range. Google’s official QUERY function documentation describes the arguments and the way mixed column types are handled.
Build a small source table before writing formulas
Create a sheet named Tasks and enter this sample in cells A1:E9. Format column A as dates and column E as numbers. Keep one header row, one record per task, and no merged cells. The hours are illustrative workload estimates, not verified timesheets or actual productivity measurements.
| A: Date | B: Owner | C: Team | D: Status | E: Hours |
|---|---|---|---|---|
| 2026-10-19 | Ava | Ops | Done | 3 |
| 2026-10-19 | Ben | Sales | Open | 5 |
| 2026-10-20 | Ava | Ops | Open | 2 |
| 2026-10-20 | Chen | Ops | Done | 4 |
| 2026-10-21 | Ben | Sales | Done | 6 |
| 2026-10-21 | Dana | Sales | Open | 1 |
| 2026-10-22 | Chen | Ops | Open | 3 |
| 2026-10-22 | Dana | Sales | Done | 2 |
Copy-ready QUERY practice table
This is the same illustrative eight-row task dataset, not a new data source. Paste it into Tasks!A1, format column A as real dates and column E as numbers, and put each QUERY formula from the examples on a separate Reports area.
Date Owner Team Status Hours
2026-10-19 Ava Ops Done 3
2026-10-19 Ben Sales Open 5
2026-10-20 Ava Ops Open 2
2026-10-20 Chen Ops Done 4
2026-10-21 Ben Sales Done 6
2026-10-21 Dana Sales Open 1
2026-10-22 Chen Ops Open 3
2026-10-22 Dana Sales Done 2Expected checks: Done hours are Ops 7 and Sales 8; total estimated hours including open tasks are Sales 14 and Ops 12. QUERY cannot correct incorrect source values on its own.
Make a second sheet called Reports. Place each example in an empty area so the returned array has room to expand. The source range is explicitly Tasks!A1:E9; later, you can use Tasks!A1:E to include future rows. When colleagues add new columns, review the query letters before assuming the report still means the same thing.
Example 1: Show open tasks and sort by estimated hours
Paste this formula into Reports!A1: =QUERY(Tasks!A1:E9,"select B, C, E where D = 'Open' order by E desc",1). It selects owner, team, and hours, excludes completed tasks, and puts the largest estimates first. The output should show Ben/Sales/5, Chen/Ops/3, Ava/Ops/2, then Dana/Sales/1. That gives a coordinator a short view of unfinished work without changing the source sheet.
Column letters refer to their positions in the original range, not the labels printed above them. The text comparison 'Open' is inside single quotes because the whole query is enclosed in double quotes. If the source contains open instead of Open, a case-sensitive text comparison may not match; standardize the status values at entry time.
Example 2: Total completed hours by team
For a summary instead of a task list, use =QUERY(Tasks!A1:E9,"select C, sum(E) where D = 'Done' group by C label sum(E) 'Completed hours'",1). It groups completed records by team and adds the numeric hours. In the sample, Ops totals 7 hours and Sales totals 8 hours. The label clause replaces the default calculated-column heading with a reader-friendly name.
Every ordinary column in a grouped select must also appear in group by. Do not add Owner to this query unless you intend to group by both Team and Owner. For a different way to explore messy Excel data, see the practical PivotTable guide; a PivotTable is often easier for colleagues who prefer a graphical summary builder.
Example 3: Count open tasks for each team
Use =QUERY(Tasks!A1:E9,"select C, count(B) where D = 'Open' group by C label count(B) 'Open tasks'",1). Both Ops and Sales return 2 open tasks in this example. The count(B) expression counts nonblank Owner cells among matching rows, so a blank owner would not contribute. If your process permits unassigned tasks, count a consistently populated task ID column instead.
This is a workload count, not a quality or performance score. A two-hour request and a five-hour request both count as one task. Pair counts with estimated hours or deadline risk before drawing conclusions. For a shared reporting process, the spreadsheet change-log guide explains how to document material corrections to the source and downstream reports.
Example 4: Filter records from a particular date
To review the last two sample days, enter =QUERY(Tasks!A1:E9,"select A, B, D where A >= date '2026-10-21' order by A asc",1). This returns the four records dated October 21 and 22. The query language expects a date literal in yyyy-mm-dd form, with the word date before the quoted value. The source cells must be real dates, not text that merely looks like a date.
For a date chosen in Reports!H1, build the literal with =QUERY(Tasks!A1:E,"select A, B, D where A >= date '"&TEXT(H1,"yyyy-mm-dd")&"' order by A asc",1). Put a valid date in H1 first. If dates are imported as text, convert them in the source rather than hiding the problem in an increasingly complicated query.
Example 5: Build a report controlled by two cells
Enter Open in Reports!H2 and 3 in Reports!H3. Then use =QUERY(Tasks!A1:E,"select B, D, E where D = '"&H2&"' and E >= "&H3&" order by E desc",1). The expected rows are Ben/Open/5 and Chen/Open/3. Changing the status or minimum hours changes the returned report without editing the query itself.
Use a drop-down for the status cell and numeric validation for the hours cell. A status containing an apostrophe can break the quoted query string; for arbitrary user-supplied text, handle quoting deliberately instead of concatenating unchecked values. Keep the input cells visibly separate from the output area so a reviewer knows which settings produced the displayed rows.
Example 6: Rank total estimated hours by team
Use =QUERY(Tasks!A1:E9,"select C, sum(E) where E is not null group by C order by sum(E) desc label sum(E) 'Total hours'",1). This includes both open and completed work and returns Sales/14 followed by Ops/12. The is not null condition excludes blank numeric values. It does not automatically detect an estimate entered as text, so check the source column type before using the result in a decision.
For recurring exports, save one authoritative source workbook and describe its purpose in the filename. The shared-folder naming guide explains a practical convention so colleagues can identify the current version.
Why does QUERY return an error, blank cell, or surprising total?
First check the exact range, the column letters, and the header argument. A query built for A:E will not necessarily mean the same thing after somebody inserts a column. A #REF! spill error can mean the destination cells are occupied; clear the output area rather than deleting source data. A parse error often comes from misplaced quotes, a missing comma, or a clause in the wrong order.
Next inspect data types. Google notes that when one column mixes types, QUERY chooses the majority type and treats minority values as null. A numeric Hours column containing values such as 5 hrs may therefore lose entries from totals. Convert imported text to numbers, use genuine date cells, and keep status spelling consistent. The official query-language reference documents clause order, comparisons, aggregation, and date literals.
Finally, distinguish QUERY from Excel lookup formulas. QUERY reshapes a table; a lookup retrieves a matching value for a key. If your real problem is matching a code to a record in Excel, the XLOOKUP examples are a better starting point.
How should you hand off a QUERY-based report?
Keep three things visible: the source tab, the report formula, and the reporting definition. A practical note might read: Source: Tasks!A:E; open tasks are Status = Open; hours are estimates; refresh is automatic when source rows change; owner checks status spelling and date types before Monday’s review. This makes the output interpretable when the original author is away.
Before sharing, compare the returned row count and one grouped total with the source data, check that newly appended rows are included, and confirm recipients have permission to view the workbook. A QUERY report is useful because it is repeatable, but it is only as dependable as the data and assumptions behind it. Start with one accurate formula, then expand the reporting logic only when a real question requires it.
Frequently asked questions about Google Sheets QUERY
What does the last 1 mean in a QUERY formula?
It tells Sheets that the selected source range has one header row. For the sample Tasks!A1:E9 data, the heading row is row 1.
Why do numeric QUERY totals omit some rows?
Mixed column types can cause minority-type values to be treated as null. Check that Hours contains numbers, not strings such as “5 hrs”.
Why does QUERY give a parse error?
Check query clause order, quotation marks, commas, parentheses and whether date literals use the supported date syntax.
Why does a QUERY formula show #REF!?
The array output may have nowhere to expand because destination cells contain data. Move the formula or clear the intended report area without deleting source records.
Should I use QUERY or XLOOKUP?
Use QUERY to filter or aggregate rows into a report. Use XLOOKUP in Excel to find a key and return its corresponding value.
Related resource: For additional practice with formulas, see the Excel Formula Troubleshooter, which includes guided exercises and troubleshooting examples for common Excel formula issues. Review the kit details before purchasing.