The Excel FILTER Function
This Excel FILTER Function tutorial is suitable for users of Excel for Microsoft 365
Objective
Use the Excel Filter Function to filter a range of data based on criteria you define to extract matching records.
The Excel FILTER Function Explained
The FILTER function is a new function available to Microsoft 365 users to filter data based on criteria you define. It is part of the Lookup and Reference group of functions in Excel.
=FILTER(array,include,[if_empty])
| array (required) | The array or cell range to filter |
| Include (required) | An 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. If this is not specified and the filter returns nothing a #CALC! error will be displayed in the cell. |
Video Tutorial
Example 1: A Basic FILTER
In this example, I am using the FILTER function to display a list of all students who sat the ‘English’ exam. The array is the entire dataset, B4:E23. I have then specified which column I want to include; in this case, the ‘Exam’ column, and I want to filter for ‘English’ in cell G4. If there are no results, I want Excel to return a blank.
This is the result:
Example 2: FILTER with Multiple Criteria
In this example, I am using the multiplication operator (*) to return the values in the range B4:E23 listed as ‘West’ AND ‘Maths.’
This is the result:
Example 3: Using FILTER and SORT
The SORT function can be combined with the FILTER function to apply an ascending or descending sort to the filter results.
In this example, I am using the multiplication operator (*) to return the values in the range B4:E23 listed as ‘West’ AND ‘Maths’. I am then sorting these results by the mark (column 4) in descending order (-1).
This is the result:
These are just a few examples of how to use the FILTER function in Excel. For more example, please visit the following links:
ExcelJet – The FILTER Function
XELPLUS – Excel’s New Dynamic Array FILTER Function
If you enjoyed this article on the Excel Filter Function, then you may like these other articles from Simon Sez IT:
- Protecting WorkBooks in Excel
- Logical Functions in Excel
- Designing Better Spreadsheets in Excel
- Introduction to Power Pivot & Power Query in Excel
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
