To effectively use the Excel FILTER function, here’s a straightforward breakdown of its components (optional parameters are in brackets):

= FILTER(array, include, [if_empty])
  • array – This is the data set or portion of it that you want to analyze.
  • include – This criterion determines which records to include in your results. Think of it as setting rules for what to keep.
  • [if_empty] – This optional parameter specifies what to show if no matches are found, like “No Data”. Without it, you might see a #CALC! error if no records meet your criteria.

Next, let’s explore some practical examples of how to apply the FILTER function.

Basic Excel FILTER Formula

Imagine you have a dataset ranging from cells A2 to C17, and you want to find all entries that match a specific region, say “Asia,” which you’ve noted in cell G2.

Here’s a simple way to do this:

  • In cell F4, enter the following Excel FILTER Formula:
= FILTER(A3:C17, B3:B17=G2)
  • This FILTER formula will show all records from A3 to C17 where the B column matches “Asia” in G2.
filter function excel

No Matching Data: #CALC! Error

If the region selection in cell G2 is empty or includes a region that doesn’t exist, you’ll get a #CALC! error message.

To fix this, modify the formula in F5 to:

= FILTER(A3:C17, B3:B17=G2, "Not Found").
filter function excel

How FILTER Function Works

The FILTER function in Excel uses a “boolean array” to decide which data to show based on a logical test. The function checks each piece of your data to see if it meets a certain condition. This test results in an array of true or false values:

  • True for data that meets the condition (for example if ‘Region’ equals ‘Asia’).
  • False for data that doesn’t.

You can see that in action when you select cell F5 and highlight the portion of the formula that represents the include argument (B3:B17=G2). Press the F9 key to see the true/false outcomes.

Excel filter boolean array

FALSE responses represent records where the region was not a match to the selected region (not equal to ‘Asia’). TRUE responses represent records where the region is a match to the selected region (equal to ‘Asia’).

Excel filter boolean array

❗ Don’t forget to press ESC to avoid keeping it as a hard-coded value in your formula.

The F9 key is great for debugging formulas. If you are writing complex formulas and need to debug errors or track logic flow, the F9 key is a key tool in Excel for this.

If you have Excel for Microsoft 365 it’s even easier: You can take advantage of the Evaluate Formula dialog, which lets you check the current value of each part of your formula. Simply select the part of the formula you want to evaluate and you’ll see the tooltip that appears and shows the current value of that parameter.

FILTER by Certain Value

Starting in cell F5, we wish to display all the records from our data set (cells A3:C17) that have a revenue greater than the value defined in cell H2 (i.e. 45,000).

  • Select cell F5 and enter the following Excel FILTER formula:
= FILTER(A3:C17, C3:C17>$H$2, "Not found")
  • This formula will display all records from cells A3 to C17 where the revenue in column C exceeds the value in H2.

Test Dynamic Updating:

Change the value in H2 to “3,000” to see how the displayed records adjust accordingly, now showing all records where the revenue is above 3,000.

#Spill Error in Excel

The Excel FILTER function adjusts its output dynamically based on the conditions applied. For example, a threshold of 45,000 might show only four results. But, a threshold of 3,000 could reveal many more.

The results of the FILTER function are displayed in what’s known as the spill range. If this range is blocked by existing data or hidden elements, you might encounter a #SPILL! error. This error happens because the function cannot show all its results. It’s due to these obstructions.

For instance, imagine you have text in the cells below. This text is right below where the results of a FILTER formula should appear.

If you set the revenue threshold in cell H2 to ‘3,000’, the formula will try to extend its results into these cells. Excel will signal a #SPILL! error and highlight the cells that need to be cleared for the results to display properly. 

Once we remove these obstructing cells, the full results of the FILTER formula will show.

FILTER with Table References

Typically, we start with what are known as “plain tables”—simply typed data in a spreadsheet without any advanced formatting or functionality. By converting these into proper Excel Data Tables, we unlock a variety of features that can significantly enhance how we work with data in Excel.

Using Structured References with Excel Tables

