Google Sheets QUERY Function
QUERY returns a new table from a source range using a compact query string. You can select columns, filter rows, sort results, aggregate values, and rename output headers without changing the source data.
Reviewed by the SheetsX team
The query text goes inside double quotes; text conditions inside it use single quotes.
The recorded formula filters Region and Revenue in one QUERY clause.
SheetsX confirms 1100, 850, and 780 in descending order while preserving A1:D9.
How do you use QUERY in Google Sheets?
Use =QUERY(data, query, headers). For example, =QUERY(A1:D9,"select B, C, D where C = 'East' and D > 700 order by D desc",1) returns columns B–D, keeps East rows above 700, and sorts revenue from highest to lowest. The final 1 tells Google Sheets that the source has one header row.
Watch SheetsX build and verify a QUERY result
The Agent clears the hardcoded placeholder output, writes one QUERY formula in F1, allows the array result to expand, formats the returned table, and verifies every row and source value.
The recording uses fictional order data. It shows the exact English prompt, the QUERY formula, the expected three rows, and confirmation that A1:D9 stayed unchanged.
“Replace the incorrect hardcoded query output in F1:H4 with this formula in F1: =QUERY(A1:D9,"select B, C, D where C = 'East' and D > 700 order by D desc label B 'Product', C 'Region', D 'Revenue'",1). Do not change any source data in A1:D9. Remove the red fill from F1:H4, format F1:H1 with dark green fill and bold white text, use pale green fill for F2:H4, and verify the rows are Laptop Stand | East | 1100; Laptop Stand | East | 850; USB Hub | East | 780, in that order. Reply in English and confirm no other cells changed.”
How to use QUERY in Google Sheets
Choose a source table and an empty destination area, then combine select, where, order by, and label clauses inside the query string.
- 1
Choose the source range
Use a rectangular range that includes every column referenced by the query. In the demo, A1:D9 contains one header row and eight records.
- 2
Select the output columns
Start the query string with select B, C, D to return Product, Region, and Revenue from the source range.
- 3
Add filters and sorting
Use where C = 'East' and D > 700 to filter rows, followed by order by D desc to put the largest revenue first.
- 4
Set the header count and verify the spill range
Pass 1 as the third argument because row 1 is a header. Keep every output cell empty so the QUERY array can expand without a #REF! error.
Google Sheets QUERY formula examples
The query language supports column selection, filters, sorting, grouping, aggregation, limits, and custom labels. Column letters refer to positions within the data range.
| Use case | Formula or setting | What it does |
|---|---|---|
| Select specific columns | =QUERY(A1:D9,"select B, D",1) | Returns only Product and Revenue while leaving the source table unchanged. |
| Filter text and numbers | =QUERY(A1:D9,"select B, C, D where C = 'East' and D > 700",1) | Keeps rows that satisfy both the East text condition and the revenue threshold. |
| Sort highest to lowest | =QUERY(A1:D9,"select B, D order by D desc",1) | Orders returned rows by column D in descending numeric order. |
| Aggregate by category | =QUERY(A1:D9,"select C, sum(D) group by C label sum(D) 'Revenue'",1) | Groups rows by Region and calculates total Revenue for each region. |
| Return the top five | =QUERY(A1:D9,"select B, D order by D desc limit 5",1) | Sorts revenue descending and limits the dynamic output to five rows. |
Ask SheetsX to write and verify a QUERY formula
A precise Agent prompt identifies the source and output ranges, supplies the exact formula and expected order, defines formatting, and explicitly protects the source data.
Install SheetsX- Open a new English Agent chat so the task starts with clean context.
- Name the source range and the destination spill range.
- Provide the exact QUERY formula and the expected returned rows.
- State which values and headers must remain unchanged.
- Review the formula bar, output order, formatting, and no-other-cells-changed confirmation.
Google Sheets QUERY questions
What is the Google Sheets QUERY function?+
QUERY returns a derived table from a source range using Google Visualization API Query Language clauses such as select, where, group by, pivot, order by, limit, and label.
What does the last argument in QUERY mean?+
The third argument is the number of header rows in the source. Use 1 when the first row contains headers, 0 when there are no headers, or omit it to let Google Sheets guess.
Why does my QUERY formula show #REF!?+
The result often cannot expand because one or more cells in its spill area already contain data. Clear the destination cells without deleting the source range, then let QUERY recalculate.
Why does QUERY say NO_COLUMN or return a parse error?+
Column references must match the data range. Use letters such as A, B, and C for a normal range, and check quote pairs, clause order, commas, and parentheses in the query string.
How do I use text in a QUERY where clause?+
Put the whole query inside double quotes and the text value inside single quotes, for example where C = 'East'. Escape or construct strings carefully when the text itself contains an apostrophe.
Does QUERY change the source data?+
No. QUERY reads the source range and returns a separate dynamic result. Editing the output formula does not reorder or delete the underlying rows.