Spreadsheet Practice

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…UseWhy
List each value onceUNIQUERemoves duplicates from a column or a whole table, and the list grows as new data arrives.
Show only the rows that meet a conditionFILTERReturns matching rows live, with AND and OR logic and a fallback when nothing matches.
Sort a table without touching the originalSORTReturns a sorted copy by any column, ascending or descending.
Sort by a column you don't want to showSORTBYSorts one range by another and takes several sort keys for tie-breaks.
Get the top or bottom few rowsTAKEWrap it around SORT for a live top-5 list.
Combine lists from two ranges or sheetsVSTACKPuts 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, North
  • F2 =UNIQUE(B2:B7,FALSE,TRUE) Contoso Customers who ordered only once
  • G2 =COUNTA(UNIQUE(B2:B7)) 4 How many different customers ordered

Full UNIQUE guide and practice →

All 21 dynamic array functions

FunctionWhat it doesPractice exercise
BYCOL functionExcel 2024 and Microsoft 365BYCOL 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 365BYROW 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 365CHOOSECOLS 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 365CHOOSEROWS 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 365DROP 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 365FILTER 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 365HSTACK 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 365LAMBDA 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 365MAKEARRAY 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 365MAP 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 365REDUCE 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 365SCAN 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 365SEQUENCE 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 365SORT 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 365SORTBY sorts one range by the values in another.Leaderboard names
TAKE functionExcel 2024 and Microsoft 365TAKE keeps the first or last rows (and columns) of a range and drops the rest.Top three deals
TOCOL functionExcel 2024 and Microsoft 365TOCOL 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 365TOROW 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 functionTRANSPOSE flips a range so rows become columns and columns become rows.Quarters down the side
UNIQUE functionExcel 2021, 2024 and Microsoft 365UNIQUE returns each distinct value in a range once, in the order it first appears.Distinct regions
VSTACK functionExcel 2024 and Microsoft 365VSTACK 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.

  1. UNIQUE & SORT9 min · Pro
  2. SORTBY, TAKE & SEQUENCE9 min · Pro
  3. VSTACK & HSTACK9 min · Pro
  4. LAMBDA with BYROW, MAP & SCAN12 min · Pro
  5. Self-building summary tables11 min · Pro
  6. Capstone: sales report15 min · Pro

Other function groups

Updated Checked by running: every formula on this page was run by our formula engine on .