To convert a plain table into a Data Table, select the table and go to the Home tab. Then click Styles group and choose Format as Table, or simply press Ctrl + T.

This conversion activates a new set of tools under the Table Tools Design ribbon, which appears only when the table is selected.

For our example, we have a data table that we have named “TableDiv2”.

A key difference with Data Tables is how we reference columns. In plain tables, clicking on a cell shows a traditional cell reference like A1.

In contrast, Data Tables use structured references, like =TableDiv2[Region] or =[Region]. These references are more intuitive as they use column names rather than cell addresses.

Data Tables also offer “sweet spots” for selecting data—a single click can select a cell, a column, or the entire table, depending on where you click.

Examine the below table and the click locations.

Now let’s see what Excel provides as a reference both in a plain table and a data table.

To verify if a plain table has been converted into a Data Table, click on it and look for the Table Tools Design ribbon. If it appears, your table is an Excel Data Table.

Remember, just because a table looks formatted doesn’t mean it’s a Data Table—it might just be styled manually without the added functionality.

FILTER Function using Structured References

Let’s apply the FILTER function using structured references in a practical scenario:

We want to filter records from our dataset spanning cells A3:C17 to only show entries that match the region specified in cell G3 (e.g., “Australia”).

Select cell F5 (as this is where you want the results to start displaying). Enter the following FILTER formula:

= FILTER(TableDiv2, TableDiv2[Region]=G3, “not found”)

💡When referencing the table in your formula, locate the “sweet spot” in the upper-left corner of the table to reveal a thick diagonal arrow. A single click here selects the entire table, regardless of its size.

Similarly, finding the “sweet spot” above a column heading reveals a thick down arrow, allowing you to select the entire column’s data.

Testing the Automatic New Record Detection Feature

One of the benefits of using table references over traditional cell references is the automatic detection of new records.

As you add a new entry to the table under the Division column, the table grows to include this new record. The FILTER function updates to reflect this change.

Filter with Multiple Criteria

Using multiple criteria in the FILTER function might seem complex at first. The trick is all about setting up the right conditions to create logical expressions that use Boolean algebra.

Let’s look at an example:

Suppose you want to filter records based on two criteria:

  • Division should be “Game” and
  • Region should be “Asia”.
excel filter function multiple criteria

The Excel table that includes our source data is called TableDiv3. Here’s how you can set this up:

Setting Up Criteria:

  • Enter “Game” in cell G3 as the comparison value for the Division.
  • Enter “Asia” in cell G4 as the comparison value for the Region.

Entering the FILTER function with multiple criteria:

  • Select cell F7 and input the following FILTER formula:
= FILTER(TableDiv3, (TableDiv3[Division]=G3) * (TableDiv3[Region]=G4), "Not found")

This formula checks for records where the Division equals “Game” and the Region equals “Asia”. The multiplication operator (*) serves as an AND function, meaning both conditions must be true for a record to be included.

  • You will see records that match both “Game” in the Division and “Asia” in the Region.
  • Changing the values in cells G3 or G4 to different Division or Region values will update the results accordingly.
  • If you search for a combination that doesn’t exist, such as “Health” for Division and “Europe” for Region, the formula will return “Not found”.

Tips for Using FILTER Function with Multiple Criteria

When filtering data in Excel with multiple conditions, it’s important to understand how to set up your formula correctly:

Avoid Traditional AND/OR Functions:

  • Instead of using Excel’s standard AND or OR functions, which don’t work with arrays, you’ll need to use a different approach for multiple conditions.

Use Proper Syntax:

  • Enclose each condition in its own set of parentheses to keep things organized and clear.
  • Connect these conditions using symbols: use an asterisk (*) for AND (to meet all conditions) and a plus sign (+) for OR (to meet any conditions).
  • AND Condition: Data that meets both of two conditions
= FILTER(DataRange, (Condition1) * (Condition2))
  • OR Condition: For data that meets either condition:
= FILTER(DataRange, (Condition1) + (Condition2))

If you’re looking to dive deeper into using the Excel FILTER function multiple criteria (AND/OR) and how to selectively return columns, check out our detailed article.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel FILTER Function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.