FILTER Function in Excel

=FILTER(array, include, [if_empty])
↑Click any parameter above to learn what it means — it'll highlight in the live example below.
  • array: (What you want to see) The exact data you want to appear in your final list. You can select a single column, or several columns to show an entire table of results. Required.
  • include: (What to look for) The column you want Excel to check, together with the rule it must match, such as B1:B10="Paid" . (See Common Criteria Patterns in FILTER and Advanced Modes below.) Required.
  • if_empty: (What to say if nothing matches) The exact text Excel shows when it finds zero matches. You must wrap this text in quotation marks, such as "No results" . Optional.

What is the FILTER function in Excel?

The Excel FILTER function shows only the rows that match one or more conditions. Instead of showing all your data, it displays just the information you want.

Imagine shopping online for shoes. You select filters like "Size 10" and "Black", and the website instantly shows only the shoes that match your choices and hides all the other shoes. The FILTER function works the same way in Excel.

Lookup & ReferenceIntermediateWorks in Excel 2021+
!Golden Rules

FILTER's 2 golden rules

Reading these is a must. Skipping them is the #1 reason FILTER formulas break.

If your array selects rows 1 to 100 (for example A1:C100) but your include rule only checks rows 1 to 50 (for example B1:B50), the formula breaks.
Both parameters must cover the exact same number of rows.
FILTER automatically shows every matching result in the cells directly below it, so you never drag it down.
The cells below your formula must be completely empty so Excel has room to display the filtered list.

