## About this lesson

An introduction to the new Dynamic Arrays capability added to Microsoft 365.

## Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Intro to DAs.xlsx26.1 KB Intro to DAs - Complete.xlsx

26.7 KB

## Quick reference

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

### Instructions

#### Availability

- 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

#### #SPILL Errors

- 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

#### New Functions

- =UNIQUE(
*array*) will generate a list of unique values from the array input - =SORT(
*array,[sort_index],[sort_order],[by_col]*)- 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.