The FILTER function in Excel is a very useful and frequently used function, that you will likely find the need for in many situations. Note that the FILTER function is only available in Microsoft Office 365, and Microsoft Office Online.
To filter by using the FILTER function in Excel, follow these steps:
In this article I will start with the basics of using the FILTER function (examples included), and then also show you some more involved ways of using the FILTER function such as filtering by multiple conditions.
This article focuses specifically on the FILTER function that is typed into the spreadsheet cells as a formula, and not the filter command available from the toolbar and pop-up menus.
Using the FILTER function in Excel is almost the same as using it in Google Sheets, but there are slight differences between the two. Click here to read the Google Sheets version of this article.
Here are the formulas that we will go over in this lesson, further below are detailed examples with images included.
The diagram below will show you exactly how the FILTER function works in Excel.
=FILTER(A3:B12, B3:B12=F1)
(Copy/Paste the formula above into your sheet and modify as needed)
The FILTER function in Excel allows you to filter a range of data by a specified condition, so that a new set of data will be displayed which only shows the rows/columns from the original data set that meets the criteria/condition set in the formula.
Syntax:
=FILTER(array,include,[if_empty])Formula summary: “The FILTER function filters an array based on a Boolean (True/False) array.”
array (Required): The array, or range to filter
include (Required): A Boolean array whose height or width is the same as the array
[if_empty] (Optional): The value to return if all values in the included array are empty (filter returns nothing)
The source range that you want to filter, can be a single column or multiple columns.
The range that is used to check against the criteria that you set, must be a single column (later I will show you how to filter by multiple conditions, but don’t worry about that for now).
The criteria that set in the condition can be manually typed into the formula as a number or text, or it can also be a cell reference.
*Note that the source range and the single column range for the condition, must be the same size (must contain the same number of rows), or the cell will display an error.
First let’s go over using the FILTER function in Excel in its simplest form, with a single condition/criteria.
I will show you how to filter by a number, a cell value, a text string, a date… and I will also show you how to use varying “operators” (Less than, Equal to, etc…) in the filter condition.
In this first example on how to use the filter function in Excel, the scenario is that we have a list of students and their grades, and that we want to make a filtered list of only students who have a perfect grade.
The task: Show a list of students and their scores, but only those that have a perfect grade
The logic: Filter the range A3:B12, where the column B3:B12 is greater than 0.7 (70%)
The formula: The formula below, is entered in the blue cell (D3), for this example
=FILTER(A3:B12, B3:B12>0.7)
In this example we are using the operator “=” (Equal To) for the filter condition/criteria, but you can also use any of the following:
“=” (Equals)
“>” (Greater than)
“
“<>” (Not equal to)
“>=” (Greater than or equal to)
“
In this example, we want to achieve the same goal as discussed above, but rather than typing the condition that we want to filter by directly into the formula, we are using a cell reference.
When you filter by a cell value in Excel, your sheet will be setup so that you can change the value in the cell at any time, which will automatically update the value that the filter criteria it attached to.
In this example, you will notice that instead of directly typing the number “0.9” into the formula itself, the filter criteria is set as cell G1, where the “0.9” value is entered.
The task: Show a list of students and their scores, but only those that have a score below 90%
The logic: Filter the range A3:B12, where B3:B12 is less than the value that is entered in the cell F1 (0.9)
The formula: The formula below, is entered in the blue cell (D3), for this example
In this example, we are going to use a text string as the criteria for the filter formula. This is very similar to using a number, except that you must put the text that you want to filter by inside of quotation marks.
In this scenario we are filtering a list that shows customers and their payment status, and we want to display only customers that have a payment status of “Late”.
The task: Show a list of customers who are past due on their payments
The logic: Filter the range A3:B12, where B3:B12 equals the text, “Late”
The formula: The formula below, is entered in the blue cell (D3), for this example
=FILTER(A3:B12, B3:B12=”Late”)
Now that you have got a basic understanding of how to use the filter function in Excel, here is another example of filtering by a string of text, but in this example we will use the “not equal” operator (<>), so that you can learn how to filter a range and output data that is NOT equal to criteria that you specify.
In this example we will also use a larger data set to demonstrate a more extensive application of the FILTER function in the real world.
You may be surprised at how often a situation comes up when you need to filter data where it is “not equal to” a certain number or piece of text that you specify.
In this example let’s say that we have a report/spreadsheet that shows data from sales calls that occur at your company, and we want to filter the data so that a specified sales rep (Bob) is NOT included in the filter output.
The task: Show sales call data for all sales reps, except Bob
The logic: Filter the range A2:E1000, where B2:B1000 DOES NOT equal the text, “Bob”
The formula: The formula below, is entered in the blue cell (G3), for this example
=FILTER(A3:E1000, B3:B1000<>”Bob”)
Notice that the filtered data on the right side of the image above does not contain any of the rows/calls that Bob was involved in.
Filtering by a date in Excel can be done in a couple of ways, which I will show you below. If you try to type a date into the FILTER function like you normally would type into a cell… the formula will not work correctly.
So you can either type the date that you want to filter by into a cell, and then use that cell as a reference in your formula… or you can use the DATE function.
When you add one day to the date, this number increments by one each time… i.e “43,618” “43,619” “43,620”
So, one date can be considered to be “greater than” another date, if it is further in the future. Conversely, one date can be said to be “less than” another date, if it is further in the past.
In this first example we will filter by a date by using a cell reference. This is similar to the example we went over in part 2, but in this example instead of working with percentages, we are dealing with dates.
Let’s say that we want to filter a list of students, their test scores, and the dates that the tests were completed… and we want to show only tests that were taken before June (06/01/2019).
The task: Show only tests that were taken before June
The logic: Filter the range A3:C12, where C3:C12 is less than the date that is entered in cell G1 (06/01/2019)
The formula: The formula below, is entered in the blue cell (E3), for this example
In this second example on filtering by date in Excel we are using the same data as above, and trying to achieve the same results… but instead of using a cell reference, we will use the DATE function so that you can type enter the date directly into the FILTER function.
When using the DATE function to designate a certain date, you must first enter the year, then the month, and then the day… each separated by commas (shown below).
The task: Show only tests that were taken before June
The logic: Filter the range A3:C12, where C3:C12 is less than the date of (06/01/2019)
The formula: The formula below, is entered in the blue cell (E3), for this example
You can also filter data to display only the largest or smallest values in Excel
We can filter to display only the largest values by using the FILTER function with the LARGE function. This will allow you to specify how many results you want to display, such as the top 10, top 100, etc.
=FILTER(A3:B12,(B3:B12>=LARGE(B3:B12,3)))
As you can see in the image above, the student names & student scores are being filtered to display only the students with the 3 highest scores. Change the number “3” in the LARGE function to any number that you want, to specify how many rows / how many results you want to display.
The formula below uses the SORT function in combination with the formula above, to display the largest results in descending order (largest first):
We can filter to display only the smallest values by using the FILTER function with the SMALL function. This will allow you to specify how many results you want to display, such as the bottom 10, bottom 100, etc.
As you can see in the image above, the student names & student scores are being filtered to display only the students with the 3 lowest scores. Change the number “3” in the SMALL function to any number that you want, to specify how many rows / how many results you want to display.
The formula below uses the SORT function in combination with the formula above, to display the smallest results in ascending order (smallest first):
When using the Excel FILTER function you may want to output a set of data that meets more than just one criteria. I will show you two ways to filter by multiple conditions in Excel, depending on the situation that you are in, and depending on how you want to formula to operate.
The normal way of adding another condition to your filter function, (as shown by the formula syntax in Excel), will allow you to set a second condition, where the first AND second condition must be met to be returned in filter output.
However I will also show you how to make a slight modification to the function so that you can choose to set a second condition where EITHER condition can be met to return/display in the filter function’s output/destination. (Separate the conditions with an asterisk to use AND logic, or separate the conditions with a plus sign to use AND logic.)
In this example, we are going to filter a set of data, and only display rows where BOTH the first condition AND the second condition are met/true.
To use a second condition in this way (with AND logic), simply enter the second condition into the formula after the first condition, separated by an asterisk (*). Each condition must be inside of its own set of parenthesis (shown below).
When using the filter formula with multiple conditions like this, the columns that are referenced in each condition must be different.
In this scenario we want to filter a list that shows customers, their payment status, and their membership status… and to show only customers who have an active membership AND who are also late on their payment.
This will make sure that customers with an inactive membership who are still designated as being late on payment in the system… are not shown in the filter results, and not put on the list for being sent a “late payment” notice.
The task: Show a list of customers who are late on their payments, but only those with active memberships
The logic: Filter the range A3:C12, where B3:B12 equals the text “Late”, AND where C3:C12 equals the text “Active”
The formula: The formula below, is entered in the blue cell (E3), for this example
=FILTER(A3:C12,(B3:B12=”Late”)*(C3:C12=”Active”))
In this example we are going to filter a set of data and only display rows where EITHER the first condition OR the second condition are met/true.
To use a second condition in this way (with OR logic), simply enter the second condition into the formula after the first condition, separated by a plus sign. Each condition must be inside of its own set of parenthesis (shown below).
When using the FILTER formula in this way, you can choose criteria from the same or different columns.
In this scenario we want to filter the same customer data as shown in the previous example, but this time we want to show a list of customers who EITHER have an active membership OR who are late on their payment.
This will give a list of customers who can be sent a notice for payment… including active members, or/also inactive members who are late on their final payment.
The task: Show a list of customers who are active members, and include customers who are late on payment even if they are not active members
The logic: Filter the range A3:C12, where B3:B12 equals the text “Late”, OR where C3:C12 equals the text “Active”
The formula: The formula below, is entered in the blue cell (E3), for this example
=FILTER(A3:C12, (B3:B12=”Late”)+(C3:C12=”Active”))
You may often find situations where you need to filter from another sheet in Excel, where your raw unfiltered data is on one tab, and your filter formula / filter output is on another tab.
This can be done by simply referring to a certain tab name when specifying the ranges in the filter. So where you would normally set a range like “A3:B”, when referencing another sheet while filtering you specify the tab name by adding the tab name and an exclamation mark before the column/row portion of the range, like “TabName!A3:B”
However when the tab name has a space in it, it is necessary to use an apostrophe before and after the tab name, like ‘Tab Name’!A3:B.
Here is an example of how to filter data from another tab in Excel, where your filter formula will be on a different tab than the source range.
Let’s say that you have a list of employees and their schedule type (Full Time / Part Time) on one tab, and that you want to display a filtered list of full time employees on another tab.
The task: Filter the list of employees on the tab labeled “Filter List”, and show a list of employees who have a full time schedule, on a separate tab
The logic: Filter the range ‘Filter List’!A3:B12, where the range ‘Filter List’!B3:B12 is equal to the text “Full Time”
The formula: The formula below, is entered in the blue cell (A3), for this example
=FILTER(‘Filter List’!A3:B12,’Filter List’!B3:B12=”Full Time”)
Here is a list of employees and their schedules, which is held on a tab labeled “Filter List”
And here is a filtered list of employees who have full time schedules, where the filter formula and output data are held on a separate tab.
In Excel you can combine multiple functions into a single formula, so that the formula performs the task of two functions all at once.
I will show you how to use the FILTER function with the SORT function, as individual functions, and in a single formula!
As a quick example, here is what a formula looks like, that both sorts and filters (contains both the SORT and FILTER function).
=SORT(FILTER(A3:C100,C3:C100=”Text”),1,1,FALSE)
The formula above filters columns A through C, where column C is equal to “Text”, and then the formula sorts the filter results by the first column in ascending order.
When no matches are found in your FILTER function, Excel will display an error message the looks like the image below. The cell will say “#CALC!” and when you hover your cursor over the cell, a message will pop up that says “Empty arrays are not supported“.
This simply means that there are no results from the source data that matches your criteria. This is not always an actual error.
For example, if you are filtering to display students with a score of 100%, and there are no students with a grade of 100%, then no matches will be found and the error message will pop up. In this case, the formula is correct but there are simply no results.
Sometimes this message will display when you have entered the wrong criteria in your function. For example, let’s say that you meant to filter to only display students who have a grade that is above 70%, but you forget to put a decimal point in your criteria and use the number “7” instead of “0.7”. This would cause an error since 7 = 700% and no student can have a score above 100%.
Answer the questions below about the Excel FILTER function, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.
Which of the following formulas uses a “cell reference” in the filter condition?
Which of the following formulas uses the “Not Equal” operator?
True or False: If the column(s) in the source range and the column in the filter condition are not the same size (if one has more rows than the other), the formula will not work, and will display an error.
Which of the following formulas uses AND logic, where BOTH conditions must be met to satisfy the filter criteria?
Which of the following formulas uses OR logic, where EITHER condition can be met to satisfy the filter criteria?
Question 1: 2
Question 2: 3
Question 3: 1
Question 4: 2
Question 5: 2
