Excel FILTER Function
The FILTER function filters a range of data and dynamically returns the matching results according to the criteria you specify.
The FILTER function returns an array.
- array can be supplied as a row of values, a column of values, or a combination of rows and columns of values.
- The dimensions of include should equal to that of the array argument.
- To add two or more criteria in include, you should enclose each criterion in brackets and connect them with an asterisk (*).
- The result returned by FILTER will spill into a range of cells in the way your original data of array is organized. If one or more cells in the spill range are not empty, the #SPILL! error will be returned.
- If any of the values supplied as the include argument is an error or cannot be converted to a Boolean value, FILTER will return an error.
- Text values in the include or if_empty arguments should be enclosed in double quotation marks. Otherwise, FILTER will return the #NAME? error.
- To display nothing when there is no matching data, supply an empty string ("") for if_empty.
- FILTER will return the #CALC! error if there is no matching data and the value for if_empty is not provided.
- FILTER can be used between different workbooks. However, make sure the referenced workbooks are open. Otherwise, a #REF! error will be returned if you refresh the result cell.
- The results of FILTER are dynamic, meaning that they update automatically when values in the source data change. However, the results will not be updated if you add new entries to array. To fix this, you should use structured references as the array argument.
Supposing you have a student table as shown below, to filter the students who are from Class A and scored more than 60, please copy or enter the formula below in the top left cell of the result table and then press Enter to get the result:
=FILTER(B3:E14,(B3:B14="Class A")*(E3:E14>60),"NO RECORDS")
Or, use cell references to make the formula dynamic:
Example of No Matches
Suppose that you want to filter the students who are from Class A and scored less than 60 (apparently there is no score that is less than 60), to display the results as nothing or a certain text string, you could copy the formulas below and press Enter to see the results.
=FILTER(B3:E14,(B3:B14="Class A")*(E3:E14<60),"") >>> Returns nothing
=FILTER(B3:E14,(B3:B14="Class A")*(E3:E14<60),"NO RECORDS") >>> Returns NO RECORDS