The FILTER Function in Excel: An In-Depth Guide|2025
/in Advanced Excel Articles /by BesttutorThe FILTER Function in Excel: Learn how to filter and extract data dynamically for efficient analysis. Master this powerful tool to streamline workflows and enhance productivity!
Excel has long been regarded as one of the most powerful tools for data manipulation and analysis. Its functionalities have evolved over the years to provide users with more efficient ways to handle large datasets. One of the most powerful features in Excel for filtering data is the FILTER function. This function allows users to return a specific subset of data that meets defined criteria. However, the availability and usage of the FILTER function depend on the version of Excel you are using. This paper explores the functionality of the FILTER function in different versions of Excel, its uses with multiple criteria, and how it can be applied to both single and multiple columns of data.
Table of Contents
ToggleOverview of the FILTER Function in Excel
The FILTER function is a dynamic array function introduced in Excel 365 and Excel 2021. It allows users to filter a range of data based on criteria they specify, with the result being a dynamic array that automatically updates when the source data changes. The basic syntax of the function is:
=FILTER(array, include, [if_empty])
Where:
- array refers to the range or array of data that you want to filter.
- include is a Boolean array or expression that defines the filtering conditions.
- [if_empty] is an optional argument that specifies the value to return if no data meets the criteria.
This function can save time for Excel users who need to extract subsets of data based on specific conditions.
Availability of the FILTER Function
The FILTER function is not available in all versions of Excel. Users of Excel versions prior to Excel 365 or Excel 2021 do not have access to this function. Therefore, users working with older versions of Excel, such as Excel 2016 or Excel 2019, will need to use alternative methods, such as traditional filter tools or advanced formulas (like IF
, INDEX
, or MATCH
) to achieve similar results.
In Excel 2016, the FILTER function is not available. Users of this version will have to rely on manual filtering or create complex formulas to simulate filtering results.
In Excel 2019, the FILTER function was not initially available at its release but was later included in the update for users with Microsoft 365 subscriptions. However, users who do not have access to the dynamic array functions in Excel 2019 will not be able to use the FILTER function and will need to explore alternative methods.
Excel FILTER Function with Multiple Criteria
One of the powerful features of the FILTER function is its ability to apply multiple criteria. When filtering data based on more than one condition, users can combine multiple logical expressions using the *
(AND) or +
(OR) operators. The result is a filtered array that meets all the conditions specified.
Example: Excel FILTER Function with Multiple Criteria (AND condition)
Suppose you have a dataset of sales data with columns for Salesperson, Product, and Amount. If you want to filter the data to show sales made by a specific salesperson for a particular product, you can use the FILTER function with multiple criteria combined with the *
operator, which functions like an AND condition.
=FILTER(A2:C10, (A2:A10="John")*(B2:B10="Laptop"))
In this formula:
- The
A2:A10="John"
condition checks for records where the salesperson is “John.” - The
B2:B10="Laptop"
condition filters for rows where the product is a “Laptop.” - The
*
operator ensures that both conditions must be true for the data to be included in the result.
Example: Excel FILTER Function with Multiple Criteria (OR condition)
Alternatively, if you want to filter the data to show sales by “John” or “Jane,” you can use the +
operator, which functions like an OR condition.
=FILTER(A2:C10, (A2:A10="John")+(A2:A10="Jane"))
This formula will return all records where the salesperson is either “John” or “Jane.”
Excel FILTER Function with Multiple Columns
The FILTER function in Excel is not limited to filtering data in a single column. It can also be applied to filter data across multiple columns. By providing a multi-column range as the array argument, users can filter based on multiple columns of data simultaneously.
Example: Filtering Data Across Multiple Columns
Imagine you have a dataset that includes sales information, including salesperson, product, and amount. You can filter the data by applying conditions to multiple columns at once.
=FILTER(A2:C10, (A2:A10="John")*(C2:C10>5000))
In this example:
- The
A2:A10="John"
condition filters for sales made by “John.” - The
C2:C10>5000
condition filters for sales amounts greater than 5000. - The
*
operator ensures that both conditions must be met.
This formula will return the sales data for “John” with amounts greater than 5000.
Excel FILTER Function for Multiple Values
The FILTER function can also handle multiple values in a single column, allowing users to filter data based on a list of values. This can be done by using the ISNUMBER
and MATCH
functions inside the FILTER function.
Example: Filtering Multiple Values from a Single Column
Suppose you want to filter data for multiple products, such as “Laptop,” “Phone,” and “Tablet.” You can use the following formula:
=FILTER(A2:C10, ISNUMBER(MATCH(B2:B10, {"Laptop", "Phone", "Tablet"}, 0)))
In this formula:
- The
MATCH
function checks if the value in theB2:B10
range matches any of the values in the list{"Laptop", "Phone", "Tablet"}
. - The
ISNUMBER
function returnsTRUE
if a match is found, allowing the FILTER function to include those rows in the result.
This formula filters the dataset to include only the rows where the product is one of the specified values.
Excel FILTER Function in Excel 2019
In Excel 2019, the FILTER function is not available as a standard feature for all users. However, users with access to Office 365 or those who have updated their Excel 2019 with Microsoft 365 subscription may be able to use the FILTER function. For those without access, alternatives like array formulas or advanced filtering features like AutoFilter or the Advanced Filter tool can be used to filter data based on specific criteria.
In Excel 2019, users often rely on the Advanced Filter tool, which allows for filtering based on complex criteria. To use the Advanced Filter, users need to define a criteria range and then apply the filter to the data. This process is more manual than the FILTER function but still provides powerful filtering capabilities.
Excel FILTER Function for Multiple Criteria in the Same Column
The FILTER function can also be used to filter data based on multiple criteria within the same column. For instance, if you want to filter a list of sales data where the salesperson is either “John” or “Jane,” you can use the following formula:
=FILTER(A2:C10, (A2:A10="John")+(A2:A10="Jane"))
This formula returns all rows where the salesperson is either “John” or “Jane.” The +
operator ensures that any record with either of the names will be included in the result.
Conclusion
The FILTER function is a powerful tool in Excel that simplifies the process of extracting specific subsets of data based on criteria. For users with Excel 365 or Excel 2021, the FILTER function offers a simple, flexible way to filter data across multiple criteria, columns, and values. However, for users of older versions of Excel, like Excel 2016 or Excel 2019, alternative methods, such as Advanced Filters and complex formulas, may be required. Understanding how to use the FILTER function effectively can significantly enhance productivity and streamline data analysis tasks in Excel.
GetSPSSHelp is the best choice for mastering the FILTER function in Excel because of our expert guidance and student-focused approach. Our team simplifies complex concepts, providing clear, step-by-step instructions to help you filter and extract data effortlessly. We ensure accuracy, save time, and enhance your understanding of Excel. With affordable pricing, 24/7 support, and a commitment to quality, we make learning FILTER stress-free and effective. Whether you’re a beginner or advanced user, GetSPSSHelp empowers you to excel in data analysis. Trust us for reliable, professional assistance that boosts your skills and confidence!
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper

