How to Use FILTER in Google Sheets
FILTER in Google Sheets returns only the rows that match your conditions. Here's the syntax, AND and OR examples, how to avoid #N/A, and how to use it for live working views.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
FILTER in Google Sheets returns a live subset of your data based on the conditions you give it. Instead of hiding rows with the toolbar filter, it creates a second view that updates automatically as the source changes.
That makes it useful for weekly lead lists, campaign views, overdue follow-up queues, and any other workflow where one master table needs multiple working views.
The syntax
=FILTER(range, condition1, [condition2], ...)- range — the rows and columns you want returned
- condition1 — the first test
- Add more conditions for additional filters
In Google Sheets, separate conditions act like AND logic. Every condition has to be true for a row to appear.
Basic example
Return all rows where column C contains the stage Proposal:
=FILTER(A2:G100, C2:C100="Proposal")This is a clean way to build a working shortlist from a bigger sales pipeline template.
Multiple conditions with AND
Return rows where the owner is Maya and the next due date is this week:
=FILTER(A2:G100, B2:B100="Maya", F2:F100<=TODAY()+7, F2:F100>=TODAY())Because each condition is a separate argument, Sheets only returns rows where all of them are true.
OR logic in Google Sheets FILTER
If you want rows where stage is either Discovery or Proposal, use an array expression:
=FILTER(A2:G100, (C2:C100="Discovery") + (C2:C100="Proposal"))The + works like OR here. If either comparison returns true, the row is included.
✦ TIP
If FILTER returns #N/A because no rows matched, wrap it in IFNA() so the sheet shows a cleaner message instead.
=IFNA(FILTER(A2:G100, C2:C100="Closed Won"), "No matching rows")Use FILTER to build a due-this-week CRM view
If column E contains next due dates and column F contains owner, you can pull only the contacts due this week for one rep:
=FILTER(A2:H100, E2:E100<=TODAY()+7, E2:E100>=TODAY(), F2:F100="Jordan")This is the kind of view that makes a CRM spreadsheet template easier to review without editing the source data directly.
Common FILTER mistakes in Google Sheets
| Mistake | What happens | Fix |
|---|---|---|
| Conditions do not match the row count of the range | #VALUE! or wrong output | Make every condition span the same rows as the range |
| No rows meet the criteria | #N/A | Wrap with IFNA() |
| Expecting OR logic from separate arguments | Too few rows returned | Use + inside an array expression |
| Filtering a full column against a small range | Slow or inconsistent sheets | Use matching bounded ranges whenever possible |
FILTER vs the toolbar filter
| Toolbar filter | FILTER formula | |
|---|---|---|
| Changes the original view | ✓ Yes | ✗ No |
| Creates a separate live output | ✗ No | ✓ Yes |
| Can be referenced in other formulas | ✗ Not directly | ✓ Yes |
| Best for reusable reporting views | ✗ Weak | ✓ Strong |
If you need one-off exploration, the toolbar filter is fine. If you need a reusable working view that keeps updating, use the formula.
The Griddy way
FILTER is one of the fastest ways to turn a messy master sheet into a usable operating view, but the condition logic gets tedious fast. Just describe the slice you want:
"Show me only agency leads owned by Sam that are due this week, and return a clean message if there are none"
Griddy builds the FILTER formula, handles the IFNA wrapper, and places the result where the team will actually use it.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Build cleaner filtered views from live lead and content data
Google Sheets FILTER is useful when teams need focused views of campaigns, contacts, or follow-ups without creating separate manual copies of the underlying sheet.
Content Calendar
Plan topics, channels, owners, publish dates, and content status in one editorial board. Track weekly campaigns and keep your publishing mix visible.
Social Media Calendar
Plan social posts by platform, format, posting time, asset, and status in one publishing board. Keep launches, drafts, and scheduled content visible.
CRM Lead Tracker
Track contacts, lead source, owner, next due date, and follow-up status in one lightweight CRM sheet. Keep hot opportunities and stale leads visible without paying for heavy sales software.
CRM Lead Tracker for Agencies
Track agency leads, referrals, discovery calls, proposal status, owners, and next steps in one free CRM spreadsheet for new-business teams.