Learn with Example: FILTER

  1. Step 1: Understand the goal We need to pull a list of every invoice whose Payment Status is Overdue, so we can see at a glance who to follow up with.
  2. Step 2: Start the formula Click the first empty cell of the Overdue Invoices area below your data (A11), where you want your new list to appear, then type the function name and an open bracket. Type: =FILTER(
  3. Step 3: Select array To select your array, highlight the whole Client Invoices table data (A3:D7). This is everything you want to bring over into the new list. Then type a comma. Type: =FILTER(A3:D7, array The exact data you want to see in your final list. Select every column you want to appear.
  4. Step 4: Select include To set your include rule, highlight the Payment Status column (D3:D7) and type ="Overdue", the rule each row must pass. Then type a comma. Type: =FILTER(A3:D7, D3:D7="Overdue", include The column you want Excel to check, plus the rule it must match. Tip: for every way to write a condition, see the criteria and wildcards reference.
  5. Step 5: Select if_empty To set if_empty, type "All clear" so Excel shows a friendly message if nothing matches. Then type a closing bracket ). Type: =FILTER(A3:D7, D3:D7="Overdue", "All clear") if_empty The text to show if zero rows match your rule. Always wrap it in quotation marks.
  6. Step 6: Check the result Press Enter. FILTER instantly spills every matching row into the cells below, so you type the formula once and Excel builds the whole list for you. Two invoices are marked Overdue (INV-102 and INV-103), so only those two rows appear in the new list. Done Hint: You only type this formula once in the top cell. Excel automatically fills the rows below it (called "spilling"). Never drag it down.

You have a master list of client invoices. How do you build a live list that shows only the invoices marked "Overdue", so you know exactly who to call?

A11
ƒx
=FILTER(array,include,[if_empty])
ABCD
1Client Invoices
2Invoice IDClient NameAmountPayment Status
3INV-101Acme Corp5000Paid
4INV-102Globex1200Overdue
5INV-103Stark Ind450Overdue
6INV-104Initech8000Paid
7INV-105Massive Dynamic3200Pending
8
9Overdue Invoices
10Invoice IDClient NameAmountPayment Status
11
12
13
14
Ready
Step-by-step guide1 / 6
Step 1: Understand the goal

We need to pull a list of every invoice whose Payment Status is Overdue, so we can see at a glance who to follow up with.

When to use FILTER

Use the FILTER function when you need to pull out every row of data that matches your rules, and you want that list to update itself automatically.

  • Finding all matching rows: When you need every order from a single customer or every employee on one team, not just the first match.
  • Creating a live report: When you want a mini-report that updates on its own the moment new information is added to your main table.
  • Searching with multiple rules: When you need rows that meet more than one rule at once, such as sales that are in the "West" region and have a "Paid" status. (See Advanced Modes below.)

Because FILTER creates a list that updates automatically, it works well next to the SORT function to order your results and the UNIQUE function to remove duplicates. If you only want a single result instead of a full list, use the XLOOKUP function instead.

When not to use FILTER

Because FILTER is built to create a list of results, you should reach for a different tool in these situations:

  • When you only need to look at data temporarily: If you just want to quickly search or sort without building a new table, use Excel's built-in Filter menus (the dropdown arrows at the top of your columns) instead.
  • When you only need one value from a single cell: If you just want to find one thing, such as a customer's email or a product's price, use the simpler XLOOKUP function.
  • When sharing files with people on older Excel versions: If the people opening your file use Excel 2019 or older, FILTER will not work and shows a #NAME? error. Use the built-in Filter menus or a PivotTable instead.
  • When working with giant tables that slow your computer down: If your table has tens of thousands of rows, many FILTER formulas make Excel recalculate constantly. Use the built-in Filter menus or Power Query to handle large data instead.

FILTER function vs Filter button vs XLOOKUP

If you are trying to find data in a table, you might wonder whether to use a formula or just use Excel's built-in buttons. This table shows when each one fits best.

FeatureFILTER functionFilter buttonXLOOKUP function
Best used forShowing every single row that matches your rule.Quickly hiding rows in your current table.Finding only the first row that matches your rule.
Number of resultsMultiple (spills down the page).Multiple (hides rows).One (returns the first match it finds).
Can use multiple rules?Yes (see Advanced Modes below).Yes (using dropdown checkboxes).Yes, but it is much more difficult.
Updates when data changes?Yes (instantly).No (you must re-apply the filter).Yes (instantly).
Excel versionExcel 365 and Excel 2021 only.All versions.Excel 365 and Excel 2021 only.
Ease of useIntermediate.Beginner.Intermediate.

Bottom line: Use the Filter button to quickly look at data in your current table. Use the FILTER function to build a completely new, separate list of everything that matches. Use XLOOKUP when you are looking for one specific thing, such as a person's phone number.

Common Criteria Patterns in FILTER

The most useful rules you can put inside the include parameter to choose which rows appear.

Criteria PatternFormula ExampleExplanation
Equal to text=FILTER(A1:C10, B1:B10="Paid")Shows the row only if the cell exactly matches the word Paid.
Greater than a number=FILTER(A1:C10, C1:C10>500)Shows the row only if the number is larger than 500.
Not equal to text=FILTER(A1:C10, B1:B10<>"Canceled")Shows the row as long as the cell does not say Canceled.

For the full list, including how to use dates and partial text matches, see the complete Excel Criteria and Wildcards reference.

Advanced Modes: Multiple Rules (AND / OR)

You can force FILTER to check several rules at the exact same time using basic math symbols. Wrap each rule in its own parentheses first.

  • The AND rule (multiply with *): To show rows that meet two rules at once, such as region is "West" and status is "Paid", multiply the rules together with an asterisk:
    =FILTER(A1:C10, (B1:B10="West") * (C1:C10="Paid"))

  • The OR rule (add with +): To show rows that meet either one rule or another, such as region is "West" or region is "East", add the rules together with a plus sign:
    =FILTER(A1:C10, (B1:B10="West") + (B1:B10="East"))

Common Mistakes When Using FILTER

Getting the #SPILL! error because the cells below are not empty
This usually happens when there is already text or data in the cells directly below your formula. Excel will not delete your existing data to show the FILTER results, so you must clear the cells below the formula to give it empty space to display the list.
Getting the #VALUE! error because the array and include are different sizes
This usually happens when your array (what to show) covers a different number of rows than your include (what to look for). For example, an array of A1:C100 with an include of B1:B50 triggers this error. Make sure the row numbers match exactly.
Getting the #CALC! error because nothing matches your rule
This usually happens when Excel searches your data, finds zero matches, and the if_empty parameter was left blank. To fix it, always fill in if_empty at the end of the formula, such as adding "No results".
Getting the #NAME? error because you forgot quotation marks
This usually happens when you type text into your include rule without wrapping it in quotes. Writing B1:B10=Paid instead of B1:B10="Paid" makes Excel think Paid is a formula that does not exist. All text must sit inside quotation marks.
For a complete list of formula errors (like #N/A or #REF!) and how to fix them, see our Common Excel Errors Troubleshooting guide.

Frequently Asked Questions about FILTER

Quick answers about FILTER

Does the FILTER function work in older versions of Excel?

No. FILTER is a modern formula available only in Excel 365, Excel 2021, or Excel on the web. If you send a file with this formula to someone using Excel 2019 or older, they will see an error.

Can I filter my data horizontally across columns instead of vertically down rows?

Yes. It is less common, but if your data runs sideways across the page, FILTER automatically shows its results sideways to match.

Does this work in Google Sheets?

Yes. FILTER exists in Google Sheets and is extremely popular there. However, Google Sheets uses slightly different syntax for multiple rules, so you cannot always copy complex formulas directly between the two programs.

Why does the FILTER function make my spreadsheet run slowly?

This usually happens when you select a full column like A:A for your array instead of specific rows like A1:A100. The formula then tries to search over one million rows at once, which slows your computer down. Select only the rows where your data actually lives.

How do I stop the formula from showing empty rows?

If your original data has blank rows, you can tell FILTER to ignore them by adding a rule that says the column cannot equal empty quotes, for example A1:A10<>"".

Can I automatically sort my search results alphabetically?

Yes. FILTER does not sort on its own, but you can wrap it inside the SORT function to organise the list, for example =SORT(FILTER(A1:C10, B1:B10="Paid")).

Can I manually edit the filtered result data?

No. If you click a cell inside the results and try to type a new value, the formula breaks. To fix or update anything, change it in your original data and the filtered list updates automatically.

How do I filter using two different rules at the same time?

You can check multiple rules at once by multiplying them together, for example finding rows that are "East" and "Paid". See the Advanced Modes section above for the exact steps.

FILTER Practice Workbook

Download ready-to-use Excel files and practice FILTER hands-on

Complete Practice File
FILTER Practice Workbook
.xlsx · 74 KB
This file contains:
  • All 5 worked examples (finance, sales, HR, ops, data), each with a data table ready to filter
  • Blank result areas to write each FILTER formula yourself, covering text, number, and multiple-rule conditions
  • An answer key showing the exact rows each filter should return
Download Complete File
Reviewed by Grapte teamLast updated September 2026Tested in Microsoft Excel 365