Dynamic arrays (SORT, FILTER, UNIQUE) in Excel - Step-by-Step Guide

Introduction

Dynamic arrays let you create formulas that spill results into multiple cells automatically. Functions like SORT, FILTER, and UNIQUE help you organize and extract data easily without copying formulas to many cells.

When you want to list unique customer names from a sales list without duplicates.

When you need to sort a list of product prices from lowest to highest automatically.

When you want to show only the sales records for a specific region from a big table.

When you want to create a dynamic dropdown list that updates as data changes.

When you want to quickly clean up data by removing repeated entries.

Steps

Step 1: Click

- cell where you want the results to start

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

Formula is entered and ready to run

💡 Start with =SORT(range) to sort data, =FILTER(range, condition) to filter, or =UNIQUE(range) to get unique values

Step 3: Press

- Enter key

The formula runs and spills results into adjacent cells automatically

Step 4: Check

- cells below and to the right of the formula cell

You see the full list of sorted, filtered, or unique data without extra formulas

Step 5: Modify

- the formula if needed

Results update automatically when source data changes

Before vs After

Before

A column with repeated customer names like: John, Mary, John, Alex, Mary

After

A list showing unique customer names only: John, Mary, Alex

Settings Reference

Calculation Options

📍 Formulas tab > Calculation group > Calculation Options

Ensures formulas recalculate automatically so dynamic arrays update when data changes

Default: Automatic

Spill Range

📍 Formula cell and adjacent cells

Dynamic arrays need empty cells next to the formula cell to show all results

Default: Empty cells to spill into

Common Mistakes

Entering a dynamic array formula but cells next to it are not empty

Dynamic arrays need empty cells to spill results; blocked cells cause errors

Clear cells next to the formula cell before entering the formula

Using FILTER without a proper condition

FILTER needs a condition that returns TRUE or FALSE for each row to work

Write a condition like =FILTER(A2:A10, B2:B10="East") to filter by region East

Trying to edit part of the spilled array range

You can only edit the formula cell; other cells are output only

Edit the formula in the original cell to change the results

Summary

Dynamic arrays automatically fill multiple cells with results from one formula.

SORT, FILTER, and UNIQUE help organize and extract data easily without copying formulas.

Make sure cells next to the formula are empty so results can spill without errors.