This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Dynamic arrays in Excel

Dynamc Arrays in Excel Thumb

Dynamic Arrays in Excel

Excel is about to change… like seriously, change.  Every time you used Excel in the past, you have accepted a simple operating rule; one formula one cell.  Even with array formulas, it was necessary to have a cell for each formula.  But this is about to change; soon Excel will allow a single formula to fill multiple cells.

 

Overview

Let me try to explain the changes with a really basic example.  Here is a simple formula:

=B3:B7

We can see this formula in the screenshot below: the old method of calculation on the left, and the new method of calculation on the right.

Implicit Intersection Explanation

In the old method, if the formula is in line with the source data (i.e., rows 3 to 7 in the screenshot above), Excel assumes we want the value from that same row.  If the formula is not in line, Excel is confused and returns an error.

The value of 1 shown in Cell D3, is Excel assuming we want the value from Cell B3 because it is in line with it.  However, Cell D8 is not in line with any of the source data, so Excel doesn’t know what we want, and returns the #VALUE! error.  This assumption has got a technical name; Implicit Intersection.

Cell G2 displays the new method; this is how Excel’s calculation engine will calculate the same formula in the future.  Instead of returning a single value based on implicit intersection, Excel will return multiple values all from one formula, the basic rule of one formula one cell has gone.

The terminology to describe a formula filling multiple cells is called “spilling”, and the range of cells filled by the formula is called the “spill range”.

Microsoft announced this change on September 24th 2018.  At the same time, there were 6 new functions introduced to make use of this new functionality: SORT, SORTBYFILTERUNIQUE, SEQUENCE and RANDARRAY.  However this change is bigger than these new functions, it’s a fundamental shift in how Excel (and Excel users)  think about formulas.  In the remainder of this post, I want to show the basics you need to know to get started with this new way of thinking.

As at the time of writing, Microsoft has only made this new functionality available to Office 365 subscribers on the Insider channel.  The updates will be rolled out to all Office 365 subscribers at a future point, but first Microsoft need to assess the impact the changes to the calculation engine will have.  The new dynamic array functions and features are not available in Excel 2019 or previous versions, so it is definitely worth getting an Office 365 subscription, otherwise, you will be waiting until Excel 2022, and that’s just too long to wait!

 

Dynamic array formulas

As noted above, there are now 6 new functions which make use of this new spilling functionality:

  • UNIQUE – to list the unique values in a range
  • SORT – to sort the values in a range
  • SORTBY – to sort values based on the order of other values
  • FILTER – to return only the values which meet specific criteria
  • SEQUENCE – to return a sequence of numbers
  • RANDARRAY – to return an array of random numbers

Advertisement:

I won’t go into detail about each of these in this post, as I have written separate posts for them (click the links above).

The ability to spill is not restricted to these new functions; many existing functions now spill too.   Look at the example below.

SUMIFS if with dynamic arrays

This is a simple scenario in which we have data in Cells B2-C9.  This data displays a name and a score.  To calculate the total score for each individual we could use the SUMIFS function in Cells F2-F5.  F2 contains the following formula:

=SUMIFS($C$2:$C$9,$B$2:$B$9,E2)

This formula would then be copied down into the 3 rows below, changing the last argument each time to E3, E4 and E5 respectively.  This ensures the total is calculated for each person.

But wait, the new Excel calculation engine can return multiple results from a single formula. Rather than having 4 formulas, one for each cell, we can have one formula which spills into the other cells.  The formula in Cell F8 demonstrates this:

=SUMIFS(C2:C9,B2:B9,E2:E5)

This formula includes the array of Cells E2-E5 as it’s last argument and spills the results into Cells F9-F11.

Which formulas spill, and which don’t?  We’re all getting used to this new way of thinking, so we’ll just have to do some experimenting and see what happens.

 

Spilling

By clicking a formula or any cell in the spill range, a blue box appears to show all the cells within the same spill range.  Everything in the blue box calculates by the top left cell of that box.

SPILL Range

If you click on any cell within the spill range, the formula bar displays the formula, but it is greyed out, you can’t change it.  Remember, the formula isn’t really in there, the formula is in the top left cell, the other values are just the spill range.

SPILL Range - Grey Out

What happens if there is data already in the spill range?  Will it overwrite the existing data?  Thankfully, nothing too dramatic happens, the formula will return a #SPILL! error.


Advertisement:

But click on the #SPILL! error and Excel will highlight the spill range required.

