Excel Tip of the Week #327 - Introduction to dynamic array functions

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:

  • Arrays had to be confirmed with Ctrl Shift Enter, and wouldn't work if they were jumped into and edited without re-pressing this combination
  • Multi-cell arrays had to be sized up front and couldn't be changed in size later on
  • Array calculation was obtuse and sometimes confusing

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.

Spilling

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:

  1. The formula =SEQUENCE(5) is entered only in the first cell - the others do not have formulas but are instead spilled into from there
  2. The formula is shown in the formula bar in the spilled cells, but it is greyed out and cannot be amended
  3. A blue pinline highlights all the cells which are part of the same array

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:

  • SEQUENCE can generate a sequence of numbers in one or two directions; it defaults to 1, 2, 3... but can be set to start at any number and count in units of any size
  • RANDARRAY fills an array with random numbers - these can be decimal random numbers between 0 and 1 similar to the RAND function, or can be whole numbers between set limits like RANDBETWEEN, or can even be decimal numbers between set limits
  • UNIQUE extracts the unique items from a range or array
  • SORT sorts an array (best for 1D arrays)
  • SORTBY sorts an array with multiple columns and, if desired, multiple levels of sorting
  • FILTER filters an array according to one or more conditions

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!

Anonymous