How to Use the FILTER Function in Excel: Syntax and Examples

shape
shape
shape
shape
shape
shape
shape
shape
How to Use the FILTER Function in Excel: Syntax and Examples - featured image

The FILTER function returns only the rows of a table that meet your conditions, and the results update automatically when the data changes. It replaces many manual steps, such as using the filter buttons, copying results to another sheet and repeating the work each week. FILTER is a dynamic array function, so a single formula fills as many cells as it needs. This guide explains the syntax, shows examples with a small sales table that you can check by hand, covers AND and OR conditions, sorting the results, handling the #CALC! error when nothing matches, combining FILTER with SUM and COUNT, and the mistakes that cause #SPILL! errors.

Updated November 2026

In this guide

  1. FILTER syntax
  2. The sample data
  3. Example 1: filter by one condition
  4. Example 2: filter by a number
  5. Example 3: AND conditions
  6. Example 4: OR conditions
  7. Example 5: sort the results
  8. Example 6: totals from filtered data
  9. Handle no matches with if_empty
  10. Why FILTER returns #SPILL!
  11. Build a dynamic sales report with FILTER
  12. FILTER compared with the filter button and COUNTIFS
  13. Practice problems
  14. Common mistakes
  15. Quick recap

FILTER syntax

The syntax is =FILTER(array, include, [if_empty]). Array is the range you want to return, include is a test that gives TRUE or FALSE for each row, and if_empty is an optional message when no rows match. Microsoft describes the FILTER function and lists support for Excel for Microsoft 365, Excel 2021 and Excel 2024, and the mobile apps. Older versions such as Excel 2019 do not have it, so check your version before you send a workbook to a client.

The sample data

All examples use this table, with the headings in row 1 and the data in A2:C7.

RegionRepAmount
EastAli1200
WestSara800
EastOmar1500
NorthHina600
WestBilal950
EastZain700

Example 1: filter by one condition

To list only the East region, use =FILTER(A2:C7,A2:A7="East"). Excel returns three rows: Ali 1200, Omar 1500 and Zain 700. You enter the formula in one cell, and the results spill into the cells below and to the right. The results are linked to the source, so if you change Sara’s region to East, the list grows to four rows without any extra work. To make the condition flexible, put the region in a cell, such as F1, and use =FILTER(A2:C7,A2:A7=F1). Combine it with a drop-down list in F1 to build a simple report where the user chooses a region.

Example 2: filter by a number

To show all sales above 900, use =FILTER(A2:C7,C2:C7>900). The result has three rows: Ali 1200, Omar 1500 and Bilal 950. Use >= for at least, and <> for not equal. To show only the names, change the array to just column B: =FILTER(B2:B7,C2:C7>900), which returns Ali, Omar and Bilal in a single column. Choose the smallest array that you need, because it keeps the result tidy.

Example 3: AND conditions

For AND logic, multiply the conditions, since TRUE times TRUE is 1. To find East sales above 1000, use =FILTER(A2:C7,(A2:A7="East")*(C2:C7>1000)). The result has two rows: Ali 1200 and Omar 1500. Zain is East but only 700, so he is left out. Wrap each condition in its own brackets, because a missing bracket gives wrong results. You can multiply as many conditions as you need, such as region, rep and a date range.

Example 4: OR conditions

For OR logic, add the conditions, since 1 plus 0 is 1. To show East or North, use =FILTER(A2:C7,(A2:A7="East")+(A2:A7="North")). The result has four rows: Ali, Omar, Hina and Zain. In rows that meet both conditions the sum is 2, which still counts as TRUE, so the row is returned once. Another way for a list of values is to use ISNUMBER and MATCH: =FILTER(A2:C7,ISNUMBER(MATCH(A2:A7,{"East","North"},0))), which is easier to read when there are many values.

Example 5: sort the results

Combine FILTER with SORT to order the output. =SORT(FILTER(A2:C7,A2:A7="East"),3,-1) returns the East rows sorted by the third column, Amount, in descending order: Omar 1500, Ali 1200, Zain 700. The 3 is the column number inside the filtered result, and the minus 1 means descending. This pattern is the basis for top-N lists. For the top three sales overall, use =TAKE(SORT(A2:C7,3,-1),3) in a version that supports TAKE, which returns Omar 1500, Ali 1200 and Bilal 950.

Example 6: totals from filtered data

You can feed FILTER into other functions. The East total is =SUM(FILTER(C2:C7,A2:A7="East")), which returns 3,400, the sum of 1200, 1500 and 700. The count of East rows is =ROWS(FILTER(A2:C7,A2:A7="East")), which returns 3. The average is =AVERAGE(FILTER(C2:C7,A2:A7="East")), which returns 1,133.33. For simple totals, SUMIFS is often shorter, as shown in our SUMIFS guide, but FILTER is more flexible when you need the full list of matching rows as well.

