Home > Formulas > Functions > FILTER Excel FILTER Function - Get Started Use the Excel FILTER function to get filtered lists that update automatically, if you change the source data, or the criteria. Video, written steps and free workbook

1) Intro: FILTER Function

The Excel FILTER function creates dynamic filtered lists, based on criteria, that update automatically, when the source data changes.

In this video, see how to use the new FILTER function to create a list of cities in a specific region. Next, remove duplicates with the UNIQUE function, and finally, the use SORT function to put items in alphabetical order.

• The written steps are below the video.

Tip: To see the FILTER examples, download the FILTER function examples workbook

2) FILTER Syntax

The FILTER function syntax has 3 arguments -- array, include, and if_empty (optional)

1. array - select the range of cells that you want in the results
2. include - enter one or more criteria settings
3. if_empty - (optional) enter a value to show if there are no results (result array of values is empty)

▶

Note: FILTER is one of the Excel Spill Functions (dynamic arrays) that are available in Microsoft Excel 365.

3) Ex 1: One Criterion, One Column

The first example shows how to use the new FILTER function to create a list of cities in a specific region.

For this FILTER formula:

• there is 1 criterion -- Region name entered in cell B4
• result is in 1 column - list of City names from specified region
• City and Region are columns in the table named Sales_Data

3.1) Formula for One Criterion One Column

The following formula is entered in cell D4, and includes a cell reference to cell B4:

• =FILTER(Sales_Data[City], Sales_Data[Region]=B4, "--")

The 3 arguments in that formula are:

1. array - Sales_Data[City] -- Use City column as source data array
2. include - Sales_Data[Region]=B4 -- for cities in the specified region
3. if_empty - "--" - show 2 dashes if no matching records are found
   • Tip: Use an empty string ("") as the last argument, if you want the result cell appear blank

▶

Note: If you don't use the if_empty argument, and no results are found for the FILTER function, the formula will return a #CALC! error.

3.2) Results for One Criterion One Column

If there are NO records for the selected region, the formula result is two dashes (the if_empty setting), because there was no matching data

However, in the screen shot below there ARE records for the selected region - Southwest

• FILTER formula is in cell D4, where the first City name is shown
• Filter returns more than one record from the selected region, so the remaining results spill down, into the cells below, as far down as needed
• City names are listed in the order they appear in the data
• Some City names are repeated, because they are in the Sales_Data table multiple times.
  • This will be fixed by following the steps in the Edit FILTER Formula section, below

3.3) Cells in the Spill Range

The cells with formula results are called the Spill Range.

If any cell in the spill range is selected:

• There is a thin blue border around the outside of the spill range
• The formula can be seen in the Formula bar
  • If cell D4 is selected, the formula is in black font
  • If any other cell in the spill range is selected, the formula is in black font

For more details, see this Microsoft article on spilled array behaviour, for FILTER and other dynamic array functions

4) How Does FILTER Function Work?

Here's the FILTER function formula, from Example 1, shown above:

• =FILTER(Sales_Data[City], Sales_Data[Region]=B4, "--")

The formula is in cell D4, and refers to the Region name entered in cell B4

4.1) Filter Function Arguments

And here's how the first two arguments in that FILTER formula work:

• 1st argument tells Excel to filter data from the City column in the Sales_Data table
  • Sales_Data[City]
• 3rd argument tells Excel to show 2 dashes if no matching records are found
  • "--"

The powerful work is done in the 2nd argument, where the criterion is entered:

• the formula tests each row in the table, to see if Region name is equal to the specific text of the region name in cell B4
  • Sales_Data[Region]=B4
• For each row, the result of the comparison is a Boolean value - TRUE or FALSE
  • If the logical test result is TRUE, city name from that row will be included in filter results
  • If the logical test result is FALSE, city name from that row will NOT be included in filter results

▶

Warning: If the result of any comparison is not a Boolean value, the FILTER formula result will be an error value

4.2) Formula Bar Demonstration

To see the TRUE and FALSE results, I did a quick test of the FILTER formula

1. First, I selected cell D4 on the worksheet, where the formula is entered
2. Next, in the formula bar, I selected the second argument:
• Sales_Data[Region]=B4
3. After that, I pressed the F9 key on my keyboard, to calculate just the selected part of the formula.

In the screen shot below, highlighted in yellow, you can see the first few results in the calculated section.

• Results are a Boolean array: {FALSE; TRUE; TRUE
• Array starts with a curly bracket
• Values are separated by semi-colons
• At the end of the array (not shown), is a closing curly bracket

4.3) TRUE and FALSE Results

To confirm those results, you can compare the Boolean array with the Sales Data records:

