The FILTER function is used to filter results based on supplied criteria. It more or less performs the same function as the filter cone in the Editing group of the Home tab. The only difference however is that whilst the filter cone hides rows that do not meet criteria set, with the FILTER function, data that meets criteria is displayed in a separate range from the original data. This means that you are able to view the original data and filtered data at the same time.
The FILTER function is very good for making visual comparisons between a data set and subsets of the data set. In this post we would provide a detailed guide regarding the use of the FILTER FUNCTION. Let's begin with the Syntax & Argument
Syntax & Argument
The generic syntax of the Excel Filter Function is:
=FILTER(array, include, [if_empty])
- Array (required): This is the range containing the values you want to filter
- Include (required): This is the boolean array supplied as criteria. It must have equal height to the array (if data is in columns) or equal width with the array (if data is in rows).
- [if_empty] (optional): Represents value to return when no entries meet the criteria. The default text that displays is a "No records found", however you could edit it to display whatever message you like, or leave it blank by putting in double quotes ("")
Please note that the FILTER function is a new feature only available in Microsoft 365 and Excel 2021 and newer. Older versions of excel don't have this function in their repertoire. It may also interest you to know that you are not limited to only one logical test with the FILTER Function. Later in this post we would show you how to use this function with multiple criteria.
FILTER Function Examples
Basic Example
To extract values in B2:B10 that are greater than 50:
You can extract rows in a multi-column range based on the values of a single column. For example, we have an array A2:E7, we could use the formula below to extract rows based on values greater than 100 in column C:
It may be more practical to input the criteria in a separate cell to enable easy change on that input cell without having to manually go into the formular to change the criteria. The FILTER Function with criteria referencing a cell can be seen below:
We have an example of the top 10 students in a test to illustrate. The top performing students are members of 3 classes only, and the data is distributed in no particular order.
Let's assume we want to filter for only students in Diamond class in a new section of our worksheet. To do this, we simply go to any cell outside the current range of B4:D14, and with enough room to take 3 columns, and we type in the formula below:
Result:
Filter Multiple Criteria (And Logic)
Using the same dataset, let's add one more column to illustrate. We have included a column containing information about Gender of the top 10 performing students in the test. The first criteria (Diamond) is maintained from the previous example, however we want to include second criteria to filter only students that are Female from the class of Diamond.
To filter for data meeting both criteria, we would include a second range and criteria combination as seen below:
Filter Multiple Criteria (Or Logic)
With the same dataset ( last time I promise, fresh data coming right up), we can apply an OR condition meaning that if either one of the 2 criteria we set is met, the row appears in our filter section. Let's assume we want to filter all Female students or students that scored exactly 90.
To do this, we use the formula below:
Filter between dates
Finally, a brand new dataset for this section. We would be going through how to filter between dates in this section. Let's introduce you to our data set:
Our data is the collation of winners for the 100 meters race in a high school Inter-house sports competition and our task is to filter for winners from 1-sep to 15-Sep.
To filter for winners from and to the dates specified, we just need to tweak the formula a little with the >= an <= operators. Let's see the formula below:
Filter out blanks
As stated in the previous section, this data captures winners of the 100m dash only. Now imagine we ask our data analyst to include winners of the 200m dash to this data set and we get the input below, how do we filter out the blanks?
To filter out rows with blank cells in any of the columns B:E, use the formula below:
Filter containing specific text
Final section for our examples is filtering for specific texts. Let's add the last name of each runner for 100m winners only, you would notice that some have the same last name by coincidence of course, they are not actually related.
To execute this successfully, we would need to combine the FILTER Function with the ISNUMBER & SEARCH Functions. Before we begin, let's first see their last names.
If you look deeply at the data, you would see that 2 of the students have last names starting with the same 4 letters (Cole). Our mission is to filter for those 4 letters, and to do that, we combine the FILTER Function with the SEARCH & ISNUMBER Functions.