The QUERY function in Google Sheets selects, filters, sorts, groups, and summarizes a range with one formula. Its query text resembles SQL, while the result expands into neighboring cells.
The examples below use tested employee data to explain columns, headers, conditions, dates, totals, cell criteria, and common formula errors.
QUERY Function Syntax and Headers
=QUERY(data, query, [headers])
- data is the source range or array.
- query is a quoted instruction written in the Google Visualization API Query Language.
- headers is the number of header rows. Use
1when the first source row contains labels.
If you omit the third argument or use -1, Sheets guesses the header count. Supplying the count is clearer and avoids an incorrect guess.
The tested range A1:E6 contains Employee, Department, Salary, City, and Start date. The six examples from the original article remain, with five additional tested cases.
| Employee | Department | Salary | City | Start date |
|---|---|---|---|---|
| John | Sales | 50000 | NYC | Jan 5, 2026 |
| Mary | IT | 60000 | LA | Jan 12, 2026 |
| Peter | Sales | 55000 | NYC | Feb 1, 2026 |
| Ana | HR | 48000 | Chicago | Feb 8, 2026 |
| Tom | IT | 52000 | LA | Jan 31, 2026 |
Select Columns and Filter Rows
Pick specific columns
=QUERY(A1:E6,"select A, C",1)
select A, C returns Employee and Salary. The output includes those two headers followed by all five employees.
Filter with a WHERE condition
=QUERY(A1:E6,"select A, C where C > 50000",1)
The where clause keeps salaries above 50000. Mary, Peter, and Tom remain, with salaries of 60000, 55000, and 52000.
Inside a direct sheet range, query letters refer to source columns. Header names such as Salary cannot replace C in this formula.
Combine QUERY Conditions with AND or OR
Require two conditions
=QUERY(A1:E6,"select A, B, D where C > 50000 and D = 'LA'",1)
Both conditions must be true. The result contains Mary and Tom, their IT department, and LA city values.
Accept either condition
=QUERY(A1:E6,"select A where B = 'Sales' or B = 'HR'",1)
The OR formula returns John and Peter from Sales plus Ana from HR. Text literals inside the query use single quotation marks.
For a simpler row subset without query-language clauses, compare the FILTER function in Google Sheets.
Filter, Sort, and Limit QUERY Results
Filter Sales and sort by salary
=QUERY(A1:E6,"select A, B, C where B = 'Sales' order by C desc",1)
The formula filters Sales employees, then sorts salary from highest to lowest. Peter at 55000 appears above John at 50000.
Return the top three rows
=QUERY(A1:E6,"select A, C order by C desc limit 3",1)
order by C desc places the highest salary first. limit 3 returns Mary, Peter, and Tom.
If you only need to reorder a range, the SORT function uses ordinary formula arguments instead of a query string.
Group Rows and Sum a Column
=QUERY(A1:E6,"select B, sum(C) group by B label sum(C) 'Total Salary'",1)
sum(C)adds the Salary values.group by Bcreates one total for each Department.labelchanges the calculated column heading to Total Salary.
The tested result contains HR at 48000, IT at 112000, and Sales at 105000.
Every selected non-aggregated column must appear in group by. Otherwise, QUERY reports a query-string error.
Use a Cell Value in QUERY
With 50000 in H2, join the cell reference to the quoted query text:
=QUERY(A1:E6,"select A, C where C > "&H2,1)
The result contains Mary, Peter, and Tom. Because the criterion is numeric, it does not need quotation marks inside the completed query.
Filter Dates with QUERY
=QUERY(A1:E6,"select A where E >= date '2026-01-01' and E < date '2026-02-01'",1)
QUERY date literals use date 'yyyy-mm-dd'. This formula returns John, Mary, and Tom because their start dates fall in January 2026.
Use Col Notation for Array Inputs
=QUERY({A1:E6},"select Col1, Col3",1)
Array inputs use Col1, Col2, and similar identifiers. The tested formula returns Employee and Salary with the same rows as the direct-range select example.
This distinction matters when braces combine ranges. Direct range queries use source letters, while array-generated columns use Col identifiers.
Fix Mixed Types and QUERY Formula Errors
Mixed column types can become null
=QUERY({"Amount";10;20;"pending"},"select Col1 where Col1 is not null",1)
QUERY assigns a majority data type to each column. In this test, 10 and 20 remain, while the minority text value “pending” is treated as null.
Check quotes, headers, and output space
Use single quotes for text inside the double-quoted query string. Pass the correct header count, and use the appropriate column identifier style.
QUERY returns an expanding array. Clear occupied cells in its output area when Sheets reports that the array result could not expand.
Other Google Sheets articles you may also like