Dynamic Arrays in Excel Explained Simply

Dynamic arrays in Excel are formulas that return many values at once and spill them into the cells around the formula. You type =UNIQUE(B2:B9) in one cell, press Enter, and the full list appears below it, resizing whenever the data changes. This guide explains spilling, the # spill reference, the #SPILL! error and version support in plain English, for anyone meeting FILTER, SORT or UNIQUE for the first time.

What are dynamic arrays in Excel?#

A dynamic array formula returns an array, which is simply a list or grid of values, instead of a single value. Excel writes the first value in the cell where you typed the formula and the rest in the cells beside or below it. That behaviour is called spilling, and the cells it fills are the spill range.

Before dynamic arrays, one formula meant one result in one cell. To list unique regions you needed helper columns or a complicated formula confirmed with Ctrl+Shift+Enter. Now one short formula does it.

Version note: dynamic arrays are available in Microsoft 365, Excel 2021, Excel 2024, Excel for the web and current versions of Excel for Mac. Excel 2019 and older don't spill.

How spilling works#

Type the formula in one cell and Excel fills as many cells as the result needs. Here is a list of orders:

In E2, type:

=SORT(UNIQUE(B2:B9))

UNIQUE returns each region once, SORT puts them A to Z, and the four results spill into E2:E5. A few things to notice:

• Only E2 holds the formula. Click E3 and the formula bar shows it greyed out. You can't edit or delete E3 on its own.
• A blue border marks the spill range when you select any cell in it.
• It resizes itself. Add an order from a new region and the list grows by one row.

The core dynamic array functions#

These functions were designed to return arrays:

XLOOKUP can spill too, when you ask it to return several columns. Microsoft 365 and Excel 2024 add more, such as TEXTSPLIT, VSTACK, HSTACK, TAKE and DROP.

The FILTER function guide is a good place to start, because FILTER replaces many manual filtering jobs with a formula that updates by itself.

Array maths: formulas on whole ranges#

Dynamic arrays also change how ordinary maths works on ranges. Multiply two columns in one formula and you get one result per row:

=B2:B6*C2:C6

With quantities in B and prices in C, this spills five line totals. Wrap it in SUM to get one number, =SUM(B2:B6*C2:C6), without Ctrl+Shift+Enter. Comparisons work the same way: =C2:C9>1000 spills a column of TRUE and FALSE, which is exactly the kind of test FILTER uses.

How to refer to a spill with#

Type # after the first cell of a spill to refer to the whole result. This is called a spill reference:

=COUNTA(E2#)

E2# means "everything E2 spilled". If the list grows from four regions to five, the count updates too. A fixed range like E2:E5 would miss the new one.

Spill references are useful for chaining formulas. A SUMIFS can total each region in the list, =SUMIFS(C2:C9,B2:B9,E2#), and it spills one total per region next to the list. A drop-down list can use a spill as its source too: in Data > Data Validation, set Allow to List and the source to =$E$2#.

What the #SPILL! error means#

#SPILL! means the formula can't put its results where they need to go. The most common causes:

• Something is in the way. A cell in the spill range isn't empty, even if it only holds a space. Clear it.
• Merged cells in the spill range. Unmerge them.
• The formula is inside an Excel table. Tables don't allow spills. Move the formula outside the table.
• The result is too big to fit before the edge of the sheet.

Click the warning icon next to the error and choose Select Obstructing Cells to jump to the blocker. Our guide to the #SPILL! error covers each cause with fixes.

Dynamic arrays in older Excel files#

Dynamic arrays change how some older formulas behave, so Excel protects them in two ways.

The @ sign. If you open an older workbook, Excel may add @ to formulas such as =@A2:A10. The @ is the implicit intersection operator: it tells Excel to return a single value, as older versions did, instead of spilling. The formula gives the same result it always did.

Opening a new file in old Excel. If you share a workbook with someone on Excel 2019, a spilled formula shows as an old-style array formula in braces, and functions that didn't exist then, such as FILTER or UNIQUE, show #NAME?. Check which version your team or a job test uses before relying on dynamic arrays.

Common dynamic array combinations#

Most real work combines two or three functions. These come up again and again in reports and interviews:

For a ranked list, see our guide to a top 5 list with SORT and FILTER. Data analyst roles lean on these functions heavily; our list of Excel skills for data analysts shows where they fit alongside pivot tables and lookups.

Practise it#

Try the exercise above to spill a sorted list of regions. Then practise in the UNIQUE lesson, where you list and count distinct customers, and the FILTER lesson, where you pull matching rows with AND and OR conditions in a live spreadsheet.