FILTER Function in Excel: Filter Data That Updates Live

The Filter button hides rows until someone changes the data — then the hidden rows are wrong, and someone has to re-filter. The FILTER function returns only the matching rows as a live spill: add a row to the source and it appears in the result; change a value and the row drops out. It turns a filtered view from a snapshot into a live report.

What FILTER does

FILTER takes a range and a TRUE/FALSE test, and returns only the rows where the test is TRUE. The test is usually a comparison on one column, but it can be a combination: region equals East AND amount over 100. If nothing matches, it returns #CALC! unless you give it a fallback message as the third argument.

The syntax

=FILTER(array, include, [if_empty])

  • array — the range to filter, for example A2:D20.
  • include — the test, for example B2:B20=”East”. Rows where it is TRUE are kept.
  • if_empty — optional: what to show when nothing matches, for example “No orders”. Without it you get #CALC!.

Step-by-step example

Show only the East-region orders from this sales list.

  1. Click an empty cell where the filtered copy should start — keep the cells below and right empty.
  2. Type =FILTER(A2:D13,B2:B13="East") — the range first, then the test on the Region column.
  3. Press Enter. Only the East rows spill below.
  4. Add a second condition with multiplication for AND: =FILTER(A2:D13,(B2:B13="East")*(D2:D13>100)) keeps East orders over $100.
  5. Add a fallback for empty results: =FILTER(A2:D13,B2:B13="North","No orders") shows a message instead of #CALC! when nothing matches.

Worked example

Region Product Amount
East Notebook $120
West Notebook $200
East Pen set $85
East Notebook $95

=FILTER(A2:C5,B2:B5="East") returns the three East rows and drops the West row entirely. Add a new East order to the source and it appears in the result with no extra step.

Common errors

#CALC! — nothing matched. No row passed the test. Check the spelling and case of the criteria, then add the if_empty fallback so the report shows a message instead of an error.

#SPILL! — no room for the result. The filtered rows need empty cells below the formula. Clear the area, including cells that contain only a space.

The test returns the wrong rows. The include range must be the same height as the array. A test on B2:B13 against data in A2:D20 silently misaligns every row.

It filters once and stops updating. You used the Filter button, not the FILTER function. Only the function is live; the button is a one-time view.

Tip

FILTER is the live replacement for most helper-column-and-filter workflows: instead of flagging rows and filtering the flags, filter the rows directly and let the result spill. Combined with SORT it builds live leaderboards and live shortlists in a single formula.

FILTER with OR, and filtering one column

For OR conditions, add the tests instead of multiplying them: =FILTER(A2:D13,(B2:B13="East")+(B2:B13="West")) keeps rows from either region, because TRUE plus TRUE is still TRUE in this context. And FILTER can return a single column: =FILTER(A2:A13,B2:B13="East") spills just the matching order IDs, which is the fastest way to build a live list of IDs for a dropdown or a lookup. One caution: a filtered column that feeds a dropdown will change length as data changes, so name the spill range with # (for example A2#) when you reference it.

Practice file

Download the FILTER practice file (.xlsx) — it contains a sales list with a live FILTER formula, an AND version, an OR version, and a blank sheet for you to try it yourself.

Google Sheets

Google Sheets has FILTER with the same syntax, and it spills live results the same way. Sheets also offers the FILTER view feature for interactive filtering, which is separate — the function is for reports, the view is for browsing.

Example prices and figures are illustrative, not market data.