What does this formula do in plain English?

The FILTER function allows you to extract matching records dynamically without modifying or hiding your raw dataset rows.

Why is my formula not working?

Typing data inside a spill range causes #SPILL! errors.

Does this work in Google Sheets?

Yes, this syntax is fully compatible with Google Sheets.

What is the most common mistake?

Referencing spilled cells with standard ranges.

Updated: 2026-09-24 · excelformulaai practical guide

How to Use the FILTER Function in Excel

FILTER returns only the rows or columns that satisfy a condition. It is especially useful when a report should update automatically as source data changes.

Basic formula

=FILTER(A2:D100,B2:B100="West","No records found")

The first argument is the data to return. The second is the include test. The optional third argument is shown when nothing matches.

Multiple criteria

Use multiplication for an AND condition:

=FILTER(A2:D100,(B2:B100="West")*(C2:C100>5000),"No match")

Both tests must evaluate TRUE for a row to be returned. For OR logic, add the tests:

=FILTER(A2:D100,(B2:B100="West")+(B2:B100="North"),"No match")

Filter by date

=FILTER(A2:D100,(C2:C100>=DATE(2026,1,1))*(C2:C100<DATE(2027,1,1)),"No records")

Using real date values is safer than comparing formatted date text.

Filter text conditions

=FILTER(A2:D100,ISNUMBER(SEARCH("laptop",A2:A100)),"No match")

SEARCH is useful for contains-style matching. If the source can contain errors, consider cleaning or guarding the condition first.

Using a table

Structured references make reports easier to maintain. A table formula such as =FILTER(Sales[[Customer]:[Amount]],Sales[Region]=H2,"No records") can automatically include newly added rows.

Why #CALC! appears

When FILTER finds no rows and the third argument is omitted, Excel can return #CALC!. Add a clear fallback such as "No matching records".

Why #SPILL! appears

FILTER normally returns multiple cells. If the destination area is blocked, the formula cannot spill. Clear the obstructing cells and check for merged cells.

FILTER versus manual filtering

Manual filters are excellent for interactive inspection. FILTER is better when another cell, dashboard, or report needs a live formula-driven result that updates as the underlying data changes.

FILTER with SORT and UNIQUE

=SORT(UNIQUE(FILTER(B2:B100,C2:C100="Active","No match")))

This pattern is useful for dynamic dropdown sources and clean report lists.

Common mistakes

The include range has a different height from the source range.
Text dates are compared as if they were real dates.
AND/OR logic is written incorrectly.
The output area is blocked.
The formula is entered into an Excel Table where the desired spill behavior is constrained.

Verification checklist

Check that source and include ranges have matching dimensions.
Test one condition before adding a second.
Test a known matching row and a known non-matching row.
Test the no-match case.
Confirm the spill area remains available when the source grows.

Takeaway

FILTER turns a static dataset into a live, formula-driven view. Start with one condition, verify it, then layer in multiple criteria, sorting, uniqueness, and error handling.

Filter one column and return another

=FILTER(D2:D100,A2:A100=H2,"No customer")

The returned array does not need to be the same columns used for the condition. This is useful for extracting email addresses, invoice numbers, or amounts based on a separate key.

Filter blanks and non-blanks

=FILTER(A2:D100,A2:A100<>"","Rows with an ID")

Use explicit blank tests when the business question is about populated records. Be aware that formulas returning an empty string are not always equivalent to genuinely empty cells in every operation.

Case sensitivity

Ordinary equality comparisons are generally not case-sensitive. If case-sensitive matching is required, combine FILTER with EXACT:

=FILTER(A2:D100,EXACT(B2:B100,H2),"No exact-case match")

When FILTER is not the right tool

If the user needs to physically remove records, use a controlled cleaning workflow rather than assuming a FILTER output has changed the source. FILTER creates a view; it does not delete source rows. For interactive one-off analysis, Excel's built-in filter UI may be simpler.

Auditability

Keep criteria cells visible and labeled. A formula such as =FILTER(Data!A2:F500,(Data!B2:B500=H2)*(Data!F2:F500>=H3),"No match") is easier for another person to audit when H2 and H3 have clear labels.

Final verification

Test matching, non-matching, blank, duplicate, and newly added records. A dynamic report is trustworthy only when those boundary cases behave as expected.