SPILL Error Dotted Line

Then it’s your choice to move or delete the value or the formula.

#SPILL! errors will occur in the following situations (this list is not exhaustive):

  • The spill range is outside the available cells on the worksheet
  • The spill range has an unknown size
  • The dynamic array formula is included in a Table
  • The spill range contains a merged cell

 

Constant arrays

A range in Excel, in it’s simplest form, is an array of values.  But, if the values we want to use are not held in cells, we can create our own array.

Here is a screenshot showing 3 names.

Array Range Rows

To refer to this range, we would use the following:

=A1:A3

Or we could create our own array

={"Tom";"Dave";"Billy"}

The curly brackets are to tell Excel that this is an array of values.  This is not new in Excel, however given this new dynamic array functionality, I expect they will become more common for regular Excel users.

Arrays in rows are separated by semi-colons ( ; ), whilst arrays in columns are separated by commas ( , ).

Look at the screenshot below.

Array Range Rows and Columns

The range A1-C2 could also be created as a constant array as follows.

{1,2,3;"Tom","Dave","Billy"}

Advertisement:

Notice, the values go across the columns, then down through the rows at the end of each column.

Constant arrays can be used within some functions to create dynamic arrays.

=INDEX({74;72;69},MATCH({"Tom";"Dave";"Billy"},{"Tom";"Dave";"Jack"},0))

The formula above contains no cell references, yet it is a formula which creates a spill range (see the screenshot below):

Array Constants - Formula

 

# References

Microsoft has also created a way for us to reference a formula and its spill range.  As an example, if the formula with the spill is contained in Cell B2, we can reference the entire result by using =B2#.

The screen shot below revisits our earlier example.  However, now the values are all based on dynamic arrays.

Dynamic Arrays - Example Hash Reference

The formula in Cell E2 is:

=UNIQUE(B2:B9)

This formula creates a list of distinct values from Cells B2-B9.

The formula in Cell F2 is:

=SUMIFS(C2:C9,B2,B9,E2#)

The SUMIFS function here is referencing the range E2#, which is Cell E2 and its associated spill range.  In this example, it is equivalent to referencing Cells E2-E5.  However, it has one big advantage; if the source values change and there become 5 unique values, the UNIQUE and SUMIFS functions will both automatically expand.

 

Conclusion

I’m sure you’ve got 100 questions spinning around your mind about dynamic arrays.  This is all still new, so we’re all getting to grips with the cool tricks we can now do.  Hopefully, this functionality will be coming to all Excel 365 subscribers in early 2019.  I hope you’re excited about the capability which spilling now provides to us.

This is going to change the way we use Excel… forever!


Advertisement:

 

Want to learn more?

There is a lot to learn about dynamic arrays and the new array functions.  Check out my other posts here to learn more:

  • Introduction to dynamic arrays – learn how the excel calculation engine has changed.
  • UNIQUE – to list the unique values in a range
  • SORT – to sort the values in a range
  • SORTBY – to sort values based on the order of other values
  • FILTER – to return only the values which meet specific criteria
  • SEQUENCE – to return a sequence of numbers
  • RANDARRAY – to return an array of random numbers

Also, here are some other resources you might find useful:

3 thoughts on “Dynamic arrays in Excel

  1. David N says:

    Have you seen anything in your testing, particularly with the new functions like UNIQUE, SORT/BY, and FILTER to say how they might react (if at all) to having filters applied that might hide rows in the source range and/or if combining them with another function force them to react? For example, could UNIQUE(B2:B9) be made to return a different subset result if a filter operation had hidden some of the rows in the B2:B9 range?

    • Excel Off The Grid says:

      Hi David,

      I’ve just tried it and can confirm that filters have not impact on the outcome of the functions at all.

      You could put the FILTER function inside the UNIQUE, SORT or SORTBY functions to achieve a similar outcome. But the filters on Tables or AutoFilter themselves do nothing to the result.

      • David N says:

        That’s slightly disappointing. I find that functions that can behave like AGGREGATE are great tools for empowering the user to get what they need through their own filtering without having to be spoon fed so much. Consequently I have a custom VBA function that can return an array of unique and/or visible values that are optionally sorted and are then ready for subsequent processing by other functions — i.e. the VBA function can be nested inside other functions. So at least Microsoft is mostly catching up with me, just not all the way quite yet.

Leave a Reply

Your email address will not be published. Required fields are marked *