If you need a live list of rows that match a condition, the FILTER function can save you from repeatedly applying manual filters or copying results to another sheet. It returns the records that meet your criteria and can update when the source data changes.
When FILTER is useful
FILTER is useful when you want a formula-driven result such as all orders for one region, all unpaid invoices, or all products above a sales threshold. It is especially useful when the result needs to refresh as the source data changes.
Basic FILTER syntax
=FILTER(array,include,[if_empty])array is the data you want returned. include is the TRUE/FALSE test that decides which rows are included. if_empty is an optional message or value to return when nothing matches.
Example: filter sales by region
Suppose A2:C20 contains Date, Region and Sales, and H2 contains the region to find:
=FILTER(A2:C20,B2:B20=H2,"No matching records")The result spills into the cells below and beside the formula. In supported Excel versions, the spilled range changes size as the result changes.
Multiple conditions with FILTER
Use multiplication for AND logic. For example, to return rows where Region is North and Sales are greater than 1000:
=FILTER(A2:C20,(B2:B20="North")*(C2:C20>1000),"No matches")Use addition for OR logic when the conditions are designed so that a row meeting either test should be returned.
FILTER with SORT
You can combine FILTER with SORT when the user needs a filtered result in a particular order:
=SORT(FILTER(A2:C20,B2:B20=H2,""),3,-1)This example filters by the selected region and then sorts the returned rows by the third column in descending order.
Common FILTER problems
- #CALC!: supply the optional
if_emptyargument when no row may match. - #VALUE!: make sure the include range has compatible dimensions with the array.
- #SPILL!: clear cells blocking the result area.
- Unexpected results: check whether text, spaces, dates or numbers are stored consistently.
FILTER versus manual filtering
Use the normal Data > Filter command when you simply want to inspect a dataset interactively. Use FILTER when you need a formula-driven result that can feed another calculation, report or worksheet.
Compatibility note
FILTER is a dynamic-array function and is available in newer Excel versions. If your workbook must work in an older Excel release, verify compatibility before replacing an older workflow with FILTER.
Practical checklist
- Make sure the returned array and criteria ranges line up.
- Decide what should happen when there are no matches.
- Leave enough empty cells for the spilled result.
- Use an Excel Table or structured references when the source grows regularly.
- Check the Excel version used by everyone who will open the workbook.
Migration 074: FILTER refinement.
What happens when FILTER has no results?
If no rows meet the condition and you leave the third argument out, Excel can return #CALC! because it cannot return an empty array. Give if_empty a useful result when an empty match is a normal possibility:
=FILTER(A2:C100,B2:B100=H2,"No matching records")For a report, a short message is usually clearer than leaving the user with an error. If you need a blank-looking result, use "", but remember that the formula is still returning a result.
AND versus OR conditions
Use multiplication for an AND condition and addition for an OR condition. For example, to return North orders above 1000:
=FILTER(A2:D100,(B2:B100="North")*(D2:D100>1000),"No matches")For North or East:
=FILTER(A2:D100,(B2:B100="North")+(B2:B100="East"),"No matches")Watch for #SPILL!
FILTER normally returns a dynamic array, so Excel needs enough empty cells for the result. If something is blocking the intended spill range, Excel can return #SPILL!. Clear or move the blocking cells rather than copying the formula into each output cell.
When FILTER is not the best choice
If you only need to inspect or temporarily narrow a table, the normal Data > Filter command is simpler. FILTER is the better fit when the result itself needs to be formula-driven, refreshed with the source data, or passed into another formula such as SORT.
Version note
Microsoft currently lists FILTER for Microsoft 365 and Excel 2021 and later releases. If a workbook must run on an older Excel version, confirm function availability before replacing an established workflow.