Work & productivity / QUICK REFERENCE

Google Sheets formulas: QUERY, FILTER and arrays

Use Google Sheets formulas to filter, sort, summarize, import and transform dynamic ranges.

44 entries

Filter and extract rows

Filter by threshold: =FILTER(A2:D100,C2:C100>100)
Returns rows where the C-column amount exceeds 100. [2][5]
Filter by exact text: =FILTER(A2:D100,B2:B100="Open")
Returns only rows marked Open. [2][5]
Filter by two conditions: =FILTER(A2:D100,B2:B100="Open",C2:C100>100)
Both boolean filter vectors must align to input rows. [2][5]
Filter by nonblank: =FILTER(A2:D100,A2:A100<>"")
Keeps rows with nonempty A-column values. [2][5]
Filter by one of two values: =FILTER(A2:D100,(B2:B100="Open")+(B2:B100="Hold"))
Addition combines boolean alternatives. [2][5]
Filter with fallback: =IFNA(FILTER(A2:D100,C2:C100>100),"No matches")
FILTER uses #N/A when no results satisfy the criteria. [2][5]
Find unique values: =UNIQUE(B2:B100)
Deduplicates repeated values in input order. [2][5]
Unique rows: =UNIQUE(A2:D100)
Only removes rows that match in every selected column. [2][5]
Sort ascending: =SORT(A2:D100,3,TRUE)
Orders result rows by the third column low to high. [2][5]
Sort descending: =SORT(A2:D100,3,FALSE)
Orders result rows by the third column high to low. [2][5]
Top five ranked rows: =SORTN(A2:D100,5,0,3,FALSE)
Returns up to five rows after descending sort on third column. [2][5]
Restrict spill rows: =ARRAY_CONSTRAIN(A2:D100,10,4)
Limits displayed array to ten rows and four columns. [2][5]

QUERY language tasks

Select two columns: =QUERY(A1:D100,"select A, C",1)
Selects the first and third input columns. [1]
Filter numeric rows: =QUERY(A1:D100,"select A, C where C > 100",1)
A Sheets query string uses Google Visualization syntax. [1]
Filter text equality: =QUERY(A1:D100,"select A where B = 'Open'",1)
String values are quoted inside the query text. [1]
Sort one column: =QUERY(A1:D100,"select A, C order by C desc",1)
Sorts in descending C-column order. [1]
Limit result size: =QUERY(A1:D100,"select A, B limit 10",1)
Returns no more than ten data rows. [1]
Summarize values: =QUERY(A1:D100,"select B, sum(C) group by B",1)
Groups categories and sums numeric C values. [1]
Average by category: =QUERY(A1:D100,"select B, avg(C) group by B",1)
Computes average numeric values per category. [1]
Count category rows: =QUERY(A1:D100,"select B, count(A) group by B",1)
Counts nonnull A values for each B group. [1]
Set output label: =QUERY(A1:D100,"select B, sum(C) group by B label sum(C) 'Total'",1)
Overrides the computed column's display heading. [1]
Choose matching category: =QUERY(A1:D100,"select A where B = '"&F2&"'",1)
Inserts a trusted lookup value; quote escaping matters for arbitrary text. [1]
Skip leading results: =QUERY(A1:D100,"select A offset 5",1)
Starts output after the first five matching data rows. [1]
Pivot grouped data: =QUERY(A1:D100,"select B, sum(C) group by B pivot D",1)
Produces columns from the distinct D values when query types permit. [1]

Array expressions and text

Multiply two ranges: =ARRAYFORMULA(B2:B100*C2:C100)
Element-wise multiply equal-length arrays. [3][5]
Conditional row labels: =ARRAYFORMULA(IF(A2:A100="","",IF(C2:C100>100,"High","Low")))
Creates row-wise labels while suppressing blank keys. [3][5]
Uppercase a column: =ARRAYFORMULA(UPPER(B2:B100))
Applies uppercase conversion down a range. [3][5]
Join columns per row: =ARRAYFORMULA(A2:A100&" - "&B2:B100)
Produces row-wise combined text. [3][5]
Increment a numeric column: =ARRAYFORMULA(C2:C100+1)
Computes a new array without dragging a formula down. [3][5]
Count only positive: =COUNTIF(C2:C100,">0")
Aggregates positive cells; it does not return an array. [3][5]
Split text fields: =SPLIT(A2,",")
Splits one string across neighboring cells. [3][5]
Join nonempty values: =TEXTJOIN(", ",TRUE,B2:B10)
Builds one delimited string while ignoring empty cells. [3][5]
Flatten via TOCOL: =TOCOL(A2:C10,1)
Transforms a range into a column, ignoring blanks. [3][5]
Transpose row/column: =TRANSPOSE(A1:D2)
Reorients the source array across axes. [3][5]

Import, permission and errors

Import another sheet: =IMPORTRANGE(F2,"Data!A1:D100")
F2 must contain an authorized source spreadsheet URL. [4][5]
Use an external named range: =IMPORTRANGE(F2,"Sales_total")
A named range in another sheet can be the import target. [4][5]
Import a single column: =IMPORTRANGE(F2,"Data!B2:B100")
Restricting the imported range reduces transferred data. [4][5]
Filter imported data: =QUERY(IMPORTRANGE(F2,"Data!A1:C100"),"select Col1,Col3 where Col3>100",1)
With imported arrays QUERY uses Col notation. [4][5]
Test import permissions: =IMPORTRANGE(F2,"Data!A1")
Initial access usually needs explicit permission approval. [4][5]
Handle an absent result: =IFNA(FILTER(A2:D100,C2:C100>100),"None")
Catches missing matches without masking every calculation error. [4][5]
Inspect formula error: =ISERROR(A2)
Returns TRUE for any error type in A2. [4][5]
Detect #N/A specifically: =ISNA(A2)
Distinguishes missing lookup values from unrelated errors. [4][5]
Keep a single row: =INDEX(A2:D100,1,0)
Returns first row of the source range as an array. [4][5]
Keep a single column: =INDEX(A2:D100,0,2)
Returns all rows of column two as an array. [4][5]

Reserved placement, separate from reference controls. No ad network is active.

Updated 2026-10-20Google Sheets · current

Sources

cheatsheetatlas.com/sheets/google-sheets-formulas/ · Updated 2026-10-20 · Google Sheets · current

← Explore more of the atlas Contact ↗