Introduction
Traditional formulas return one value into one cell. Since Excel 2021 and in Microsoft 365, a formula can return a whole list or table of values, which spills into the neighbouring cells. This changes how Excel is used: a single formula can filter a list, sort it, remove duplicates or generate a series, and it updates the moment the data changes. This lesson covers these dynamic array functions, and LET and LAMBDA, which make advanced formulas readable and reusable.
How it works
Type =UNIQUE(B2:B100) into one cell and the distinct values fill the cells below: that is a spill range, outlined in blue when selected. Only the first cell holds the formula; the others show results. Refer to the whole range with a spill reference, the first cell followed by #: =COUNTA(E2#) counts however many values spilled, even as the list grows or shrinks.
If anything is in the way, the formula returns #SPILL!. Clear the cells it needs, or move the formula. Spilling does not work inside an Excel Table.
The main functions:
=FILTER(A2:C100,C2:C100>500,"None")returns the rows where column C is over 500, or None if there are none. Conditions combine with*for AND and+for OR:(B2:B100="North")*(C2:C100>500). Without the third argument, an empty result gives#CALC!.=SORT(A2:C100,3,-1)sorts by the third column, largest first.SORTBY(A2:A100,C2:C100,-1)sorts one range by another.=UNIQUE(B2:B100)returns distinct values; combine as=SORT(UNIQUE(B2:B100)).=SEQUENCE(5)returns 1 to 5 down a column;=SEQUENCE(3,4,10,10)returns a 3 by 4 grid starting at 10 and counting in tens.
Older array formulas had to be confirmed with Ctrl+Shift+Enter and showed braces in the formula bar; dynamic-array Excel needs only Enter. A file with spilled formulas opened in an older version shows them as legacy array formulas.
LET names values inside a formula: =LET(total,SUM(C2:C100),count,COUNT(C2:C100),IF(count=0,0,total/count)). Each name is calculated once and the final argument uses them, which makes long formulas readable and often faster. LET is in Excel 2021 and Microsoft 365.
LAMBDA, in Microsoft 365, defines a function. In Formulas, Name Manager, create a name such as GROSS that refers to =LAMBDA(net,rate,net*(1+rate)); then =GROSS(B2,0.15) works anywhere in the workbook. Helper functions such as MAP, BYROW and REDUCE apply a LAMBDA to each item or row.
Example
A sales list in A2:C6 has Rep, Region and Amount; the report should list each region once, and the North sales over 400, largest first.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Amount |
| 2 | Rina | North | 500 |
| 3 | Arif | South | 300 |
| 4 | Zoya | North | 700 |
| 5 | Omar | South | 450 |
| 6 | Lena | North | 350 |
Line by line
- In E2 type
=SORT(UNIQUE(B2:B6)). - In G2 type
=SORT(FILTER(A2:C6,(B2:B6="North")*(C2:C6>400),"None"),3,-1). - In E5 type
=ROWS(E2#)to count the regions that spilled.
Expected result
E2:E3 show North and South. G2:I3 show two rows: Zoya, North, 700 and then Rina, North, 500; Lena's 350 is left out. E5 shows 2. Changing Omar's region to East adds East to the region list at once, and E5 becomes 3.
In the real world
Dynamic arrays power modern dashboards: drop-down lists fed by SORT(UNIQUE(...)), live top-10 tables with TAKE and SORT, and filtered views that update without PivotTables or macros. LET and LAMBDA let teams share tested calculations, such as a company's commission rule, as one named function instead of copies of a long formula.
Common mistakes
- Leaving data in the way of a spill, which gives
#SPILL!. - Referring to a fixed range such as E2:E10 instead of E2#, which misses rows when the list grows.
- Using dynamic arrays in files that colleagues open in Excel 2016 or 2019, where they do not work.
- Forgetting FILTER's third argument, so an empty result shows
#CALC!.
Best practices
- Leave empty space below and beside spilling formulas.
- Use spill references (#) in anything that depends on a dynamic result.
- Wrap long formulas in LET, and name reusable calculations with LAMBDA when everyone has Microsoft 365.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Confirm a legacy array formula (older Excel) | Ctrl+Shift+Enter | Command+Shift+Return |
| Open the Name Manager | Ctrl+F3 | — |
| Expand the formula bar for long LET formulas | Ctrl+Shift+U | Control+Shift+U |
| New line inside a formula | Alt+Enter | Option+Return |
Summary
In Excel 2021 and Microsoft 365, formulas can return many values that spill into neighbouring cells, referred to as a whole with #. FILTER, SORT, SORTBY, UNIQUE and SEQUENCE build live lists, #SPILL! means something is in the way, LET names parts of a formula, and LAMBDA in Microsoft 365 turns a formula into a reusable named function.
Quick check
Key takeaways
- In Excel 2021 and Microsoft 365, one formula can return a whole list that spills into the cells below and beside it.
- FILTER, SORT, SORTBY, UNIQUE and SEQUENCE replace many older multi-step techniques.
- LET names parts of a formula; LAMBDA (Microsoft 365) turns a formula into a reusable function.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.