Subscriber only lesson.
Sign up to this course to view this lesson.
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.