Don't drown in a stream of data
The FILTER function in Microsoft Excel is one of the most crucial functions to master. Without it, you will have a hard time finding the data you need. Here is a crash course into using FILTER in Excel.
It is also useful to note that the function is not the only way to filter data in MS Excel. You have tools like Auto Filter and Advanced Filter to achieve the same thing, with some important caveats we will discuss in this guide.
Table of Contents
What Is the FILTER Function?
Functions or Excel Formulas are the bread and butter of Excel, letting you do things like finding the average of a large dataset or creating a Bell curve graph. Each function has its syntax, which you can usually check by just entering the function name in Excel.
The Excel FILTER function, as the name describes, is used to “filter” the values of a specified range according to certain conditions. Both the range and the conditions to be used are entered with the function, making it extremely customizable.
With the right parameters, you can extract the exact information you need from a spreadsheet without having to go over the entire thing to find matching entries manually. And since the output is contained in a cell, you can chain more functions to perform calculations or visualize the results in a graph.
Why Is the FILTER Function Preferred Over the Advanced Filter?
Most Excel beginners stick to the built-in Data filtering tools in Excel rather than attempting to learn the syntax of a function. The Auto filter is the easiest, allowing you to exclude columns and set filtering criteria from a menu-based interface. Then there is the Advanced filter with its capability to apply multiple criteria for implementing complex filtering schemes.
Then why even bother using the FILTER function?
The main advantage of using Excel functions over doing any operations manually (with another Excel tool or even any other program) is that functions are dynamic. The Auto filter or the Advanced filter gives you one-off results that do not change when the source data is modified. The FILTER function, on the other hand, updates its results accordingly when the data changes.
FILTER Function Syntax
The syntax of the FILTER formula is straightforward enough:
=FILTER(array, include, [if_empty])
An array is a rectangular subset of the spreadsheet, indicated by specifying a range between the top left cell and the bottom right cell. For example, A3:E10 is an array consisting of the columns A to E and the rows 3 to 10.
The next parameter is simply the criteria to be used, or more technically a boolean array. This is entered in the form of an expression evaluating the value of a range of cells (usually a column) that returns TRUE or FALSE. For example, A3:A10=”Pass” will return TRUE when the value of the cell matches the given string.
Finally, you can enter a value to be returned by the FILTER function when no rows match the conditions. This can be a simple string like “No Records Found”.
Related: Need to see how accurately sample data fits a larger dataset? Check out our guide on calculating standard error in Excel.
Using the FILTER Function
Now that we know the syntax of the FILTER function, let’s look at how to actually use FILTER in a spreadsheet.
The sample data we are using for this demonstration has an array from A2 to F11, tabulating the Biology scores of ten students along with the normal distribution.
Let’s write a function to filter the entries based on their exam scores (listed in the D column) and return only those that scored below 30. This should be the syntax:
=FILTER(A2:F11,D2:D11<30,”No Matches Found”)
Since the filtered results are a subset of the array, use the function in a cell with enough space after it. We will do it below the original table:
And we get the expected results. All entries with a score below 30 are chosen and displayed in the same table format.
You are not limited to a single condition either. Use the AND operator (*), to chain multiple expressions as a single parameter, creating a more complex filter.
Let’s construct a function that returns the entries lying between 30 and 70 marks. Here is the syntax and the results:
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),“No Matches Found”)
For non-exclusive criteria, you can also use the OR operator (+). This matches the filter even if only one of the included conditions evaluates to TRUE.
In the following formula, we use it to find the outliers, by filtering for results less than 15 or more than 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),”No Records Found”)
Finally, instead of using a single value or string to return when the FILTER function does not find anything, you can specify values for every column to ensure the output always keeps to a consistent format.
First, let us try a condition we know is false to see what it looks like by default:
=FILTER(A2:F11,D2:D11>90,”No Matches Found”)
As you can see, the result only has a single string, at odds with the expected format. This is not usually a problem unless you are looking to feed the results (or some values from it) into another formula.
So let’s try giving default values in the same format as an entry of the array. We can do this by specifying comma-separated values enclosed in curly braces. Like this:
=FILTER(A2:F11,D2:D11>90,{“No Record”, “No Record”, “No Record”, 0})
This gives us more palatable results, consistent with the rest of the spreadsheet’s format.
Is the FILTER Function Worth It?
Even if you only use MS Excel for maintaining records and don’t intend to do any fancy calculations, the FILTER function is one of the few you should still look into.
Because once your workbook reaches a certain size, manually locating data can be a pain. And while the Auto filter and Advanced filter tools are handy, using a function is actually more convenient in the long run since the results update themselves and can be paired with other functions.
