What Spilling Means

Most formulas you have written return a single value into a single cell. Dynamic arrays change that. One formula can return a whole list or grid of results, and the spreadsheet automatically places each result into its own cell.

This automatic placement is called spilling. You type the formula once, press Enter, and the results flow out across nearby cells like water filling a tray.

Suppose A1:A5 holds the numbers 1 through 5. In cell C1 type =A1:A5*10 and press Enter.

Instead of one answer, you get five: 10, 20, 30, 40, 50, stacked down C1:C5. You only typed the formula in C1, but the result spilled into the cells below it automatically.

The block of cells a formula fills is called its spill range. Only the top-left cell, called the anchor, actually contains the formula. The other cells are filled automatically and show a faint blue border when you select the anchor.

You cannot edit the spilled cells directly. If you try, you get an error. To change the output, you edit the formula in the anchor cell.

Because a spill can grow or shrink, you reference the whole result with the spill operator: a hash sign after the anchor address.

If a formula in C1 spills down to C5, then C1# means the entire spilled range. Use it elsewhere like =SUM(C1#) and the total will always include every spilled value, even if the spill grows later.

The real power of spilling is that it is dynamic. If you add a new number to the source range, the spill range automatically grows to include it. Remove a value and the spill shrinks.

You never have to copy a formula down again. The single anchor formula adjusts its output size to match the data it reads.

Many modern functions are built to return arrays and therefore spill:

• FILTER returns matching rows.
• UNIQUE returns a distinct list.
• SORT and SORTBY reorder a range.
• SEQUENCE generates a list of numbers.

You will meet FILTER and UNIQUE in this course. Each one outputs many results from a single formula.

Imagine column A lists regions: East, West, East, North, West. To get a clean list of distinct regions, type one formula:

=UNIQUE(A2:A6)

It spills three results: East, West, North. Add another region to the source and the spilled list grows on its own. One formula, an always-current list.

Spills are not limited to going down. A formula can spill across columns, or even fill a two-dimensional grid.

For example =SEQUENCE(3,4) spills a block 3 rows tall and 4 columns wide, filling 12 cells with the numbers 1 to 12. The direction and shape depend entirely on the array the formula produces.

Dynamic arrays and spilling work in Excel for Microsoft 365 and Excel 2021 and later. Google Sheets has supported array spilling for years too.

Older versions like Excel 2016 and 2019 do not spill. There, array formulas had to be confirmed with Ctrl+Shift+Enter and could not grow automatically. If your formulas refuse to spill, check your version first.

Before dynamic arrays, you would write =B2*C2 in one cell and drag it down hundreds of rows. With spilling you write it once.

The formula =B2:B100*C2:C100 spills one result per row across the whole range. Maintenance becomes trivial: there is a single formula to read and fix, not hundreds of copies that might drift out of sync.

Think of a spilling formula as a small program that outputs a shape of data, not just one number. The spreadsheet then lays that shape into the grid starting at the anchor cell.

Your job is to make sure there is empty room for the shape to land. The next lessons show how to produce useful shapes with FILTER and UNIQUE, and what to do when the landing spot is blocked.

Test your understanding of spilling.

You learned that a dynamic array formula can return many results, and that the spreadsheet automatically lays them out in a spill range.

• Only the anchor cell holds the formula.
• Reference the whole spill with the # operator, like C1#.
• Spills grow and shrink with the source data automatically.
• Functions like FILTER, UNIQUE, SORT, and SEQUENCE all spill.

Next, you will use FILTER to return only the rows you want.