Table of Contents
Do you need to quickly create a complex filter in Excel? If so, then this guide is for you. In this tutorial, we will be learning how to create a nested FILTER function in Excel. We will be covering the basics of the FILTER function, including how to create a simple FILTER, multiple FILTER criteria, and a nested FILTER. By the end of this tutorial, you will be able to confidently create FILTERs in Excel. Let’s get started!
You can use the following syntax in Excel to create a nested FILTER function:
=FILTER(FILTER(A2:C11, B2:B11>20), {1,0,1})
In this particular formula, the inner FILTER function filters the cells in the range A2:C11 to only return the rows where cells in the range B2:B11 are greater than 20.
Then, the outer FILTER function filters the results to only include columns A and C.
Note that the syntax {1,0,1} uses 1 to specify that a column should be included in the results while 0 is used to specify that a column should not be included.
By using {1,0, 1} we specify that in the range A2:C11 we’d like to include column A, exclude column B, and include column C.
The following example shows how to use this formula in practice.
Example: Create Nested FILTER Function in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
We can type the following formula into cell E2 to filter the dataset to only show the rows where the value in the points column is greater than 20:
=FILTER(A2:C11, B2:B11>20)
The following screenshot shows how to use this formula in practice:
This FILTER function filters the original dataset to only show the rows where the value in the points column is greater than 20.
However, we could use the following nested FILTER function to apply the same filter and only return the values from the team and assists columns:
=FILTER(FILTER(A2:C11, B2:B11>20), {1,0,1})
The nested FILTER function filters the original data to only include rows where the points value is greater than 20 and then returns only the values in the team and assists columns.