Excel dynamic array functions
Dynamic array functions return a whole list or table from one formula. The results spill into the empty cells below and to the right, and they update the moment the source data changes. Most need Excel 2021 or later, and TAKE, DROP, VSTACK and HSTACK need Excel 2024 or Microsoft 365; older versions show #NAME?.
- Functions
- 21
- With a graded exercise
- All 21
- Start with
UNIQUE
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| List each value once | UNIQUE | Removes duplicates from a column or a whole table, and the list grows as new data arrives. |
| Show only the rows that meet a condition | FILTER | Returns matching rows live, with AND and OR logic and a fallback when nothing matches. |
| Sort a table without touching the original | SORT | Returns a sorted copy by any column, ascending or descending. |
| Sort by a column you don't want to show | SORTBY | Sorts one range by another and takes several sort keys for tie-breaks. |
| Get the top or bottom few rows | TAKE | Wrap it around SORT for a live top-5 list. |
| Combine lists from two ranges or sheets | VSTACK | Puts ranges one under another so you can sort, dedupe or total them as one list. |
Try UNIQUE live
The most useful dynamic array function, on a sample sheet you can edit. Change a value and watch the results update.
E2=UNIQUE(C2:C7) West Each region once: West, East, NorthF2=UNIQUE(B2:B7,FALSE,TRUE) Contoso Customers who ordered only onceG2=COUNTA(UNIQUE(B2:B7)) 4 How many different customers ordered
All 21 dynamic array functions
| Function | What it does | Practice exercise |
|---|---|---|
BYCOL functionExcel 2024 and Microsoft 365 | BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across. | Column totals |
BYROW functionExcel 2024 and Microsoft 365 | BYROW runs a LAMBDA on each row of a range and returns one result per row, all from one spilling formula. | Best month per rep |
CHOOSECOLS functionExcel 2024 and Microsoft 365 | CHOOSECOLS returns only the columns you name from a range or array, in the order you name them, so 3,1 puts the third column first. | Phone list |
CHOOSEROWS functionExcel 2024 and Microsoft 365 | CHOOSEROWS returns the rows you name from a range or array: 1 is the first row and -1 the last, so CHOOSEROWS(range,-1) always gives the latest entry. | First and last month |
DROP functionExcel 2024 and Microsoft 365 | DROP removes rows or columns from the start or end of a range and returns what is left. | Lose the total row |
FILTER functionExcel 2021, 2024 and Microsoft 365 | FILTER returns only the rows of a range that pass a test, such as every order from the West region. | Filter by region |
HSTACK functionExcel 2024 and Microsoft 365 | HSTACK places ranges side by side to build one table, so you can pick columns that aren't next to each other, put them in a new order, or add a calculated column, all in one formula that updates with the source. | Name and revenue |
LAMBDA functionExcel 2024 and Microsoft 365 | LAMBDA creates your own function from a formula: name the inputs, then write the calculation, as in LAMBDA(price, rate, price*(1+rate)). | Your own discount function |
MAKEARRAY functionExcel 2024 and Microsoft 365 | MAKEARRAY builds an array of a given number of rows and columns by calling a LAMBDA with each row and column number. | Price grid |
MAP functionExcel 2024 and Microsoft 365 | MAP runs a LAMBDA on each value of an array, or on matching values from several arrays, and returns an array of the same size. | Flag both conditions |
REDUCE functionExcel 2024 and Microsoft 365 | REDUCE walks through every value in an array, passing a running result and the next value to a LAMBDA, and returns the final running result. | Count large orders |
SCAN functionExcel 2024 and Microsoft 365 | SCAN works like REDUCE, passing a running result and each value to a LAMBDA, but it returns the running result at every step instead of just the last one. | Running total in one formula |
SEQUENCE functionExcel 2021, 2024 and Microsoft 365 | SEQUENCE generates a list or grid of numbers from one formula: 1 to 10, every fifth number, or a countdown. | Weekly check-ins |
SORT functionExcel 2021, 2024 and Microsoft 365 | SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse. | Sorted rep list |
SORTBY functionExcel 2021, 2024 and Microsoft 365 | SORTBY sorts one range by the values in another. | Leaderboard names |
TAKE functionExcel 2024 and Microsoft 365 | TAKE keeps the first or last rows (and columns) of a range and drops the rest. | Top three deals |
TOCOL functionExcel 2024 and Microsoft 365 | TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column. | One list of names |
TOROW functionExcel 2024 and Microsoft 365 | TOROW turns a range or array into a single row, reading across each row unless you ask for column by column. | Names across a row |
TRANSPOSE function | TRANSPOSE flips a range so rows become columns and columns become rows. | Quarters down the side |
UNIQUE functionExcel 2021, 2024 and Microsoft 365 | UNIQUE returns each distinct value in a range once, in the order it first appears. | Distinct regions |
VSTACK functionExcel 2024 and Microsoft 365 | VSTACK stacks ranges on top of each other into one list, such as this month's orders under last month's or one region's reps under another's. | One leaderboard from two teams |
Learn them in order
These functions are taught step by step in the Dynamic arrays track. Each lesson ends with graded exercises on real-looking data.
- UNIQUE & SORT9 min · Pro
- SORTBY, TAKE & SEQUENCE9 min · Pro
- VSTACK & HSTACK9 min · Pro
- LAMBDA with BYROW, MAP & SCAN12 min · Pro
- Self-building summary tables11 min · Pro
- Capstone: sales report15 min · Pro
Other function groups
Updated Checked by running: every formula on this page was run by our formula engine on .