The FILTER function is one of the most powerful and dynamic functions introduced in Excel 365 and Excel 2021. It allows users to extract specific data from a range or table based on defined criteria, eliminating the need for complex array formulas or helper columns. Whether you’re cleaning data, building dashboards, or creating interactive reports, the FILTER function can significantly streamline your workflow.
What Does the FILTER Function Do?
At its core, the FILTER function returns a filtered version of a range based on one or more conditions. Unlike earlier versions of Excel, where filtering was mostly a manual task or required advanced formulas, FILTER dynamically updates the results as the source data or criteria change.
Syntax
=FILTER(array, include, [if_empty])
Array is the range of data to filter. This can be a range such as A2:E50 (it is not necessary to include the row that contains the headings) or a named Table
Include is an expression that defines which rows to include. For example C2:C50 = “London” (assuming column C contains the names of the cities)
if_empty] is optional and is the value to return if no results are found
Example 1
Suppose you are tracking the number of hours that your team has worked on several projects.
You need to track the projects by State. You type the name of a State into cell F1 and cells E4:Gx (where x is a row number) are populated with the appropriate information from A2:C100. To do this, enter the following into E4:
=FILTER(A2:C100, B2:B100=F1, “No results for this State”)
This will return only those rows where the value in column B matches the value in F1. If no matches are found, it will display “No results for this State”.
Multiple Conditions
FILTER also supports multiple criteria. For example, to extract rows where the State matches the value in F1 and the number of hours is greater than 150, use the following formula in E4:
=FILTER(A2:C100, (B2:B100=F1)*(C2:C100>150), “No matching data”)
The asterisk (*) acts as the AND operator. For OR conditions, use the plus (+) symbol.
Benefits of Using FILTER
- Dynamic Output: Results automatically update when the source data changes
- No Sorting Required: Unlike functions like VLOOKUP, the source data doesn’t need to be sorted
- Array-Based: Returns multiple rows and columns as an array spill, perfect for dashboards and reports
- Cleaner Workbooks: Reduces reliance on intermediate steps and helper columns
As Excel continues to evolve, functions like FILTER represent a shift toward more intuitive and powerful data analysis tools. By mastering FILTER, you’ll not only save time but also build smarter, more responsive spreadsheets.
Elevate your project management skills and propel your career forward with an MPUG Membership. Gain access to 500+ hours of PMI-accredited training, live events, and a vibrant online community. Watch a free lesson and see how MPUG can teach you to Master Projects for Unlimited Growth. JOIN NOW
