Lesson 9: One formula can return many cells
In current Microsoft 365 and Excel for the web, a dynamic-array formula spills results into adjacent cells. Keep the spill area empty. An occupied cell causes #SPILL!. Older perpetual editions may not include all functions on this page.
If A2:A6 contains names and B2:B6 contains regions, =SORT(UNIQUE(A2:A6)) returns a sorted list of distinct names.
Dynamic-array and reusable formula reference
25 formulas shown
| Function | Purpose | Example |
|---|---|---|
| FILTER | Returns rows or columns meeting a Boolean condition. | =FILTER(A2:D100,D2:D100>=1000,"None"). |
| SORT | Sorts an array by a selected column. | =SORT(A2:D20,4,-1) sorts by column 4 descending. |
| SORTBY | Sorts one array using one or more separate sort arrays. | =SORTBY(A2:C20,C2:C20,-1). |
| UNIQUE | Returns distinct rows or columns. | =UNIQUE(A2:A100). |
| SEQUENCE | Generates a sequential array. | =SEQUENCE(5,1,1,1) spills 1 to 5. |
| RANDARRAY | Generates an array of random numbers. | =RANDARRAY(3,2,1,10,TRUE). |
| TAKE / DROP | Keeps or removes rows/columns from array edges. | =TAKE(A2:D100,10). |
| CHOOSECOLS | Selects columns by position. | =CHOOSECOLS(A2:F20,1,4,6). |
| CHOOSEROWS | Selects rows by position; negative positions count from end. | =CHOOSEROWS(A2:D20,1,-1). |
| VSTACK / HSTACK | Combines arrays vertically or horizontally. | =VSTACK(A2:C5,E2:G5). |
| TOCOL / TOROW | Reshapes an array into one column or one row. | =TOCOL(A2:C10,1) ignores blanks. |
| WRAPROWS / WRAPCOLS | Wraps a vector into rows or columns of a chosen size. | =WRAPROWS(A2:A13,4). |
| EXPAND | Expands an array to a requested number of rows/columns. | =EXPAND(A2:B3,5,4,"-"). |
| TEXTSPLIT | Splits text by column and/or row delimiters. | =TEXTSPLIT(A2,",",";"). |
| TEXTBEFORE / TEXTAFTER | Returns text before or after a delimiter. | =TEXTAFTER(A2,"-"). |
| LET | Names intermediate calculations inside one formula. | =LET(qty,B2,price,C2,qty*price). |
| LAMBDA | Defines a reusable custom function without VBA. | =LAMBDA(x,x*1.1)(A2) adds 10%. |
| MAP | Maps a LAMBDA calculation over array values. | =MAP(A2:A10,LAMBDA(x,x*1.1)). |
| BYROW / BYCOL | Calculates one result for each row or column. | =BYROW(B2:D10,LAMBDA(r,SUM(r))). |
| REDUCE | Accumulates an array into one result. | =REDUCE(0,A2:A10,LAMBDA(a,v,a+v)). |
| SCAN | Returns each intermediate accumulator result. | =SCAN(0,A2:A10,LAMBDA(a,v,a+v)) creates running totals. |
| MAKEARRAY | Builds an array from row and column index calculations. | =MAKEARRAY(3,3,LAMBDA(r,c,r*c)). |
| ISOMITTED | Tests an optional LAMBDA parameter. | =LAMBDA(x,y,IF(ISOMITTED(y),x,x+y))(2). |
| TRANSPOSE | Swaps rows and columns in a spilled array. | =TRANSPOSE(A2:C4). |
| HSTACK | Appends arrays side by side; mismatched rows may pad with #N/A. | =HSTACK(A2:A5,C2:C5). |
Filter and sort matching rows: =SORT(FILTER(A2:D100,D2:D100>=1000,"None"),4,-1)
LET and LAMBDA patterns
LET makes a long formula easier to read by naming values once. LAMBDA can be saved in Name Manager to create a workbook function. Start with a small test range, confirm spill dimensions, and document any custom names so other users can maintain the workbook.
Progress stays in this browser.