What FILTER Actually Returns
FILTER is a dynamic array function. You give it an array, a condition, and an optional message for empty results. Excel returns every row where the condition is TRUE and spills the output down and across as far as it needs. The source range stays untouched. This matters because a normal AutoFilter hides rows visually but still forces you to copy the visible cells somewhere else. FILTER hands you a live result you can point a chart or a SUM at.
The Syntax in Plain Terms
The function takes three arguments. The first is the range you want returned. The second is a boolean condition of the same height. The third is what to show when no rows match. Keep all three in mind even on simple jobs, because the empty-result message is the part most people forget and then panic about later.
Click an empty cell and start typing =FILTER(. Highlight your data block, for example A2:D100 on the Sales sheet. This is the range whose rows you want back, not just the column you filter on.
Add the condition after the comma. To keep West rows, type C2:C100="West". The condition range must have the same number of rows as your return range, or Excel throws a #VALUE! error.
Type a comma, then "none" in quotes as the third argument. Now if no row matches, the cell shows none instead of a #CALC! error, which is far easier for a teammate to read.
Press Enter. Excel drops the matching rows starting at your formula cell. You will see a light blue border around the spilled block. Do not type inside that border, or you will get a #SPILL! error.
Filter a Text Column Like Region
Text conditions are the most common use. On our Sales table the Region column holds values like West, East, and North. A single equals test is enough. You can also test partial matches with the SEARCH function nested inside, which is handy when people type the region inconsistently.
Use =FILTER(A2:D100, C2:C100="West", "none"). Every row where the Region column equals West appears. The match is case-insensitive, so West and west both work.
To catch anything containing east, write =FILTER(A2:D100, ISNUMBER(SEARCH("east", C2:C100)), "none"). SEARCH returns a number when found and an error when not, so ISNUMBER turns it into a TRUE or FALSE test.
Prefix the test with NOT or use <>. For rows that are not West, write =FILTER(A2:D100, C2:C100<>"West", "none"). This returns everything else, which is a quick way to build a reject list.
I recommend pointing the return range at a proper Excel Table, such as Sales[#All], instead of a fixed A2:D100. Then new rows you add to the table are picked up automatically. A common mistake is hard-coding the last row, so the report silently misses data once the table grows past row 100.
Filter With a Number Condition
Numbers open up the useful comparisons: greater than, less than, and between. On the Orders table the Amount column holds dollar values, so I filter deals above a threshold to build a manager view without touching the raw list.
On the Orders table, write =FILTER(A2:E500, E2:E500>5000, "none") to return every deal over $5,000. The Amount column must be real numbers, not text that looks like money, or the comparison fails silently.
Use the multiply trick for AND logic. =FILTER(A2:E500, (E2:E500>=1000)*(E2:E500<=5000), "none") keeps only mid-size deals. Each test in parentheses becomes a column of 1s and 0s, and multiplication acts as AND.
For OR logic, add the tests: =FILTER(A2:E500, (C2:C500="West")+(C2:C500="East"), "none"). A row in either region passes. Mixing plus and times lets you build nearly any rule.
Filter Rows Between Two Dates
Date filtering turns FILTER into a live period report. On the Orders table the OrderDate column holds real dates, so a pair of greater-than and less-than tests pulls a window like the first quarter without a single helper column. I build every monthly view this way because the window slides when I change two cells.
Write =FILTER(Orders, (Orders[OrderDate]>=DATE(2026,1,1))*(Orders[OrderDate]<=DATE(2026,3,31)), "none") to keep Q1 2026 rows. Both tests must be TRUE, so multiply them to get an AND between the two dates.
Put the start in G1 and the end in G2, then use (Orders[OrderDate]>=G1)*(Orders[OrderDate]<=G2). Change either cell and the report slides to a new window with no formula edit. This is my go-to month picker.
Drop one test for a half-open range, like (Orders[OrderDate]>=G1) to show everything from the start date forward. Use this for a running year-to-date list that grows each day.
Filter on Two or More Columns at Once
Real requests rarely involve one column. A manager wants West region AND deals above $5,000 AND closed status. You stack those conditions with the multiply trick so every rule must hold.
Write =FILTER(Sales, (Sales[Region]="West")*(Sales[Amount]>5000)*(Sales[Status]="Closed"), "none") using structured references on a Table named Sales. Each parenthesis is one rule; multiplication means all must be TRUE.
Put 5000 in cell G1 and write (Sales[Amount]>G1). Now the report updates when someone changes G1, with no formula edit. I do this whenever a threshold changes often.
If G1 can be empty, wrap with IF: (G1="",1,(Sales[Amount]>G1)). The 1 means always pass that rule when G1 is blank, so the filter still returns rows. This avoids a confusing empty report.
Nest FILTER Inside SUM and Other Functions
FILTER returns a range, so you can drop it straight into another function. The most practical combo is SUM wrapped around FILTER, which gives you a conditional total without building a pivot table for a one-off question.
Write =SUM(FILTER(Sales[Amount], Sales[Region]="West", 0)). FILTER returns just the West amounts, SUM adds them. The 0 as the third argument means a blank sum instead of text when nothing matches.
Use =COUNTA(FILTER(Sales[Region], Sales[Region]="West", "none")) to count West rows. COUNTA counts the spilled cells. This is faster than COUNTIF when the condition is already complex.
Wrap with SORT: =SORT(FILTER(Sales, Sales[Amount]>1000, "none"), 5, -1) returns the big deals sorted by the fifth column, Amount, in descending order. Chaining dynamic array functions like this is where the real speed comes from.
A common mistake is referencing the spilled output with a normal cell click, then dragging that reference. The spilled range shifts as data changes, so a fixed reference can break. Reference the formula cell itself, not the spilled cells below it.
Why FILTER Throws #SPILL! and #CALC!
Two errors show up most. #SPILL! means something blocks the cells Excel wants to fill. #CALC! with FILTER usually means no rows matched and you left out the empty message. Both are easy to fix once you know the cause.
If you see #SPILL!, look for a value, a merged cell, or a border in the cells below your formula. Delete or move it. Excel needs a clear rectangular area equal to the result size.
If you see #CALC!, the condition matched zero rows and the third argument was missing. Add "none" or 0 as the third argument and the error becomes a clean result.
A #VALUE! here often means your condition range has a different row count than the return range. Make both cover exactly rows 2 through the last data row, for example A2:D100 with C2:C100.
FILTER vs VLOOKUP and Pivot Tables
FILTER returns many rows; VLOOKUP returns one value per match. A pivot table also summarizes, but it is a static object you must refresh. For a live sub-list that feeds another formula, FILTER wins. For a single lookup value, see our focused guide on the classic lookup approach before you reach for FILTER.
You can also link this technique to broader data cleaning habits. When the source is messy, filter it first, then clean. That order saves time because you only fix the rows you actually use.
Next Steps and a Quick Checklist
Start with one condition on a real table, confirm the spill looks right, then add a second rule. Build the empty message in from the start. Once that works, wrap SUM around it for a live total. Keep the return range pointed at a Table so new rows flow in.
Pairs worth learning next: SORT to reorder the spilled block, UNIQUE to drop duplicates before filtering, and SEQUENCE to build test data fast. Each one is a dynamic array function that chains with FILTER cleanly.
