Exploring Excel’s dynamic FILTER function

Q. How do I use the FILTER function in Excel, and how is this an improvement over the filter feature?

A. The FILTER function was introduced five years ago as part of the Excel Dynamic Arrays rollout. The function has all the filtering power of the venerable filter feature but can perform more tasks, including setting up automated filters and showing results where the user wants them to be shown. Currently, if you filter a dataset, the results are displayed in the same set of rows and columns where the raw data was located. However, the Filter function can be placed anywhere in the workbook.

The function also can be used along with other functions and formulas, which makes it capable of performing some amazing feats.

So how does the FILTER function work? Let’s walk through some examples. To follow along, download this Excel workbook. A video demonstration also is available at the bottom of this article.

Note that the walk-through and video were made using Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

The screenshot below shows the spreadsheet used for this demonstration. Note that this is just a snippet of the entire spreadsheet.

First, let’s do a simple filter on the month (Mon) to show only transactions from June ( Jun). There are three arguments to define for FILTER: Array, Include, and If_empty. The first two arguments are required, and the third argument is optional. The input for the Array argument is the entire range of cells, or array, to filter. The input for the Include argument is the range of cells that contains the condition or conditions to be filtered. The input for the If_empty argument is anything you want to be displayed if the filter does not return anything. If you leave this argument blank, an error message will be returned if there are no results.

For this example, I copied the table headers in columns L:S to place my filtered data. I also put Jun, the month I want to use as my filter, in cell J2. You can either type the filter into a cell, or you can create a drop-down list for all months.

The Array is A2:H151, the cell range that holds all the data. Include is the column that includes the months in my array = J2, which is the condition I am looking for in that column. Therefore, it appears as G2:G151=J2. The If_empty argument is “” (quote quote), instructing Excel to leave the cell blank if nothing is returned in the filter. The finished formula entered into cell L2 is shown below.

This formula entered into cell L2 will then “spill” over into the appropriate surrounding cells to show the dataset for only the month of June, as shown in the screenshot below.

Let’s do another example, this time with two conditions (the FILTER function also can handle three or more conditions). In this case, we want the data to be filtered to show information that is from the month of June (Jun) AND the marketing (MKTG) department. I include MKTG in cell J5. The Array is A2:H151, just as it was in the first example. The Include argument is (G2:G151=J2)*(D2:D151=J5). Note that the FILTER function requires parentheses around each condition if there is more than one. Also, the *sign must be included to instruct Excel that both conditions must be met to show the data in the filtered dataset. The If_empty argument is “”, just as it was in the previous example. The finished formula entered into cell L2 is shown below.

See our new filtered data in the screenshot below.

For the third example, I want to see accounts from the month of June OR the marketing department displayed in the filtered data set. The Array and If_empty are unchanged from the previous (AND) example. The only difference is a + sign is used instead of the * sign. Thus, the Include argument now reads (G2:G151=J2)+(D2:D151=J5). Entering the finished formula into cell L2 produces the updated filtered dataset shown in the screenshot below.

The last example includes multiple conditions and used both AND and OR in the criteria. The goal is to filter the data to show the information that is from the month of June OR the marketing department AND has a budget of more than $10,000. The Array remains A2:H151, and If_empty argument is, again, “”.

The Include argument now is ((G2:G151=J2)+(D2:D151=J5))*(H2:H151>10000). Notice the extra parentheses around the portion of the argument that includes columns G and D. These are required to show Excel that these two things create the OR condition and must be kept together.

As for the rest of the argument, I can’t just reference a cell because the goal is to find all accounts with a budget exceeding $10,000. This is handled by including >10000 in the argument instead of using a cell reference. The finished formula entered into cell L2 and the updated filtered dataset are shown below.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.