Handle no matches with if_empty

If no row meets the condition, FILTER returns a #CALC! error, because Excel does not support empty arrays. Use the third argument to show a friendly message: =FILTER(A2:C7,A2:A7="South","No sales"). The cell then shows No sales instead of an error. Always use if_empty in reports that clients will open, because a new filter value may not exist yet. You can also return an empty text with two quotation marks, which looks cleaner in a dashboard.

Why FILTER returns #SPILL!

A #SPILL! error means that the cells where the results should appear are not empty. Clear the cells below and to the right of the formula, and the results will appear. Merged cells and Excel tables also block spilling, because tables do not allow dynamic arrays. If you need the result inside a table, put the FILTER formula outside of it. If you want to refer to the whole result in another formula, use the spill reference, which is the cell address followed by a hash sign, such as F3#. This reference grows and shrinks with the result.

Build a dynamic sales report with FILTER

A practical use is a one-page report that a manager can control. Put a region drop-down in F1, a minimum amount in F2 and the FILTER formula in F4: =FILTER(A2:C7,(A2:A7=F1)*(C2:C7>=F2),"No sales"). Above the table, show a summary line with the count =IFERROR(ROWS(F4#),0) and the total =SUM(INDEX(F4#,0,3)). When the manager picks East and sets the minimum to 1000, the report shows Ali 1200 and Omar 1500, a count of 2 and a total of 2,700. Change the drop-down to West with a minimum of 900, and the report shows Bilal 950, a count of 1 and a total of 950. No macros are needed, and the report is always current. Add conditional formatting to highlight the largest amount, and freeze the header row so that long results stay readable. If the report is for a client, protect the sheet so that only the two input cells can be changed, and add a short instruction line next to them. Test the report with an input that returns no rows, such as South, to make sure the message appears and the total shows zero rather than an error.

FILTER compared with the filter button and COUNTIFS

The filter button hides rows in the original data, so it does not create a separate report, and you must reapply it when the data changes. FILTER creates a new range that updates by itself, and it leaves the original data untouched. COUNTIFS and SUMIFS return only a number, not the rows. Use FILTER when you need the rows, use COUNTIFS or SUMIFS when you need a number, and see our COUNTIFS guide for counting with several conditions. For lookups of a single value, VLOOKUP or XLOOKUP is still a better choice, and our VLOOKUP troubleshooting guide covers common errors.

Practice problems

Using the sample table, try these. Problem 1: list the rows where the amount is below 800. The answer is Hina 600 and Zain 700. Problem 2: list the West rows. The answer is Sara 800 and Bilal 950. Problem 3: total the West sales, which is 1,750. Problem 4: list rows where the region is East and the amount is at least 1200. The answer is Ali 1200 and Omar 1500. Problem 5: count the rows that are not East, which is 3. Check each by scanning the table.

Common mistakes

The most common mistake is to use a include range of a different size from the array, which causes #VALUE!. Another is to use AND or OR functions inside the include argument, since they return a single result instead of an array, so use multiplication and addition instead. A third is to leave out the brackets around each condition. A fourth is to expect FILTER to work in Excel 2019 or earlier. Finally, remember that text conditions are not case sensitive, and that numbers stored as text will not match numbers, so fix the data first as shown in our guide to converting text to numbers in Excel.

Quick recap

FILTER returns the rows that meet your conditions and updates when the data changes. Use a comparison for one condition, multiply conditions for AND and add them for OR. Wrap it in SORT to order the results, in SUM, ROWS or AVERAGE to get totals, and use the third argument to avoid #CALC! when nothing matches. Keep the cells below the formula empty to avoid #SPILL!, and remember that FILTER needs Microsoft 365, Excel 2021 or Excel 2024. Test every report with one input that returns no rows and one that returns all rows, so you know it behaves in both extremes before the client sees it.

Frequently asked questions

What does the FILTER function do?

It returns the rows of a range that meet one or more conditions, and updates automatically.

How do I use two conditions in FILTER?

Multiply the conditions for AND, and add them for OR.

Why does FILTER show #CALC!?

No rows match. Use the if_empty argument.

Which Excel versions have FILTER?

Microsoft 365, Excel 2021 and Excel 2024, plus the mobile apps.

Why do I get #SPILL!?

The cells where the result should appear are not empty.

Related guides and free tools

Sources: FILTER function, Microsoft Support

Leave a Reply

Your email address will not be published. Required fields are marked *