Open tasks
=FILTER(A2:E, D2:D="Open") returns all columns for rows where status is Open.
What is FILTER in Google Sheets?
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
When to use it
Use FILTER for dynamic lists: open tasks, selected export rows, regional slices, or any table that should shrink and grow as source data changes.
When to skip it
Skip FILTER when you need grouped totals or sorted output with complex SQL. QUERY may be clearer. Static one-time cuts can use filter views instead.
How it works
-
1
Point FILTER at the full data array including all columns you want in the output.
-
2
Add a condition range and test, such as status column equals Open.
-
3
Combine tests with * for AND or + for OR inside parentheses.
-
4
Leave empty room for the spill below and beside the formula cell.
-
5
Wrap with SORT if the filtered list needs ordering without QUERY.
-
6
Use IFERROR or IFNA when no rows match and you want a friendly message.
Examples in Google Sheets
Region and quota
=FILTER(Sales!A:C, (B2:B="West")*(C2:C>1000)) keeps West rows above quota.
Checked rows
=FILTER(Data!A:D, E2:E=TRUE) exports only rows with the Include checkbox on.
Build this without starting from a blank cell
Use a Better Sheets tool for FILTER, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Condition ranges shorter than the data range, which returns #VALUE!.
- Using AND() instead of * between boolean arrays in FILTER.
- Placing the formula over data FILTER should return into.
- Comparing dates stored as text, which filters out every row silently.
- Expecting FILTER to aggregate; it only subsets rows.
Frequently asked questions
- FILTER vs filter view?
- FILTER is formula-driven and can feed other sheets. Filter views are UI-only on one tab.
- Multiple conditions?
- Multiply boolean arrays for AND or add them for OR.
- Why #CALC! empty?
- No rows matched. Use IFERROR to show a custom message.
- FILTER with dates?
- Compare date cells to DATE or TODAY() with consistent numeric date values.
- Can FILTER return some columns?
- Yes. FILTER the column array you need, or FILTER full rows then INDEX columns.
- FILTER and SORT together?
- Wrap FILTER inside SORT for ordered dynamic lists.
- FILTER across sheets?
- Reference another tab in the range argument like any formula.
Related Tutorials
Watch how FILTER works
Make Your Headers Useful
Search Every Function in Google Sheets
Fabulous Filters
Create a Public Sheet and Private Sheet: Using ImportRange()
FILTER Can Save You From Task Overload
Related blog posts
Guides that explain FILTER in more depth.
Filter a Database in Google Sheets based on Dates, Checkboxes, and Dropdown selections!
In today’s tutorial, we’ll be answering the question from Mohamed. The question is “How do I filter a database in Google Sheets based on dates, checkboxes, and dropdown selections?”
Read post →Automate Google Sheets without Apps Script, without Zapier
I've been doing a lot in spreadsheet automation using Apps Script. But I wanted to go back to some formulas that I think feel like magic. They make me feel like a wizard without having to code.
Read post →Revolutionize Your CRM: 3 Game-Changing Google Sheet Formulas!
CRM software is essential for any business looking to increase revenue and build relationships with clients. Many small businesses are looking for their first piece of software to go from spreadsheets to something else. But what if I told you, you could just make your spreadsheets better.
Read post →Related glossary terms
UNIQUE
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
Read guide →QUERY
QUERY runs a SQL-like statement against a range of cells and returns a new table of results. You pass a data range, a query string with SELECT, WHERE, ORDER BY, and optional headers. It is the fastest way to filter, sort, group, and pivot sheet data without helper columns.
Read guide →SORTN
SORTN sorts a range and returns only the first N rows. You choose how many rows, which column drives the sort, and ascending or descending order. Leaderboards and best-seller reports use SORTN instead of manual sorting that goes stale.
Read guide →XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →Done reading about FILTER?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.