Scribd
Upload
6 views4 pages

Excel FILTER() Function Tutorial

This tutorial introduces the Excel FILTER() function, which allows users to extract specific data from a range based on defined criteria, returning an array of values. It covers the syntax, parameters, and provides an example of filtering sales data, highlighting the function's ability to create a dynamic spill range. Additionally, it offers tips for using multiple conditions and enhancing formula readability with named ranges.

Uploaded by

Luis Alberto Marín Llanes
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views4 pages

Excel FILTER() Function Tutorial

This tutorial introduces the Excel FILTER() function, which allows users to extract specific data from a range based on defined criteria, returning an array of values. It covers the syntax, parameters, and provides an example of filtering sales data, highlighting the function's ability to create a dynamic spill range. Additionally, it offers tips for using multiple conditions and enhancing formula readability with named ranges.

Uploaded by

Luis Alberto Marín Llanes
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Excel FILTER() Function

Tutorial: Mastering Data


Filtering with Step-by-Step
Examples

EXCEL FORMULA | EXCEL TIPS

In today’s tutorial, we’ll be diving into the exciting world


of dynamic arrays and spill functions in Excel. These features have
revolutionized the way we work with data, providing a more flexible and
efficient way to handle arrays. I am going to explain these concepts step
by step with examples that are perfect for all beginners till expert.
Note:
This is the first article of Dynamic Arrays and Spill Functions in
Excel series. In this series we are going to learn
about FILTER(), SORT(), UNIQUE(), SEQUENCE() and RANDARRAY() functio
ns.
The first such function we are going to learn is the FILTER() function.
It allows you to extract specific data from a range based on given
criteria, returning an array of values that meet the specified conditions.
Here "returning an array" is noteworthy in this formula. Unlike most of
the excel formula, this does not return a single value rather it returns a
series of values. This makes this excel formula special.
FREE! Playground and Download
Do not forget to play around the embedded excel sheet at the end of this
article.

Syntax of Filter() formula


Following is the syntax of filter formula
Copy
=FILTER(array, include, [if_empty])
Filter Formula

Explanation about the parameters

1. array (mandatory): The range of data you want to filter.


2. include (mandatory): The conditions that the data must meet to
be included in the filtered result.
3. [if_empty] (optional): What to return if no data meets the
specified conditions.

Example: Filtering Sales Data


Let’s say you have a list of sales data as shown in picture below. You want
to filter out the products with sales greater than 500. So one option is to
use the excel’s built-in filter feature available on a table and filter only
those products that have sales higher than 500. But note that, this option
simply hide those rows which does not meet the criteria. It does not
create a separate sub-set of your original table by filtering those records
which are meeting the criteria provided by you.
If you want to create a separate sub-set of your original data by applying
some filter conditions, you can use Filter() formula in excel.
Input Data:
Sales Data Table – Filter Formula

Copy
=FILTER(B2:E5, E2:E5>500, "No Products Found")
Explanation:

1. array (B2:E5): This is the range of data we want to return for which
conditions are met. In this case, it should return values from two
columns B to E.
2. include (E2:E5>500): This condition specifies that only sales
greater than 500 should be included in the result.
3. [if_empty] ("No Products Found"): If there are no sales exceeding
500, the function will return “No Products Found”
Filter Function in Excel

Important Notes:

1. Dynamic Spill Range:The FILTER() function automatically spills


results into adjacent cells, creating a dynamic spill range.
2. Multiple Conditions: You can use logical operators (AND, OR) to
apply multiple conditions within the “include” parameter.
3. Tips and Best Practices: Named Ranges: Consider using named
ranges for your array parameter to enhance formula readability.

You might also like