Filtering to extract data based on matching criteria is a traditionally hard problem in Excel. However, the new FILTER function makes this task much easier. The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains names, scores, and groups. Our goal is to use the FILTER function to filter the data by group. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “filter”. Once we have a match, I’ll press the TAB key. The first argument is called “array”. This is the data we want to filter. In this case, that’s the range B5:D14. Next, we need to provide an argument called “include”. The include argument is actually what does the filtering. This needs to be a boolean array, or an expression that creates a boolean array, with a size compatible with the data we are filtering. In this case, that means it must have the same number of rows. Let’s filter on the Blue group. To do this, I enter the expression D5:D14=“blue”. Note that FILTER is not case-sensitive. The next argument is the optional “is_empty” argument. This message only appears when FILTER can find no matching data. I’ll use “no results” in double quotes. When I hit enter, FILTER extracts just the data in the blue group. Next, let’s place the group name directly on the worksheet in H2, to make it easier to change. I need to adjust the include argument to use this value instead. Now I when I change H2 to “red” we get a new set of results. And, if I try to filter on a group that doesn’t exist, we’ll see the “no results” message that I provided. Finally, notice these results are dynamic. If I temporarily change a group in the source data, results update instantly.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
