This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we're exploring some of the possibilities offered by the newly-released dynamic array functions in Office 365.
This is new functionality that was added to Office 365 in December 2019. It is not available in other versions of Excel. However, similar functionality is available through Google Sheets - for more, see our comparative webinar on Dynamic arrays in Google Sheets and Excel.
What is a dynamic array?
The "old-fashioned" array functions (covered most recently in TOTW #182) allowed a formula to be modified so that it could spread over multiple cells, or a cell with a single input could be modified to take a range of inputs, each in turn. However, they had some awkward problems:
Dynamic arrays represent a complete overhaul of the whole business, and are significantly simpler and, as a result, more useful. They also come with a handful of brand-new special functions, designed specifically for working with dynamic arrays. But they are still very unfamiliar for anyone used to how regular Excel works. Let's examine some simple demonstrations.
The primary attribute of dynamic arrays is their ability to spill - that is, expand from the cell they are entered into and fill multiple cells. Here is an example of perhaps the simplest new dynamic array function, SEQUENCE:
SEQUENCE creates simple numeric sequences - more detail below. But the main things to note about the new spilling behaviour:
Like any function, SEQUENCE can take its value from an external cell - and this is where the "dynamic" part comes in, as you can amend the value of the cell to change how many cells the SEQUENCE spills into. Watch out though - if a dynamic array tries to fill an already-filled cell, the entire thing will fail and show a #SPILL! error:
New referencing syntax
What about if you want to make a function that references a dynamic array? A traditional approach might be to make multiple copies of the formula - but this won't keep up if the dynamic array expands or contracts. So to solve this, we have some new syntax in the language of Excel.
References written with a # after them refer, not only to the named cell, but to the entire array that spills from that cell. So for example, we can multiply each number generated by our SEQUENCE by 2 like so:
You can even get a 2D spill by referencing 1D spills in each direction:
There's also a change to a more obscure feature of Excel called implicit intersection. Under old Excel, writing something like =A1:A10 would get you the value in that range from the same row as the formula. Now you will get a spilled dynamic array that returns the entire range - but you can replicate the old functionality by instead writing =@A1:A10:
New dynamic array functions
Several new functions have been rolled out that are designed to work with dynamic arrays. Here are the most important:
The pre-existing TRANSPOSE function is also very useful for flipping between vertical and horizontal arrays.
We will explore these functions and more in the future - but for now, here's a taster of how arrays can be combined with traditional functions to perform some impressively simplified computations:
If you have Office 365, try experimenting with dynamic arrays today! You can download a workbook with the examples shown in this post below.
Previous post - Revisiting loan functions TOTW index Next post - Navigation shortcuts redux!