# Excel dynamic array functions

> Dynamic array functions return a whole list or table from one formula.

Source: https://spreadsheetpractice.com/functions/category/dynamic-arrays · Updated October 29, 2026

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?.

## Which one to use

| You want to | Use | Why |
|---|---|---|
| List each value once | [UNIQUE](https://spreadsheetpractice.com/functions/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](https://spreadsheetpractice.com/functions/filter) | Returns matching rows live, with AND and OR logic and a fallback when nothing matches. |
| Sort a table without touching the original | [SORT](https://spreadsheetpractice.com/functions/sort) | Returns a sorted copy by any column, ascending or descending. |
| Sort by a column you don't want to show | [SORTBY](https://spreadsheetpractice.com/functions/sortby) | Sorts one range by another and takes several sort keys for tie-breaks. |
| Get the top or bottom few rows | [TAKE](https://spreadsheetpractice.com/functions/take) | Wrap it around SORT for a live top-5 list. |
| Combine lists from two ranges or sheets | [VSTACK](https://spreadsheetpractice.com/functions/vstack) | Puts ranges one under another so you can sort, dedupe or total them as one list. |

## All 21 dynamic array functions

| Function | What it does | Available in |
|---|---|---|
| [BYCOL](https://spreadsheetpractice.com/functions/bycol) | BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across. | Excel 2024 and Microsoft 365 |
| [BYROW](https://spreadsheetpractice.com/functions/byrow) | BYROW runs a LAMBDA on each row of a range and returns one result per row, all from one spilling formula. | Excel 2024 and Microsoft 365 |
| [CHOOSECOLS](https://spreadsheetpractice.com/functions/choosecols) | 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. | Excel 2024 and Microsoft 365 |
| [CHOOSEROWS](https://spreadsheetpractice.com/functions/chooserows) | 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. | Excel 2024 and Microsoft 365 |
| [DROP](https://spreadsheetpractice.com/functions/drop) | DROP removes rows or columns from the start or end of a range and returns what is left. | Excel 2024 and Microsoft 365 |
| [FILTER](https://spreadsheetpractice.com/functions/filter) | FILTER returns only the rows of a range that pass a test, such as every order from the West region. | Excel 2021, 2024 and Microsoft 365 |
| [HSTACK](https://spreadsheetpractice.com/functions/hstack) | 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. | Excel 2024 and Microsoft 365 |
| [LAMBDA](https://spreadsheetpractice.com/functions/lambda) | LAMBDA creates your own function from a formula: name the inputs, then write the calculation, as in LAMBDA(price, rate, price*(1+rate)). | Excel 2024 and Microsoft 365 |
| [MAKEARRAY](https://spreadsheetpractice.com/functions/makearray) | MAKEARRAY builds an array of a given number of rows and columns by calling a LAMBDA with each row and column number. | Excel 2024 and Microsoft 365 |
| [MAP](https://spreadsheetpractice.com/functions/map) | 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. | Excel 2024 and Microsoft 365 |
| [REDUCE](https://spreadsheetpractice.com/functions/reduce) | 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. | Excel 2024 and Microsoft 365 |
| [SCAN](https://spreadsheetpractice.com/functions/scan) | 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. | Excel 2024 and Microsoft 365 |
| [SEQUENCE](https://spreadsheetpractice.com/functions/sequence) | SEQUENCE generates a list or grid of numbers from one formula: 1 to 10, every fifth number, or a countdown. | Excel 2021, 2024 and Microsoft 365 |
| [SORT](https://spreadsheetpractice.com/functions/sort) | SORT returns a sorted copy of a range, ordered by any column you choose, smallest to largest or the reverse. | Excel 2021, 2024 and Microsoft 365 |
| [SORTBY](https://spreadsheetpractice.com/functions/sortby) | SORTBY sorts one range by the values in another. | Excel 2021, 2024 and Microsoft 365 |
| [TAKE](https://spreadsheetpractice.com/functions/take) | TAKE keeps the first or last rows (and columns) of a range and drops the rest. | Excel 2024 and Microsoft 365 |
| [TOCOL](https://spreadsheetpractice.com/functions/tocol) | TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column. | Excel 2024 and Microsoft 365 |
| [TOROW](https://spreadsheetpractice.com/functions/torow) | TOROW turns a range or array into a single row, reading across each row unless you ask for column by column. | Excel 2024 and Microsoft 365 |
| [TRANSPOSE](https://spreadsheetpractice.com/functions/transpose) | TRANSPOSE flips a range so rows become columns and columns become rows. | Every current version |
| [UNIQUE](https://spreadsheetpractice.com/functions/unique) | UNIQUE returns each distinct value in a range once, in the order it first appears. | Excel 2021, 2024 and Microsoft 365 |
| [VSTACK](https://spreadsheetpractice.com/functions/vstack) | 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. | Excel 2024 and Microsoft 365 |

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
