Part of the free Module 5: Excel Formulas and Functions · Function 30 of 105 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365 and Excel 2021 or later.
The Excel FILTER function returns every row (or column) of a range that meets one or more conditions, as a dynamic array that spills into the cells below. It is the formula equivalent of AutoFilter: the results update automatically when the source data or the criteria change, and you can combine conditions with AND and OR logic.
FILTER syntax
=FILTER(array, include, [if_empty])| Argument | Required | What it does | Notes |
|---|---|---|---|
| array | Required | The range or array to filter. | Can be one column, several columns or a whole table. The result keeps the same column layout. |
| include | Required | A logical array of TRUE/FALSE values, one per row (or column) of array. | Usually a comparison such as B2:B100=”East”. Must have the same number of rows as array (or columns, when filtering across). |
| if_empty | Optional | Value to return when no rows match. | Text, number or another formula. If omitted, FILTER returns #CALC! when nothing matches. |
How FILTER works
FILTER evaluates the include argument to a list of TRUE and FALSE values and keeps only the rows where the value is TRUE. Because the output is a dynamic array, you enter the formula once in the top-left cell and Excel spills the results downwards and to the right. A thin blue border shows the spill range, and the formula in the other cells appears greyed because they contain no formula of their own.
To combine conditions, multiply logical tests for AND (both must be TRUE) or add them for OR (either can be TRUE). Multiplying TRUE by TRUE gives 1, which FILTER treats as TRUE; TRUE plus FALSE gives 1 as well, while FALSE plus FALSE gives 0.
Version notes. FILTER is available in Excel 365, Excel 2021 and later, and Excel for the web. In Excel 2019 and 2016 it shows #NAME?; the older alternative is an INDEX and SMALL array formula or the Advanced Filter command. Google Sheets has FILTER too, but it takes each condition as a separate argument (condition1, condition2) rather than one include array.
Worked examples
All examples use this sales table in A1:D7.
| A: Order | B: Region | C: Product | D: Amount |
|---|---|---|---|
| 1001 | East | Chairs | 1,200 |
| 1002 | West | Desks | 3,400 |
| 1003 | East | Desks | 2,150 |
| 1004 | North | Chairs | 640 |
| 1005 | East | Lamps | 310 |
| 1006 | West | Chairs | 980 |
Example 1: filter by one condition
Return every East order.
=FILTER(A2:D7, B2:B7="East", "No orders")Result: three rows (1001, 1003 and 1005) with all four columns, spilling into a 3 by 4 block. If you later change a region to East, the block grows automatically.
Example 2: AND logic with two conditions
Return East orders worth more than 1,000.
=FILTER(A2:D7, (B2:B7="East")*(D2:D7>1000), "None")Result: orders 1001 and 1003. The multiplication keeps a row only when both tests are TRUE. Each condition sits in its own brackets; leaving them out is the most common cause of wrong results.
Example 3: OR logic and a total
Sum the amount of all orders that are either Chairs or Lamps.
| Formula | Result |
|---|---|
=FILTER(D2:D7, (C2:C7="Chairs")+(C2:C7="Lamps")) |
1,200; 640; 310; 980 (four cells) |
=SUM(FILTER(D2:D7, (C2:C7="Chairs")+(C2:C7="Lamps"))) |
3,130 |
Adding the two tests gives 1 when either is TRUE. Wrapping FILTER in SUM, AVERAGE or COUNT turns the spilled list into a single figure without SUMIFS.
Example 4: return only some columns
To show just the Order and Amount of West orders, filter a two-column array built with CHOOSECOLS (Excel 365) or filter twice.
=CHOOSECOLS(FILTER(A2:D7, B2:B7="West"), 1, 4)Result: 1002 with 3,400 and 1006 with 980. In Excel 2021, which lacks CHOOSECOLS, nest FILTER inside FILTER: =FILTER(FILTER(A2:D7, B2:B7="West"), {1,0,0,1}), where the array constant marks the columns to keep.
Example 5: sorted results and a cell-driven criterion
Type a region in F1 and list its orders from highest to lowest amount.
=SORT(FILTER(A2:D7, B2:B7=F1, "No match"), 4, -1)With East in F1 the result is 1003 (2,150), 1001 (1,200) and 1005 (310). Change F1 and the list refreshes. Pair F1 with a data validation drop-down for a simple interactive report.
Tips and common mistakes
- Bracket every condition.
(B2:B7="East")*(D2:D7>1000)works; without the brackets Excel compares the wrong things. - Always supply if_empty. A blank result otherwise shows #CALC!, which looks like a mistake to readers.
- Keep the spill area clear. Anything typed below the formula blocks the spill and causes #SPILL!.
- Use Tables for growing data.
=FILTER(Sales, Sales[Region]="East")expands as rows are added. - Partial-text matches need ISNUMBER(SEARCH()). FILTER has no wildcard mode:
=FILTER(A2:D7, ISNUMBER(SEARCH("desk", C2:C7))). - Refer to the spill with #. If the formula is in F2,
=ROWS(F2#)counts the returned rows. - Blank cells in the include column return FALSE. Add
(B2:B7<>"")when you need to exclude or include them deliberately.
Errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
| #CALC! | No rows matched and if_empty was omitted. | Add an if_empty value such as “None”. |
| #SPILL! | The cells needed for the result are not empty, or the formula is inside a Table. | Clear the cells below and to the right, or move the formula outside the Table. |
| #VALUE! | include has a different number of rows from array. | Make both ranges the same height, for example A2:D7 with B2:B7. |
| #NAME? | Excel 2019 or earlier, or misspelt function name. | Use Advanced Filter or an INDEX-SMALL formula on older versions. |
| #N/A inside results | The source data contains #N/A, which FILTER passes through. | Clean the source or wrap with IFERROR. |
Practice exercise
- Using the sample table, list all Chairs orders. Expected: three rows.
- List West orders above 900 using AND logic. Expected: 1002 and 1006.
- Return the average amount of East orders by wrapping FILTER in AVERAGE. Expected: 1,220.
- Put a region name in F1 and build a sorted list of that region’s orders as in Example 5, then add a data validation drop-down to F1.
- Count how many rows your FILTER returned using ROWS and the # spill reference.
Key takeaways
- FILTER returns all matching rows as a dynamic array and updates automatically.
- Multiply conditions for AND, add them for OR, and bracket each test.
- Always set if_empty to avoid #CALC!.
- Nest FILTER inside SORT, SUM, CHOOSECOLS or UNIQUE for reports.
- Available in Excel 365 and 2021 or later; older versions need Advanced Filter or array formulas.
Related functions and lessons
- Excel Formulas and Functions course hub
- XLOOKUP: returns one match rather than every match.
- SUMIFS and COUNTIFS: totals with criteria in any Excel version.
- SEARCH: build contains-text conditions for FILTER.
- Sort and Filter course: the AutoFilter and Advanced Filter commands.
- Data Validation course: drop-downs that drive FILTER criteria.
- Microsoft Support: FILTER function
Frequently asked questions
How do I use FILTER with multiple criteria in Excel?
Put each condition in brackets and join them with an asterisk for AND or a plus sign for OR. For example, (B2:B7=”East”)*(D2:D7>1000) keeps rows that satisfy both tests, while (C2:C7=”Chairs”)+(C2:C7=”Lamps”) keeps rows that satisfy either. You can chain as many conditions as you need.
Why does FILTER return #CALC!?
#CALC! appears when no row meets the condition and you have not supplied the if_empty argument. Add a third argument such as “No match” or 0 and the formula shows that instead. If rows should have matched, check for extra spaces or text-formatted numbers in the criteria column.
Can FILTER return only certain columns?
Yes. In Excel 365 wrap FILTER in CHOOSECOLS and list the column numbers you want. In Excel 2021, filter across columns with a second FILTER whose include argument is an array constant such as {1,0,0,1}, where 1 keeps a column and 0 drops it.
Does FILTER work in Excel 2016 or 2019?
No. FILTER needs the dynamic array engine that arrived in Excel 365 and Excel 2021. In older versions, use Data > Sort & Filter > Advanced to copy matching rows to another location, or build an INDEX and SMALL array formula entered with Ctrl+Shift+Enter.
How do I filter for text that contains a word?
FILTER has no wildcard option, so use SEARCH inside ISNUMBER: =FILTER(A2:D7, ISNUMBER(SEARCH(“desk”, C2:C7))). SEARCH returns a number when the word is present and an error when it is not; ISNUMBER converts that to TRUE or FALSE. Use FIND instead of SEARCH for a case-sensitive test.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems built with these functions are available at NextGenTemplates.com.