Excel Dynamic Array Improvements

This post has been republished via RSS; it originally appeared at: Excel Blog articles.

Since announcing dynamic arrays, we’ve received an overwhelmingly positive response from the community. Being a fundamental change to the way formulas work, we wanted to take some time to gather and incorporate user feedback. Below are some of the improvements we've made.

Dynamic Arrays in Action

Introducing the Implicit Intersection Operator: @  

Regular Excel formulas had a calculation behavior called implicit intersection that would silently select a single value from an array of values. This ensured formulas would always return just one value as that's all a cell could hold.

Implicit intersection worked as follows: 

• If the value was a single item, then return the item
• If the value was a range, then pick the cell on the same row or column as the formula
• If the value was an array, then pick the top left value

With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so invisible implicit intersection is no longer needed. Where an old Excel formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred. With the initial release of dynamic arrays, Excel indicated where this occurred by using the SINGLE function. However, based on user feedback, we’ve moved to a more succinct notation: the @ operator.  

Why did we select the @ symbol? The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1].  

When should you expect to see the @? Generally speaking, functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. It's important to note that there is no change to the way your formula behaves, you can just see the previously invisible implicit intersection. Common functions that could return multi-cell ranges include INDEX, OFFSET, and User Defined Functions (UDFs).  A full list can be found here. A common exception is if they are wrapped in a function that accepts an array or range (e.g. SUM() or AVERAGE()). 

Let’s consider some examples: 

Can you safely remove the @? It depends on what the part of the formula to the right of the @ returns: 

• If it returns a single value (the most common case), there will be no change by removing the @
• If it returns a range or array, removing the @ will cause it to spill to the neighboring cells

If you remove the @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}), this is done to ensure old Excel will not trigger implicit intersection. 

Object Model (VBA/VSTO) 

We’ve made some tweaks to the object model to improve compatibility with existing add-ins and macros. We’ll be introducing Range.Formula2 which will supersede the existing Range.Formula. Setting one will automatically set the other.

Range.Formula will continue to be supported and will i) be silent on implicit intersection when read, and ii) implicitly intersect as required when set. Setting Range.Formula will always result in a formula that will never spill, because implicit intersection will force it to return just one result. This is consistent with how it behaved before the introduction of dynamic arrays.

Range.Formula2 is what will be reported in dynamic array Excel’s formula bar. Any implicit intersection operations will be indicated by the @ operator.  

When should I use Range.Formula vs Range.Formula2? If you expect your code/macro to only run in dynamic array versions of Excel, you should simply use Range.Formula2. If, however, you want to target all versions of Excel you should use Range.Formula, but you should understand the differences.  

Here is a short VBA example to illustrate some differences: 

[B1].Formula = "=A1:A10" 'The value of A1 is shown in B1 and no spill occurs
MsgBox [B1].Formula 'The message box shows =A1:A10
MsgBox [B1].Formula2 'The message box shows =@A1:A10 consistent with the formula bar

To improve compatibility for macros recorded in dynamic array enabled Excel when opened in older versions of Excel, the Excel Macro Recorder will record Range.Formula when it would result in the same formula as Range.Formula2.   

Object Model (JS) 

In JavaScript, we’ll be updating Range.Formula to continue reporting the contents of the formula bar rather than introducing Range.Formula2. The reasons for the variance vs VBA/VSTO is i) it simplifies the JS object model, ii) the vast majority of Office JS addins already behave correctly in dynamic array enabled Excel, iii) it’s possible for developers to centrally deploy updates to Office JS Addins (if required) and iv) Office JS addins are newer and, in most cases, are being actively developed and maintained. 

Availability notes: 

We’ll be rolling out these changes to users signed up for the Office 365 Insiders Program starting today. Over the coming months, we’ll continue gathering feedback and monitoring feature quality as we move towards making the feature available to all Office 365 users. 

Joe McDaid (@jjmcdaid) 

Program Manager, Excel