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: FILTER is one of the Excel spill functions (dynamic arrays) that are available in Microsoft 365 plans. See more dynamic array formulas on the Spill Function Examples page |
Intro: FILTER FunctionThe 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 SyntaxThe FILTER function syntax has 3 arguments -- array, include, and if_empty (optional) Note: The comma separator is used in these examples. For other regional settings, a semi-colon separator might be required.
|
In the first four 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, and you can get this data in the Download section, at the bottom of this page.
In the Sales_Data table:
Each row in the food sales range of data shows values for the following fields:
The FILTER function examples shown below will pull data from this table, based on one criterion, or multiple criteria.
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:
The following formula is entered in cell D4, and includes a cell reference to cell B4:
The 3 arguments in that formula are:
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.
If there are NO records for the selected region, the formula result is two dashes (the if_empty setting).
However, in the screen shot below there ARE records for the selected region - Southwest
The cells with formula results are called the Spill Range.
If any cell in the spill range is selected:
For more details, see this Microsoft article on spilled array behaviour, for FILTER and other dynamic array functions
Here's the FILTER function formula, from Example 1, shown above:
The formula is in cell D4, and refers to the Region name entered in cell B4
And here's how the first two arguments in that FILTER formula work:
The powerful work is done in the 2nd argument, where the criterion is entered:
Warning: If the result of any comparison is not a Boolean value, the FILTER formula result will be an error value
To see the TRUE and FALSE results, I did a quick test of the FILTER formula
In the screen shot below, highlighted in yellow, you can see the first few results in the calculated section.
To confirm those results, you can compare the Boolean array with the Sales Data records:
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:
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:
If any other cell in the spill range is selected (not cell D4):
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:
To add those enhancements, follow the instructions below, to add two more functions to the formula.
To get a list where each city name only appears once, follow these steps to add the UNIQUE function to the formula:
Here is the revised formula, to create a unique list of city names:
The result is a list of the unique city names in the selected region.
To sort the list of unique city names, follow these steps:
Here is the revised formula, to create a unique list of city names:
The result is a sorted list of the unique city names (A to Z) in the selected region.
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:
Here is the revised formula in cell D4:
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.
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(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(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.
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,
Previous examples only had one criterion in the FILTER function's include argument:
In this example, there are 2 criteria
To use multiple criteria in the FILTER function,
Here is the include argument for the Example 3 FILTER function:
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.
Note: There is only one multiplication operator in this formula, but you can add more, if needed, to create additional criteria tests.
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,
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:
And here is the completed formula in cell D4, returning the list of formatted dates
To see more examples of using the FILTER function, these pages, here on the Contextures site:
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
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
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.
Last updated: July 23, 2023 3:24 PM