If you’re familiar with array formulas, then the simplicity of the new Excel Dynamic Arrays will be a breath of fresh air. And if you’ve always shied away from array formulas then Dynamic Arrays are for you.
Excel no longer requires you to enter array formulas with CTRL+SHIFT+ENTER and formulas that return multiple results will now automatically ‘spill’ into the cells below and or to the right, as you can see below:
Before Dynamic Arrays the ROW formula above would return 1 because it can only display the first value in a single cell, but now that it can ‘spill’ into the cells below, it returns the values 1 through 4.
Previously we would have to select cells A1:A4 prior to entering the ROW formula and then press CTRL+SHIFT+ENTER to return the array. I think you’ll agree it’s now far simpler.
Also notice in the formula bar above that there aren’t any curly braces around the formula to indicate it’s an array formula. And when I move my cell selection away from A1 you can see the formula bar now contains a ghosted formula, indicating I can’t modify it.
This new ‘spill’ result gives Excel array functions transparency and makes them more easily discoverable, getting users up and running with array formulas more quickly than ever before.
Array formulas are no longer limited to super users, they are for everyone!
Note: Dynamic Arrays are only available in Excel 2021 onward and with a Microsoft 365 license.
Download the Workbook
If you have Office 365, or plan to get it now that you’ve seen how amazing Dynamic Arrays are, you can download the workbook for this lesson here:
Enter your email address below to download the sample workbook.
Let’s look at some of the cool new functions.
Extracting unique or distinct values from a list is a common requirement and up until now you could either use a complicated array formula like this:
However, now that we have the UNIQUE function the formula above can be replaced with this:
You’ve got to admit, it doesn’t get much easier than that!
The syntax for the UNIQUE function is:
=UNIQUE(array, [by_col], [occurs_once])
array is the range or array you want the unique values returned from.
by_col is an optional logical value (TRUE/FALSE) and allows you to compare values by row (FALSE), or by column (TRUE). You can see in the example above that I omitted it, which means it will default to FALSE and sort by row.
occurs_once is also an optional logical value (TRUE/FALSE) and allows you to find the truly unique values, i.e. the values that only occur once (TRUE), or all distinct values (FALSE). If you omit this argument, as I have done above, it will default to FALSE and return a distinct list.
It’s nice to be able to easily get a distinct list with the UNIQUE function, but what if you also want it sorted. The old array formula approach was something scary like this:
=IFERROR(INDEX($A$26:$A$30, MATCH(SMALL(IF(COUNTIF($B$26:B5, $ A$26:$A$30)=0, COUNTIF($A$26:$A$30, "<"&$ A$26:$A$30), ""), 1), COUNTIF($A$26:$A$30, "<"&$ A$26:$A$30), 0)),"")
But you’ll be pleased to know there’s a new SORT function that you can simply wrap around UNIQUE like so:
The example above is SORT in its simplest form, but the syntax for the SORT function has some more optional arguments:
=SORT(array, [sort_index], [sort_order], [by_col])
array is the range or array containing the values you want sorted.
sort_index is optional and indicates the row or column to sort by. When omitted it will default to sort by the first row or column in the array.
sort_order is optional. It’s a number; 1 for ascending and -1 for descending. If omitted, it will sort in ascending order.
by_col is an optional logical value (TRUE/FALSE) indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column. You can see in the example above that I omitted it, which means it will default to FALSE and sort by row.
Now that we have our nicely sorted unique list it makes sense that you might want to use it in a data validation list. To reference a spilled array we simply use the new Spilled Range Operator, #, as shown in the Data Validation ‘Source’ field below:
When we reference the spilled range using the # notation it will automatically adjust to include new cells as the spilled range grows, or exclude rows as the range contracts. In other words, it’s a dynamic reference. And I haven’t had to go near OFFSET or INDEX to create it!
Tip: The Spilled Range Operator gets automatically inserted if you select the whole spilled range of cells in a formula e.g.
=COUNTA(B26:B30) would be converted to =COUNTA(B26#) as you can see below:
Unfortunately, it doesn’t automatically insert the spilled range operator in the Data Validation List source field…yet.
The FILTER function returns a range filtered on criteria you define. In the example below, we have returned the records from the table in cells B7:F21 for ‘Stamps’, as selected in the data validation list in cell B25.
Notice the spill range is cells B31:F33 as denoted by the blue border around the cells.
In English the formula in cell B31; =FILTER(B7:F21,C7:C21=B25), reads:
Return the records from the table in cells B7:F21, where the values in cells C7:C21 = the value in cell B25, which is ‘Stamps’.
The syntax for FILTER is:
=FILTER(array, include, [if_empty])
array is the range or array containing the values you want to filter.
include is the logical test instructing FILTER which rows to include in the filtered result. It returns an array of Boolean values (TRUE/FALSE).
If_empty is optional. It’s the value to return if all values in the included array are empty.
Tip: We can filter by multiple criteria by adding logical tests e.g.
Filter for Stamps AND Support:
Filter for Stamps OR Paper
Dynamic Array Effect on Existing Functions
As I mentioned at the beginning, Dynamic Arrays have changed the way the Excel calc engine treats functions that can return a range, in that they can now spill. It also means that some functions that didn’t previously return arrays can also spill if you reference a range in an argument that is expecting a single value/cell (scalar).
For example, the COUNTIF formula below spills because the criteria argument is given the spilled range i.e. =COUNTIF(C7:C21, $B$25#)
Warning, the following content is advanced, and you don’t need to know it or understand it, so read on if you wish.
If you’re familiar with implicit intersection then you may be thinking that the COUNTIF formula above is relying on implicit intersection to return the correct count, but with dynamic arrays it’s not. You could move the COUNTIF formula anywhere in the worksheet and it would still return the spilled array, as you can see below where COUNTIF is now offset by 1 row to the list of count criteria:
In older versions of Excel this would break the COUNTIF formula.
In fact, this is one of the fundamental changes to the Excel calc engine. Implicit intersection is no longer the default. Instead if you want a formula to evaluate implicitly you need to prefix the range or formula that returns the range with the @ symbol.
For example, the COUNTIF formula example given earlier; =COUNTIF(C7:C21,$B$25#), would become:
If a user opens a workbook containing a dynamic array Excel will automatically convert it to a legacy array formula surrounded by curly braces. When you open it again in Office 365 it will remove the curly braces.
If you use one of the new array functions, or use the Spilled Range Operator (#), it will prefix it with _xlfn to indicate that the function is not supported in the version of Excel you are currently in. You can see an example of this in the Excel 2013 screenshot below:
As long as you don’t edit the formula Excel will display the results and you can reference these cells in other formulas etc. If you edit the formula it will break.
#SPILL! Errors - Range Isn’t Empty
If you have data in the path of a Spill you’ll get the #SPILL! Error like so:
Clicking on the warning icon will reveal a list of options for rectifying the problem. Or you can just delete the problem data and it will Spill.
Dynamic Array Limitations
There is limited support for references between workbooks. Dynamic Arrays require both workbooks to be open, otherwise you will get #REF! errors.
More Excel Dynamic Arrays
This is just the beginning and a taste of some of the Dynamic Array functions. Check out our comprehensive tutorials on some of the other new Dynamic Array functions:
|FILTER||Filter cells based on criteria.|
|RANDARRAY||Returns an array of random numbers between 0 and 1.|
|SEQUENCE||Returns list of sequential numbers that increment as specified.|
|SORT||Sort cells or arrays in ascending or descending order.|
|SORTBY||Sort a range or arrays based on criteria.|
|UNIQUE||Extract a unique or distinct list from a range or array.|
Array Shaping Functions
Note: these functions are only available to Microsoft 365 users.
|EXPAND||Expands or pads an array to a specified number of rows and columns.|
|TOROW||Returns the array in a single row. Useful for combining data across multiple columns and rows into a single row.|
|TOCOL||Returns the array in a single column. Useful for combining data across multiple columns and rows into a single column.|
|WRAPROWS||Lets you wrap (reshape) a row or column of values into rows, you specify the number of values in each row.|
|WRAPCOLS||Lets you wrap (reshape) a row or column of values into columns, you specify the number of values in each column.|
|DROP||Remove a specified number of contiguous rows or columns from the start or end of an array.|
|TAKE||Extract a specified number of contiguous rows or columns from the start or end of an array.|
|CHOOSEROWS||Extract rows from the specified column or columns.|
|CHOOSECOLS||Extract columns from the specified rows or rows.|
|VSTACK||Combine arrays arranged vertically into a new single array.|
|HSTACK||Combine arrays arranged horizontally into a new single array.|
Fellow Excel MVP, Bill Jelen (aka MrExcel) has written a whole book on them. He’s giving it away free until the end of the year. You can download it here.
And if you ever wondered how Excel evaluated array formulas before Dynamic Arrays I recommend you watch Joe McDaid’s presentation from the 2018 Business Applications Summit (from the 22:20 mark). In fact, even if you never wondered how Excel handled array formulas, I recommend you watch it because it will be the most enlightening 38 minutes you ever spend learning about Excel.
For any Google Sheets fans out there, while you may already be familiar with the Google versions of the SORT, FILTER and UNIQUE functions, I think you'll agree the new spilled range operator (#) is going to be super useful. And the way existing Excel functions now also spill will open up a host of solutions we can't even imagine right now. Exciting times ahead!