FILTERSORTUNIQUEDynamic ArraysExcel 365Advanced

Master Excel Dynamic Arrays (Excel 365)

Learn how to use modern dynamic array functions like FILTER, SORT, UNIQUE, and SEQUENCE to transform your spreadsheets.

Introduction

Dynamic arrays are one of the most significant updates to Excel in recent years. They automatically spill results into multiple cells, eliminating the need for Ctrl+Shift+Enter array formulas. Functions like FILTER, SORT, UNIQUE, and SEQUENCE make complex data manipulation simple and intuitive.

Prerequisites

Excel 365 or Excel 2021 (dynamic arrays not available in older versions)
Basic understanding of Excel formulas
Familiarity with range references

1Understanding Dynamic Arrays

Dynamic arrays automatically spill results into adjacent cells. When you enter a formula that returns multiple values, Excel places each value in a separate cell.

Spill behavior

Dynamic array formulas return arrays that 'spill' to fill as many cells as needed.

#SPILL! error

If there's already data in the spill range, you'll get a #SPILL! error.

Dynamic arrays work in Excel 365, Excel 2021, and Google Sheets.

You can reference a spill range with the # operator.

2FILTER Function

FILTER extracts rows from a range that meet specified conditions.

Example

=FILTER(A2:C100, B2:B100="Sales")

Result: All rows where Column B equals 'Sales'

Filters the range A2:C100 to show only rows where Column B contains 'Sales'.

Use logical operators: >, <, >=, <=, =, <>

Combine conditions with * (AND) or + (OR).

3SORT Function

SORT sorts a range by specified columns.

Example

=SORT(A2:C100, 3, -1)

Result: Data sorted by Column C in descending order

Sorts the range A2:C100 by the 3rd column (C) in descending order (-1).

Use 1 for ascending, -1 for descending.

Sort by multiple columns by providing an array of column numbers.

4UNIQUE Function

UNIQUE returns unique values from a range, removing duplicates.

Example

=UNIQUE(A2:A100)

Result: All unique values in Column A

Returns each value from A2:A100 only once, even if it appears multiple times.

Use UNIQUE with multiple columns to find unique combinations.

Add TRUE as the second argument to compare case-sensitively.

5SEQUENCE Function

SEQUENCE generates a sequence of numbers.

Example

=SEQUENCE(10, 1, 1, 1)

Result: Numbers 1 through 10 in a column

Generates a 10-row, 1-column sequence starting at 1, incrementing by 1.

Parameters: rows, columns, start, step.

Great for creating numbered lists or date sequences.

6Combining Dynamic Array Functions

The real power comes from combining these functions.

Example

=SORT(FILTER(A2:C100, B2:B100="Sales"), 3, -1)

Result: Sales data sorted by amount descending

Filters for sales records, then sorts by column 3 (amount) from highest to lowest.

Nest functions from inside out.

Use UNIQUE(SORT(FILTER(...))) for unique sorted filtered results.

Functions Used

FILTER

Filters a range based on conditions

SORT

Sorts a range by specified columns

UNIQUE

Returns unique values from a range

SEQUENCE

Generates a sequence of numbers

Summary

Dynamic array functions are a game-changer for Excel users. FILTER, SORT, UNIQUE, and SEQUENCE make complex data manipulation much simpler without the need for array formulas. By combining these functions, you can create powerful data analysis workflows that update automatically when your source data changes.

Next Steps

Practice filtering data with FILTER
Try sorting with multiple columns
Create unique lists with UNIQUE
Generate date sequences with SEQUENCE
Combine multiple dynamic array functions