Dynamic arrays in Excel

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.


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


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

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:


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:


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.


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.


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.

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

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

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:


Or we could create our own array


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.


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.


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

Array Constants - Formula

If you’re currently thinking this is a bit too abstract to ever be useful, lets look at an example with VLOOKUP.  The third argument of VLOOKUP is the column index, which is normally a single value.  But what if we entered a constant array instead?

Array Constants - VLOOKUP

The fomula in Cell G6 is:


This formula includes a constant array of {2,3}.  Excel is using both column index values of 2 and 3 in the VLOOKUP calculation and returning values to Cell G6 and G7.  The old method would have required two formulas, but with dynamic arrays we use one.

# 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:


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

The formula in Cell F2 is:


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.


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!

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:

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

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 *