Introduction to Dynamic Arrays
Introducing Dynamic Arrays and how they can expand Excel’s capabilities.
When to use
When you need to return more than one item to a block of cells and need it to dynamically update in future.
- Dynamic arrays were released to Microsoft 365 AFTER Excel 2019 was released
- This means that you must have a version of Excel newer than Excel 2019 to use them
Creating a Dynamic Array
- Write a formula that refers to more than one cell (i.e. =A5:A10 )
- Write a formula that refers to a table column/row or the entire table
- If an array’s output is “blocked” it will return a #SPILL! Error
- Select the cell to see the intended spill range
- Select the flyout menu to locate the blocking cell
- Clear the blocking cell in order to allow the array to spill into the worksheet
- =UNIQUE(array) will generate a list of unique values from the array input
- Will sort the array in ascending order by default
- Can be used to sort by other arrays, in descending order or even by columns/rows
- =TRANSPOSE(array) will rotate the array output by 90 degrees
Referring to Array outputs in other formulas
- To refer to a Dynamic Array’s output:
- Set your formula to refer to the address of the first cell, and
- Add a # character at the end
- i.e =A5# will refer to the dynamic array that spills from A5
Lesson notes are only available for subscribers.