## About this lesson

Using FILTER() to restrict the results of a Dynamic Array based on a single condition

## Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Single Condition DA FILTERing.xlsx48 KB Single Condition DA FILTERing - Complete.xlsx

48.5 KB

## Quick reference

### FILTER-ing Dynamic Arrays with a Single Condition

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 Microsoft 365 AFTER Excel 2019 was released
- This means that you must have a version of Excel newer than Excel 2019 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.