How to Use FILTER in Google Sheets
Use FILTER in Google Sheets for live matching rows, multiple AND or OR conditions, sorted results, and a clean no-match fallback.
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.
Google requires each condition to have the same length as the rows or columns being filtered. One FILTER call can filter rows or columns, but not both directions at once.
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.
Combine OR with an owner condition by keeping the OR expression in parentheses:
=FILTER(A2:G100, ((C2:C100="Discovery")+(C2:C100="Proposal"))>0, B2:B100="Maya")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.
Return selected columns and sort the result
Use an array to return only ID, owner, and stage without changing the source table:
=FILTER({A2:A100,B2:B100,C2:C100}, C2:C100<>"Closed")Wrap the result in SORT to order by the second returned column:
=SORT(FILTER(A2:G100, C2:C100="Proposal"), 2, TRUE)The output is a live array. Keep its destination area empty and edit the source rows rather than typing over returned cells.
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 |
| Result cannot expand | Data, a formula, or a merged cell blocks the output | Inspect and move the obstruction; do not clear a large range blindly |
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.
Do this with AI
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.
Related guides: how to filter in Google Sheets for the toolbar workflow and Google Sheets date formulas for rolling date windows.
Sources
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or 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.
Open templateMarketingSocial Media Calendar
Plan social posts by platform, format, posting time, asset, and status in one publishing board. Keep launches, drafts, and scheduled content visible.
Open templateSalesCRM 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.
Open templateSalesCRM Lead Tracker for Agencies
Track agency prospects by client account, service or retainer fit, source, stage, owner, value, and next action.
Open templateSalesCRM Lead Tracker for Real Estate
Track real-estate buyer, seller, investor, tenant, listing, and referral leads by source, stage, owner, value, activity, and next date.
Open templateSalesCRM Lead Tracker for Recruiters
Qualify prospective recruiting clients by requisition label, role family, source, search stage, owner, next action, follow-up, and expected fee.
Open template