Introduction to Dynamic Arraysby
In this article, Liam Bastick considers a new and exciting area of Excel: Dynamic Arrays.
Excel is moving on. Yes, we’ve had Power Pivot, Power Query/Get & Transform and Power BI, but Microsoft’s Calc Intelligence team has been busy behind the scenes rearranging the furniture.
By “furniture” I mean the “calculation engine” – it’s had a complete re-write, and there are benefits general Excel users will reap for years to come. The first wave sees a new array calculation (Dynamic Array), several new functions and two new error messages. And that’s just the start. There’s going to be plenty more coming in the next few years.
It’s all still in what Microsoft refers as “Preview” mode, ie it’s not yet generally available but it is something you can try and hunt out. Presently, you need to be part of what is called the “Office Insider” programme which is an Office 365 fast track. You can register in File > Account > Office Insider in Excel’s backstage area.
Let me be clear. Office 2019 does not include Dynamic Array functions. It’s likely you will have to wait until Office 2022 (assuming such a thing will exist) as Microsoft tries to convert everyone to the annual subscription model.
So what’s the big deal?
Let me begin by just looking at what a Dynamic Array is. Consider the following data:
If I were to type =F12:H27 into another cell, Excel in the past would have thought I had gone mad. I’d need to wrap it in an aggregation function such as SUM, COUNT or MAX, to name but a few. Otherwise, I would have to wrap it in braces using CTRL + SHIFT + ENTER and use it as an array formula.
But with the new Dynamic Array, look at what happens when I type =F12:H27 into cell F33:
The formula automatically extends to three columns by 16 rows! It has spilled. Get used to the vernacular. There’s a reason this article got the name it did!
Any formula that has the potential to return multiple results can be referred to as a Dynamic Array formula. Formulae that are currently returning multiple results, and are successfully spilling, can be referred to as Spilled Array Formulae.
Notice I did not have to highlight all of the cells F33:H48. It spilled. Also take note I formatted cell F33 – that didn’t spill, because presently formatting isn’t propagated. This is why this is not yet generally available. Microsoft is still trying to work out what should and shouldn’t be allowed to happen in this first release. But don’t let that put you off.
Do you see I am not having to anchor cells (ie use dollar [$] signs)? The formula just spills. Let me be clear. If I select cell F34, I get the following:
Here’s a first. Check out the formula in the formula bar. It’s greyed out. Have you ever seen that before? Effectively, cell F34 contains the value ‘Triangle’ but it does not actually contain an “Excel” formula in the usual sense. To demonstrate this, let me show you the VBA Immediate window:
But, to quote Bill Jelen, similar to Schrodinger's Cat, if you select cells F33:H48 and use ‘Go To Special’ (F5 -> Special), and then select ‘Formulas’, cells F33:H48 are shown as formula cells. You can even copy and paste them as values. Ladies and gentlemen, welcome to The Twilight Zone (cue eerie music).
Linking a Dynamic Array to a table
As we will see in the next article, you cannot use Dynamic Arrays in a table, but Dynamic Arrays may refer to a table, viz.
In this above illustration, cells F57:H72 have been converted into a table (CTRL + T), with the table named Basic_Array_Example. In cell L57, I have simply typed ‘=’ and then highlighted the entire table. It was all replicated.
The advantage of linking a Dynamic Array to a table is clear:
I can add rows and/or columns and the Dynamic Array will update automatically. Do note that this does not breach the #SPILL! range is volatile in size error. This is because the range size will not vary on every calculation pass.
Talking of varying sizes, it’s clear to see one potential issue with Dynamic Arrays. If we are not referring to a table, what happens if the source data changes dimensions? This may be why you should refer to a table for safety.
However, once you have a Dynamic Array, referring to it is simple using what is known as the Spilled Range Operator. For example, if I want to refer to the Dynamic Array in the previous examples, it initially had a range of L57:N72. However, once I had added a row and column to the Table, this resized to L57:O73. I can easily refer to this array, whatever its size as follows. In its initial state:
The formula =L57# allows for variations – you simply type in the top left-hand cell reference (ie the cell with the non-greyed out formula) and add ‘#”, known as the Spilled Range Operator. Simple!
It’s not all peaches and cream though. Whilst Dynamic Arrays and tables share some similarities, they are very different beasts. This couldn’t be clearer than when you create charts:
Here, I created two charts when I only had the data up to June. Then, I added the data for July. The chart on the left referencing the Table source data updated instantly. However, the chart on the right still only displayed up to June even though the Dynamic Array had updated.
Conclusion: use Tables, not Dynamic Arrays, as your references for dynamic charts.
Word to the wise
As at the time of writing, all the features, functions and error messages are beta features only. They are available to a portion of Office Insiders at this time, but don’t let that put you off. Start getting excited now! Microsoft will continue to optimise these features over the next several months. This means they might change. When they're fully cooked, Microsoft will release them out into the wild, first to all Office Insiders and then finally Office 365 subscribers (this is when a feature is known as “Generally Available”).
The future’s looking bright though.
In future articles, we will look at Dynamic Array errors, legacy formulae and the implications that Dynamic Arrays have for data tables and PivotTables.
Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...