Excel Advanced
Advanced 13 min read Excel Advanced

Dynamic Arrays and Array Formulas

Excel that recalculates like a query

Dynamic array functions — FILTER, SORT, UNIQUE, SEQUENCE and spilled references — turn Excel into a live query engine over ranges, replacing fragile fill-down formulas.

dynamic arraysFILTERSORTUNIQUEspill#SPILLLAMBDA
Prerequisites: Excel Essential Skills
Introduction

What is this?

Dynamic arrays return whole ranges that 'spill' into neighboring cells automatically. FILTER, SORT, UNIQUE, SEQUENCE and XLOOKUP-array forms let you express range queries in one formula, with spilled results updating live as source data changes. LAMBDA then packages repeatable logic into custom, nameable functions.

Motivation

Why do you need it?

  • One formula replaces drag-down ranges and static ctrl-shift-enter arrays.
  • Results recalc live: filter and sort react the moment source data changes.
  • Expressions read like the query they are — no fragile helper columns.
  • Array-powered formulas compute over the whole range with clearer intent.
Think about it

Mental model

Imagine

A fountain that re-paints its shape as the pool fills

Think of

Dynamic arrays

You specify the fountain (formula) once; it continuously resizes to the water (data) it receives. No manual refitting with every drop — that's the spilled range.

Mechanics

How it works

  • A dynamic array function returns an array; with dynamic array behavior, Excel spills results across rows/columns.
  • FILTER(range, include) returns matching rows; SORT/SORTBY order them; UNIQUE dedupes.
  • SEQUENCE(n, c, start, step) builds grids for lists, calendars and simulation inputs.
  • Spilled references (#) let later formulas consume the entire dynamic output: =SUM(A2#).
  • LAMBDA(named params, expression) creates reusable mini-functions you can name and pass ranges into.

One dynamic formula, live output.

Source range
Dynamic formula
Spill region
Consuming formulas
Hands-on

Examples

A live filtering query

A live filtering query
// Top 5 regions by revenue, live
=SORT(Sales, 4, -1)                       // sort whole table by col 4 desc
=TAKE(SORT(Sales,4,-1), 5)                // first 5 rows only
 
// Unique customers + their totals live
=UNIQUE(Sales[user_id])
=SUMIFS(Sales[amount], Sales[user_id], A2#)  // spilled reference
 
// Custom function: LAMBDA once, reuse everywhere
=LAMBDA(a,b, (a-b)/b) (ACTUAL, BUDGET)

These spill dynamically as the Sales table grows — the same formula keeps answering 'top 5 now' with zero maintenance.

Real world

In production

  • A live leaderboard of top accounts by margin that re-sorts itself as new rows land.
  • A dropdown-driving UNIQUE list that never needs 'add that new region' maintenance.
  • A scenario grid built from SEQUENCE for break-even and what-if simulation over 100 inputs.
Guidelines

Best practices

  • Use # references so formula ranges follow the spill.
  • Keep dynamic arrays in one contiguous area; anything in the spill path causes #SPILL!.
  • Name recurring LAMBDA expressions for readability and reuse.
  • Beware full-column references with FILTER — scope to the actual Table.
Watch out

Common mistakes

  • Writing static helper columns where FILTER/UNIQUE would update itself.
  • Breaking a spill by inserting values into its predicted region.
  • Using array formulas expecting CSE behavior instead of the new native spill.
  • Filtering an entire column and spilling hundreds of thousands of blanks.
When things break

Debugging tips

  • #SPILL! — delete the blocking content in the spill range.
  • #CALC! or nested-array errors — your intermediate expressions construct incompatible shapes.
  • SORT orders text/dates oddly — check the sort column's type before ordering.
  • Preview a LAMBDA with a tiny range before generalizing it.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which formula returns only unique values from a range?

2

Dynamic array spills stay static once computed.

Go deeper

Official documentation

Microsoft — dynamic arrays and spill
docs
Microsoft — LAMBDA function
docs