1. FALSE: First sales order is NOT from the Southwest region
• City name from that row, Charleston, WILL NOT be included in filter results.
2. TRUE: Second sales order IS from Southwest region.
• City name from that row, Austin, WILL be included in filter results.
3. TRUE: Third sales order IS from Southwest region
• City name from that row, Santa Fe, WILL be included in filter results.

4.4) Safely Exit Formula Cell

After calculating part of a formula, press the Esc key on your keyboard, to exit the cell, without saving the calculation. Otherwise, the calculated results will be saved in the formula

▶

Tip: If you accidentally press the Enter key, instead of Esc: You'll see the boolean array in the formula, instead of the criteria test. Press the Undo shortcut immediately -- Ctrl+Z -- to fix the formula

5) Edit FILTER Formula

In the next section, the FILTER formula will be edited, to create a list where each city name only appears once.

If you want to make changes to the FILTER formula, start with these steps:

• Select cell D4, where the FILTER formula was entered
• With cell D4 selected, the formula shows in the Formula bar, in black font
  • You will be able to make changes to the formula in the Formula bar

5.1) Other Cells

If any other cell in the spill range is selected (not cell D4):

• The formula can be seen in the Formula bar, but it is in grey font (dimmed out)
• If you click in the Formula bar, the formula disappears
• You cannot edit the formula in the "spill" cells
• You can only change it in the top cell, where the formula was entered

5.2) Create a Unique Sorted List

In some cases, when using the FILTER function, it will be acceptable to see items listed multiple times.

However, for this example, we want a list of the cities in the selected region, with these enhancements:

• Unique: show each city name only once
• Sorted: show the city names in alphabetical order - A to Z

To add those enhancements, follow the instructions below, to add two more functions to the formula.

5.3) Unique List of Names

To get a list where each city name only appears once, follow these steps to add the UNIQUE function to the formula:

• Select cell D4, where the formula was entered
• Click in the Formula bar, to start editing the formula
• Click to the right of the equal sign
• Type the UNIQUE function name, and an open bracket: UNIQUE(
• Click at the end of the formula, and type a closing bracket
• Press Enter, to complete the change

Here is the revised formula, to create a unique list of city names, from the matching records:

• =UNIQUE(FILTER(Sales_Data[City], Sales_Data[Region]=B4, "--"))

The result is a list of the unique city names in the selected region.

5.4) Sorted Unique List of Names

To sort the list of unique city names, follow these steps:

• Select cell D4, where the formula was entered
• Click in the Formula bar, to start editing the formula
• Click to the right of the equal sign
• Type the SORT function name, and an open bracket: SORT(
• Click at the end of the formula, and type a closing bracket
• Press Enter, to complete the change

Here is the revised formula, to create a unique list of city names:

• =SORT(UNIQUE(FILTER(Sales_Data[City], Sales_Data[Region]=B4, "--")))

The result is a sorted list of the unique city names (A to Z) in the selected region.

6) More Filter Examples

To see more examples of using the FILTER function, the following pages, here on the Contextures site:

6.1) FILTER Function - Advanced Examples

For advanced FILTER function examples, with written steps and a video, go to the FILTER Function Advanced Examples page.

6.2) Hide Used Employee Names

After an employee name has been used in the "On Call" list, the FILTER function prevents it from appearing in the selection list. Employee List - Dynamic Arrays example

6.3) Region Employees Drop Down List

Select a region name from one drop down, and the FILTER function creates a dynamic list of employees from that region. Dependent Drop Down from Dynamic Arrays

6) Get the Sample File

• FILTER Examples: To see the FILTER examples, download the FILTER function examples workbook, which contains food sales data (described below). The file is zipped, and is in Excel xlsx format, with no macros.

6.1) Food Sales Data

In the FILTER function examples shown on this page, the original data set is stored in a food sales table, named Sales_Data. These are food product orders, for an imaginary food company.

In the Sales_Data table:

• There are 9 columns of data, including 1 column with a calculation.
• There are 244 rows of data in the food sales table.

Each row in the food sales range of data shows values for the following fields:

• Date: Date the order was placed
• Region: geographic region where order will be shipped
• City: city where order will be shipped
• NameL: last name of sales representative
• Category: product category - Bars, Cookies, Crackers or Snacks
• Product: product name
• UnitPrice: product selling price per unit
• Qty: number of units ordered (quantity)
• SalePrice: total price of order - calculation - Qty x UnitPrice

The FILTER function examples shown above will pull data from the Sales_Data table, based on one criterion, or multiple criteria.

▶

Tip: I use the comma separator in the examples above. For some regional settings, you might need a semi-colon separator

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Functions Tutorials

FILTER Function Advanced Examples

Interactive FILTER Example

Excel Filter for List Items

FILTER Function Lookup

FILTER Function Reports

Spill Function Examples

Dynamic Drop Down Lists

Named Excel Tables