Locked lesson.

## About this lesson

Learn how to filter a range of data based on criteria you define with the FILTER function.

## Exercise files

Download this lesson’s related exercise files.

Introduction to FILTER - Begin.xlsx50.5 KB Introduction to FILTER - Complete.xlsx

50.8 KB

## Quick reference

### Introduction to FILTER

Working with the FILTER function to reduce the results of a dynamic array

### When to use

When you need to filter certain items out of your dynamic array results

### Instructions

#### Availability

- Dynamic arrays were released to Office 365 AFTER Excel 2019 was released
- This means that you must have Excel 2021 or newer (or Office 365) to use them

#### Using FILTER()

- Can be used with table components or ranges
- Syntax: =FILTER(
*array, include, [if_empty]*) - The array parameter can have any number of rows/columns
- The “include” formula must be a single column (row) compared to a value
- The “include” formula must have the same number of rows (columns) as the original array

#### Sample data

- Returning the rows from the Sales table where the Category = Beer:
- =FILTER( Sales, Sales[Category] = "Beer" )

- Returning a block of data where the value in column E = 10
- =FILTER( A10:C30, C10:C30 = 10 )

#### Operators for FILTER inclusion

- = Include result in array output if array item is equal to item
- < Include result in array output if array item is less than item
- > Include result in array output if array item is greater than item
- <> Include result in array output if array item is NOT equal to item
- <= Include result in array output if array item is less than or equal to item
- >= Include result in array output if array item is greater than or equal to item

Lesson notes are only available for subscribers.