Excel FILTER Function Examples

These Excel FILTER function examples show how to create dynamic filtered lists that update automatically, when the source data changes. Video, written steps and free workbook

NOTE: Spill functions (dynamic arrays) are available in Microsoft 365 plans.

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, you'll see three FILTER function examples, to help you get started with this powerful Excel function. The written instructions are below the video.

FILTER Syntax

The FILTER function 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

Food Sales Data

In these FILTER function examples, the data is in a food sales table, named Sales_Data.

The following examples will return values from one or more columns, based on values in other columns.

Ex 1: Cities in Region

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

On the filter results sheet, there is a region name in cell B4.

In cell D4, this formula returns a list of cities in the selected region, from the City column in the Sales_Data table.

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

The 3 arguments in that formula are:

1. array - Sales_Data[City]
2. include - Sales_Data[City]
3. if_empty - "--"

If there are NO records for the selected region, the formula result is two dashes (the if_empty setting).

If there ARE records for the selected region, the formula result is a list of all the city names from that region, in the food sales table.

• The formula is in cell D4, and the results spill into the cells below, as far down as needed
  • There is a thin blue border around the spill area
  • If a spill cell is selected, the formual can be seen in the Formula bar, in grey font
  • The formula can only be edited in the top cell, where it was entered (D4)
• Names are listed in the order they appear in the data
• There might be multiple instances of the city names.

Create a Unique Sorted List

In most cases, the preferred result would

• show each item only once
• show the items in alphabetical or numerical order.

To get that result, first add the UNIQUE function to the FILTER formula:

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

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

Next, add the SORT function to the FILTER formula:

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

Now the city names are sorted in alphabetical order.

Ex 2: City and Sales Rep

The second example is similar to the first one, but the results will spill into 2 columns, to show the cities and sales reps for the selected region.

To do this, we'll change the first argument in the FILTER function:

• Select the table reference (Sales_Data[City]) in the first argument, and delete it
• Go to the FoodSales sheet, and select the City and NameL columns (data only, not the headings)
• Press Enter, to complete the change

Here is the revised formula in cell D4:

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

There are 2 columns in the formula's spill cells, showing the city names and sales rep names. City names are in A-Z order, but the sales rep names are not sorted.

To fix that, we'll add a second SORT function, so both columns are sorted.

First, we'll add the optional arguments for the existing SORT function - sort index and sort order.

This will be the SORT for the sales rep names, which should be sorted after the city names are in alphabetical order. So, its

• sort index is 2
• sort order is 1 (Ascending)

=SORT(UNIQUE(FILTER(Sales_Data[[City]:[NameL]], Sales_Data[Region]=B4, "--")) ,2,1)

Next, add another SORT function at the start of the formula, for the City column. It should be sorted first, so its

• sort index is 1
• sort order is 1 (Ascending)

=SORT(SORT(UNIQUE(FILTER(Sales_Data[[City]:[NameL]], Sales_Data[Region]=B4, "--")) ,2,1)) ,1,1)

Now the cities are sorted in ascending order, and for each city, the sales reps are sorted in ascending order.

Ex 3: Region and Price

The third example will show product names that were sold in the selected region, with the price greater than a set amount.

On the filter results sheet,

• There is a region name selected from the drop down list in cell B4
• There is a minimum price type in cell B7

Multiple Criteria for FILTER Function

Previous examples only had one criterion in the FILTER function's include argument:

• Sales_Data[Region]=B4

In this example, there are 2 criteria

• Region name = B4
• Price > B7

To use multiple criteria in the FILTER function,

• enclose each criterion in round brackets
• use the multiplier operator (*) between the criteria

Here is the include argument for the Example 3 FILTER function:

• (Sales_Data[Region]=B4) *(Sales_Data[SalePrice]>B7)

And here is the completed formula in cell D4, to create a list of product names for the selected region, with a price greater than the set amount.

• =SORT(UNIQUE(FILTER(Sales_Data[Product], (Sales_Data[Region]=B4) *(Sales_Data[SalePrice]>B7), "--")))

Ex 4: Dates as Year and Month

The fourth example will show a list of months (yyyy-mm) when there were sales in the selected region, with the price greater than a set amount.

Just like the third example, this formula has 2 criteria in the FILTER function,

• (Sales_Data[Region]=B4) *(Sales_Data[SalePrice]>B7)

Format the Dates

In this formula the FILTER function will return items from the Date field. However, we don't want a long list of specific dates. Instead, we want a list of the months, formatted as yyyy-mm

To get that result, use the Excel TEXT function -- it formats numbers based on your specifications. Here is the array argument in the FILTER function, with the TEXT function formatting the results:

FILTER(TEXT(Sales_Data[Date],"yyyy-mm")

And here is the completed formula in cell D4, returning the list of formatted dates

• =SORT(UNIQUE(FILTER(TEXT(Sales_Data[Date],"yyyy-mm"), (Sales_Data[Region]=B4) *(Sales_Data[SalePrice]>B7), "--")))

More Filter Examples

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

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

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

Download the Sample Files

Four Examples: To see the four FILTER examples, download the FILTER function examples workbook. The file is zipped, and is in Excel xlsx format, with no macros.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

More Functions Tutorials

Functions List

FILTER Function Lookup

Spill Function Examples

Dynamic Drop Down Lists

Named Excel Tables