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
- 10
- With a graded exercise
- All 10
- 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 10 dynamic array functions
| Function | What it does | Practice exercise |
|---|---|---|
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 |
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 |
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
- FILTER10 min · Pro
- Capstone: sales report15 min · Pro