Home > Formulas > Functions > FILTER Excel FILTER Function Examples These Excel FILTER function examples show advanced formulas for dynamic filtered lists that update automatically, if you change the source data or criteria. Video, written steps and free workbook Also see: Getting Started with FILTER Function

1) Video: FILTER Function - 3 Examples 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. Also see: Getting Started with FILTER Function Video Timeline: 00:00 Introduction 00:24 Cities in Selected Region 03:45 Cities and Sales Reps 06:01 FILTER with 2 Criteria Tip: To see the FILTER examples, download the FILTER function examples workbook

2) Ex 1: One Criterion, 2 Columns

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:

• Start with a copy of the completed FILTER formula from Example 1
  • =SORT(UNIQUE(FILTER(Sales_Data[City], Sales_Data[Region]=B4, "--")))
• 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, "--")))

Now 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.

2.1) Add SORT Function Optional Argument

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

But 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)

2.2) Add a Second SORT Function

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.

3) Ex 2: Two Criteria, 1 Column

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
• In cell B2, the FORMULATEXT function shows the formula from cell D4
• Labels are typed in cells B3, D3 and B6
  • Those labels are not used in the formulas

3.1) 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

1. Region name = B4
2. Price > B7

To use multiple criteria in the FILTER function,

• enclose each criterion in round brackets
• use the asterisk multiplier operator (*) between the criteria, to test if a record meets BOTH criteria
  • for the FILTER criteria, the asterisk operator means AND
  • to be included in the filter results, record must be in selected region AND price must be greater than set amount

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), "--")))

Note: There is only one multiplication operator in this formula, but you can add more, if needed, to create additional criteria tests.

4) Ex 3: Dates Formatted YYYY-MM

Instead of pulling unformatted data from the source data set, you can add formatting within the FILTER function formula.

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)

4.1) 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. That will group the individual dates by year and month.

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), "--")))

5) More Filter Examples

To see more examples that use the FILTER function, click the links below.

5.1) Filter By Partial Match

Select up to 3 criteria from the drop down lists. The FILTER formula returns a list of records where there is a partial match for those criteria, in the Day, Item and Name columns. Click to get the Excel Partial Match Filter sample file.

5.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

5.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

• Four 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 Get Started

Interactive FILTER Example

Excel Filter for List Items

FILTER Function Lookup

FILTER Function Reports

Spill Function Examples

Dynamic Drop Down Lists

Named Excel Tables