3.3 Dynamic arrays and spill formulas

What you'll be able to do

Write one formula that returns a whole list — filtered, sorted, with duplicates removed — and that resizes itself when the data changes. No more columns of helper formulas.

Before you start

Open the pivot sales workbook. Its sales Table feeds every example. These functions need a current Microsoft 365 Excel.

The work

1. Same either way: see spilling once. In an empty cell type =SORT(UNIQUE(Sales[Store])) and press Enter. The result flows into as many cells as it needs, and that area is called the spill range. You wrote one formula, and the cells below it hold nothing at all. Delete the top cell and the whole list goes.
2. Same either way: refer to a whole spill with the hash sign. If the list starts in E2, then =COUNTA(E2#) counts it, and E2# in any formula means the entire spill, whatever size it is right now. A dropdown list built on E2# grows by itself.
3. Same either way: learn the verbs, one at a time. =FILTER(Sales, Sales[Store]="Hyderabad") returns the matching rows. =SORT(range, 2, -1) orders by the second column, largest first. =SORTBY(names, amounts, -1) orders one list by another. =UNIQUE(range) removes duplicates. =SEQUENCE(12) makes the numbers 1 to 12, which is useful for dates and row numbers.
4. Same either way: put them inside one another and read them aloud from the inside out. =SORT(FILTER(Sales[Amount], Sales[Store]="Hyderabad"), 1, -1) says "Hyderabad's amounts, largest first". Each function hands its result to the next one.
5. Same either way: give FILTER its empty answer. =FILTER(range, condition, "none found") says what to show when nothing matches. Without that third part, no matches shows as #CALC!.
6. Same either way: #SPILL! means the result has nowhere to land. Something is sitting in the cells it needs: a stray value, a merged cell, or a Table, because a spill cannot land inside a Table. Click the warning symbol beside the error and choose Select Obstructing Cells. Clear what is in the way and the result appears.
7. Same either way: here is the habit that pays. Next time you reach for a helper column — first extract, then remove duplicates, then sort — write one combined formula instead. There is one cell to audit, nothing to fill down, and the result resizes with the data.

Try it

Build each of these in one cell. The store list in alphabetical order. Every Hyderabad sale, largest first, with an honest empty answer. The top month by total, using SORTBY over a summarised pair. A SEQUENCE of 1 to 12. Then break one on purpose by typing into the cells it needs, and repair the #SPILL!.

Check yourself