Dynamic Arrays in Excel

 

Dynamic array formulas, entered into a single cell, fill multiple cells. This behavior in Excel 365/2021 is called spilling.

Formula Example

Let's start with an easy example.

1. Lock the reference to cell E2 by placing a $ symbol in front of the column letter and row number.

Absolute Reference

2. To quickly copy the formula in cell C2 to the other cells, select cell C2, click on the lower right corner of cell C2 and drag it down to cell C7.

Copy Formula

Check:

Traditional Excel Formula

Explanation: the absolute reference ($E$2) stays the same, while the relative reference (B2) changes to B3, B4, B5, B6 and B7.

3. If you have Excel 365 or Excel 2021, simply enter the dynamic array formula shown below into cell C2.

Dynamic Array Formula

4. Press Enter.

Blue Border

Explanation: this dynamic array (DA) formula, entered into cell C2, fills multiple cells. This behavior in Excel 365/2021 is called spilling. If you select a cell in the range C2:C7, a blue border appears.

5. If you select a cell in the range C3:C7, the formula in the formula bar will be greyed out.

Formula Greyed Out

Dynamic Array Functions

Excel 365 and Excel 2021 offer many new dynamic array functions. Follow the links below to learn more about these functions.

1. The SORT function below sorts by the second column, in ascending order.

SORT function

Note: this dynamic array function, entered into cell F2, fills multiple cells. Wow!

2. The FILTER function below extracts all USA records.

FILTER function

3. The RANDARRAY function below generates random decimal numbers between 0 and 1. The array below consists of 5 rows and 2 columns.

RANDARRAY function

4. The SEQUENCE function below generates a two-dimensional array. Rows = 7, Columns = 4, Start = 0, Step = 5.

SEQUENCE function

5. The UNIQUE function below (with no extra arguments) extracts unique values.

UNIQUE function

6. The XLOOKUP function below looks up the ID and returns the first name, last name and salary.

XLOOKUP function

Note: if you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

7. The SORTBY function sorts a range based on the values in a corresponding range.

SORTBY function

Note: use 1 to sort in ascending order, use -1 to sort in descending order.

Old Array Formulas

Old array formulas still work. The good news is, if you have Excel 365 or Excel 2021, you don't have to press CTRL + SHIFT + ENTER anymore. Below you can find a few examples.

1. A traditional array formula finished by pressing CTRL + SHIFT + ENTER.

CSE formula

Explanation: this formula sums the 4 largest numbers.

2. The same formula in Excel 365/2021 finished by simply pressing Enter. Bye bye curly braces.

Bye Bye Curly Braces

3. A traditional TRANSPOSE function entered by preselecting the range E2:E4 and finished by pressing CTRL + SHIFT + ENTER.

Old TRANSPOSE function

Explanation: the TRANSPOSE function converts a horizontal range to a vertical range, or vice versa.

4. The same function in Excel 365/2021, entered into a single cell, spills to neighboring cells.

Function Spills to Neighboring Cells

Spill Range

To refer to the values inside the blue rectangle, also called the spill range, always use the first cell and a hash character.

1. For example, the UNIQUE function below, entered into cell C1, extracts unique values.

UNIQUE function

2. The COUNTA function below uses the spill range (C1#) to count unique values.

Spill Range in Excel 365/2021

3. If something is blocking the spill range, Excel displays the #SPILL! error.

#SPILL! error

Note: in this example, simply empty cell C6 to fix the #SPILL error.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.