The FILTER function in Google Sheets returns only the rows or columns of a range that meet your conditions, written as =FILTER(range, condition1, condition2 and so on). For example, FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) returns the rows of A2:B26 where column A is above 5 and column D is below 10.
Here's the catch. FILTER can only filter rows or columns at one time, never both in the same formula. To filter both, Google's answer is to use the result of one FILTER as the range in another. And every condition must be exactly the same length as the range, or the formula won't work.
Five formulas that spill a whole block of results
| Function | What it does | Sample from Google's help | Watch out for |
|---|---|---|---|
| FILTER | Keeps only rows or columns that meet conditions | FILTER(A2:B10, NOT(ISBLANK(A2:A10))) | Returns #N/A if nothing matches |
| SORT | Sorts rows by one or more columns | SORT(A2:B26, 1, TRUE) | Other columns come back in their original order |
| UNIQUE | Returns unique rows, dropping duplicates | UNIQUE(B2:D5) | Hidden trailing spaces make rows look like duplicates |
| QUERY | Runs a Google Visualization API Query Language query | QUERY(A2:E6,"select avg(A) pivot B") | Mixed data types in a column are treated as null |
| ARRAYFORMULA | Shows an array result across rows and columns | ARRAYFORMULA(A1:C1+A2:C2) | Array formulas can't be exported |
Building a FILTER formula
- Pick the range you want back, such as A2:B26. This is the data FILTER returns.
- Write the first condition as a column or row of TRUE and FALSE values that lines up with the range, such as A2:A26 > 5. It must be exactly the same length as the range.
- Add more conditions if you need them, such as D2:D26 < 10. A row only comes through when every condition is TRUE for it.
- Keep all your conditions the same type. Google doesn't permit mixing row conditions and column conditions in one FILTER.
- If you get #N/A, FILTER found no values that satisfy your conditions, so loosen a condition or check the data.
Three FILTER patterns worth copying
Google's sample formulas each show a different way to write a condition, as of October 2026.
- FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) uses two comparisons. Notice the condition columns, A and D, don't have to sit inside the returned range.
- FILTER(A2:C5, {TRUE; TRUE; FALSE; TRUE}) uses a typed array instead of a formula. A2:C5 has four rows, so the four values line up and the third row is dropped.
- FILTER(A2:B10, NOT(ISBLANK(A2:A10))) keeps every row where column A isn't empty, which is the quickest way to strip blank rows from a list.
If you only want to hide rows on screen rather than build a new list with a formula, the menu route is simpler, and here's how to filter in Google Sheets with filters and filter views.
Sorting what FILTER returns
SORT takes a range, a sort_column and is_ascending. The sort_column can be the index of a column in the range, such as 1, or a range outside it, like SORT(A2:B26, C2:C26, TRUE), as long as it's a single column with the same number of rows. TRUE sorts ascending and FALSE sorts descending, and you can add further sort columns and flags in order of precedence.
Because any function that takes a range will also accept an array in its place, you can wrap a FILTER inside SORT and get a filtered, sorted list from one formula. Remember that the range is sorted only by the columns you name, and other columns are returned in the order they originally appear. For sorting a sheet by hand instead, here's how to sort in Google Sheets.
Removing duplicates with UNIQUE
UNIQUE(range, by_column, exactly_once) returns rows in the order they first appear. by_column and exactly_once are both false by default. Google's example uses three colour columns in B2:D5:
| Formula | What comes back |
|---|---|
| =UNIQUE(B2:D5) | Red, Yellow, Red; Blue, Magenta, Blue; Green, White, Green. The repeated Red row is dropped |
| =UNIQUE(B2:D5, TRUE) | Compares columns instead of rows, so the repeated column goes |
| =UNIQUE(B2:D5, FALSE, TRUE) | Only rows that appear exactly once: Blue, Magenta, Blue and Green, White, Green |
If UNIQUE returns rows that look identical, check for differing hidden text such as trailing spaces, and make sure numbers are formatted the same way, percentages as percentages and currency values as currency values.
When QUERY or ARRAYFORMULA fits better
QUERY runs a Google Visualization API Query Language query, such as QUERY(A2:E6,"select avg(A) pivot B"). The query must be in quotation marks or be a reference to a cell holding the text. Each column can only hold boolean, numeric or string values; with mixed types, the majority type wins and the minority values are treated as null. The optional headers argument sets the number of header rows, and if it's omitted or set to -1, Sheets guesses it. Google's examples cover Select and Where, Group by, Pivot and Order by, and QUERY accepts either Col notation or A, B notation.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
ARRAYFORMULA lets non-array functions work with arrays, as in ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0))). Many array formulas expand into neighbouring cells on their own, and pressing Ctrl+Shift+Enter while editing a formula adds ARRAYFORMULA( to the start for you. You can also build arrays with curly brackets: ={1, 2} puts the values side by side, ={1; 2} stacks them, and ={A1:A10; D1:D10} joins two ranges into one column. In countries that use commas as decimal separators, backslashes replace the commas.
My take on formula filtering
I'd use FILTER whenever I want a live list that updates itself, and keep the menu filter for a quick look. Line the conditions up with the range row for row, and if you need to cut rows and columns, nest one FILTER inside another. When the list has repeats, wrap it in UNIQUE, and when it needs ordering, wrap it in SORT. I'd only move to QUERY when I want grouping or pivoting, because its column type rules can quietly turn odd values into nulls. If you work in Excel too, my guide to the FILTER function in Excel covers that version.
Frequently asked questions
Can the FILTER function filter rows and columns at the same time?
No. FILTER can only filter rows or columns at one time. To filter both, use the return value of one FILTER function as the range in another.
Why does FILTER return #N/A in Google Sheets?
FILTER returns #N/A when it finds no values that satisfy the conditions you gave it.
Do FILTER conditions have to be the same size as the range?
Yes. Condition arguments must have exactly the same length as the range, and they must all be the same type, rows or columns.
Why does UNIQUE show duplicates?
Cells that look the same can contain differing hidden text such as trailing spaces, or numbers formatted in different ways.
What does Ctrl+Shift+Enter do in a Google Sheets formula?
Pressing Ctrl+Shift+Enter while editing a formula adds ARRAYFORMULA( to the beginning of the formula.
