Excel: Microsoft unveils Dynamic upgrades

Mark 24 September as the day Excel moved on. Yes, we’ve had Power Pivot, Power Query/Get & Transform, and Power BI, but at its Ignite event, Microsoft revealed that its CALC team has been busy behind the scenes rearranging the furniture.

By “furniture” I mean the “calculation engine” — it has had a complete rewrite, and general Excel users should reap the benefits for years to come. The first wave, unveiled 24 September, sees a new array calculation (Dynamic Array), seven 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 to as Preview mode, ie, it’s not yet Generally Available (another official Microsoft term), but it is something you can try and hunt out. Presently, you need to be part of the Office Insider programme, which is an Office 365 fast track. Office 365 subscribers can register in File > Account > Office Insider in Excel’s backstage area.

Even then, you’re not guaranteed a ticket to the ball, as only some will receive the new features and functions as Microsoft slowly rolls them out. Please don’t let that put you off.

All Office 365 subscribers will have access to these features soon. For those not on an annual subscription, Office 2019 will not include Dynamic Array functions. It’s likely you will have to wait until Office 2022 (assuming such a thing will exist) as Microsoft desperately tries to convert users to the subscription model.

So, how big a deal is this? It’s a very big deal. Big enough, in fact, that it is going to take us seven articles over the next several weeks to cover everything Excel is releasing and the possible impacts, good and bad, those new features will have.

Let’s begin by 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 no more.

Look 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. It’s going to be a big part of Excel moving into the future.

Any formula that has the potential to return multiple results can be referred to as a Dynamic Array formula. Formulas that are currently returning multiple results, and are successfully spilling, can be referred to as Spilled Array Formulas.

Notice I did not have to highlight all of the cells F33:H48. It spilled. Also take note I formatted cell F33 – er, 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.

If you feel a general sense of underwhelm coming over you, it’s because I haven’t yet communicated how powerful this all is. My example was too basic. So let’s look at some reasons accountants have to be excited about Dynamic Arrays.

An Arrays-ing improvement

In short, Dynamic Arrays will greatly simplify what previously were complex calculations. Prior to this new functionality, if you wanted to work with ranges in Excel, you had to build array formulas, where references would refer to ranges and be entered as Ctrl+Shift+Enter formulas. The main differences between the new way and old way are as follows:

• Any new formulas that return more than one result will automatically spill. There’s simply no need to press Ctrl+Shift+Enter.
• Dynamic Array formulas may spill outside the cell bounds where the formula is entered. The Dynamic Array formula technically only exists in the cell in the top left-hand corner of the spilled range (as shown earlier), whereas with a legacy Ctrl+Shift+Enter formula, the formula would need to be entered in the entire range.
• Dynamic Arrays automatically resize as data are added or removed from the source range. Ctrl+Shift+Enter array formulas truncate the return area if it’s too small, or return #N/A errors if too large.
• Dynamic Array formulas will calculate in a 1 × 1 context.
• According to Microsoft, Ctrl+Shift+Enter array formulas are only retained for backwards compatibility reasons. Going forward, you should use Dynamic Array formulas instead.
• Dynamic Array formulas may be easily modified by changing the source cell, whereas Ctrl+Shift+Enter array formulas require that the entire range be edited simultaneously.
• Column and row insertion/deletion, easily done with Dynamic Arrays, is prohibited in an active Ctrl+Shift+Enter array formula range. You first need to delete any existing array formulas that are in the way.

Dynamic Arrays will make it much easier for accountants to sort data, identify unique entries, and create simpler data constructs. In the next six parts of this series, we will cover the functions and functionality that will make life easier for accountants — and we’ll cover some rough patches as well. Next week, we’ll further examine how Dynamic Arrays work and how they compare to Excel Tables.

An Excel Evolution: The Series

Getting started

Part 1: Excel: Microsoft Unveils Dynamic Upgrades

Part 2: Excel: Arrays, Tables, and ‘The Twilight Zone’

Part 3: Excel: 2 New Errors and a SINGLE Function

Function focus

Part 4: This Excel Change is More Than SORT of a Big Deal

Part 5: Focus on 2 New Excel Functions: UNIQUE and FILTER

Part 6: Excel New Function Focus: SEQUENCE and RANDARRAY

Final thoughts

Part 7: Will Dynamic Arrays Kill Data Tables, PivotTables?

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.