Preview of Dynamic Arrays in Excel
Published Sep 25 2018 06:00 AM 162K Views
Microsoft

July 1st 2020 Update
Dynamic Arrays is now available to Office 365 users on all endpoints. 

 

Until now, you wrote a formula for each value you wanted returned to the grid. One formula, one value. If you wanted another value, you wrote (or copied) another formula. With dynamic arrays, that all changes. Now, you can write a formula hit the enter key and get an array of values returned. One formula, many values. This will allow you to build more capable spreadsheets, faster, with fewer formulas and less chance of error.
 
To harness the power of dynamic arrays, we’ve added some amazing new functions. So, for instance, you can use the SORT function to sort a list, the UNIQUE function to remove duplicates from that list, then use the FILTER function to get just what you want from the list. And when your data changes, the dynamic array will resize and recalculate automatically! 
 

Dynamic arrays in actionDynamic arrays in actionSpilling

We call the behavior of placing values in neighboring blank cells "spilling”, and you’ll see Excel indicate the formula’s “spill range" with a thin blue border when you select any cell inside the spill range.
 
Don’t worry about the spill range overlapping your data--if there isn’t enough space, the formula will roll up and show an informative #SPILL error. When you select the #SPILL error, the formulas desired spill range will be indicated by a dashed blue border. Just move or delete the obstructing data and your formula will automatically spill.
 
Spilling range not blankSpilling range not blank
Native to Excel
Dynamic array support is deeply integrated into Excel and it's not limited to the functions shipping alongside it -- any newly authored formula that returns an array will spill. For instance, entering =A3:A13 into B3 will cause the values in A3:A13 to be spilled into B3:B13. And just like Excel's grid, dynamic arrays can be 2 dimensional as shown in the multiplication table example below.
 
 Using dynamic arrays with existing functionsUsing dynamic arrays with existing functions
Referencing the spill range using A1# notation
Dynamic arrays may seamlessly resize as your data changes. To make it easy to reference resizing dynamic arrays, we are adding a way to reference the entire spill in a dependable, resilient way. You can do this by following a cell reference with the # symbol, for example A1#. This is equivalent to referencing the entire spilled range for the dynamic array in A1. We'll default to this style reference whenever you write a formula that refers to the entire spill range.
 
In the example below notice how the SUMIF function is using all the product names from the dynamic array in D5. When Grapes is added to the sales table, the D5 spill range grows and so does the result of the SUMIF because it references D5# rather than D5:D8.
 
 Using # to refer to the spill rangeUsing # to refer to the spill range
New Functions
Here is the full set of functions that will be accompanying dynamic arrays.
 
FILTER - filters an array of data based on criteria you define.
UNIQUE - returns a list of unique values from a list or range.
SORT - sorts an array of values.
SORTBY - sorts an array based on a corresponding array.
SEQUENCE - generates a list of sequential numbers, such as 1, 2, 3, 4.
RANDARRAY - returns an array of random numbers between 0 and 1.
 
We cannot wait to see how our users use these new building blocks in their spreadsheets.
 
Learn More
You can learn more about dynamic arrays from these resources:
 
 
Availability notes:
Dynamic arrays functionality is available in Preview for users signed up for the Office 365 Insiders Program starting today. We will initially roll out to a subset of Insider users on Windows so that we can gather feedback and monitor feature quality. Over the next few months, we'll be increasing the number of Insider users with access to dynamic arrays and light up support for Excel on Mac, web, and mobile. You’ll know if you have dynamic arrays if you see any of the new functions in your formula autocomplete when you start typing a formula.
 
To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.
 
Joe McDaid (@jjmcdaid)
Program Manager, Excel
266 Comments
Iron Contributor

Few additional comments:

for my everyday work I find quick tool like formula:

=unichar(transpose(sequence(256;256;0))) - try on empty sheet - extreamly useful tool :)

 

plus one negative comment:

Data validation list formula =Table[Column] still not work
Data validation list formula =Indirect("Table[Column]") works fine (workaround for previous)
Data validation list formula =Sort(Indirect("Table[Column]") don't work again

@Ed Hansberry, in COUNTIFS criteria range can't be a formula, only range. Thus error. If you use $E$2# that is shortcut for $E$2:$E$7, not for LEFT(Table1[Number],6).

 

In general

=SUMPRODUCT((LEFT(Table1[Number],6)=LEFT(Table1[@Number],6))*1)

works

 

Brass Contributor

Hi Joe,

I've just got the Dynamic Arrays last week.  And it is simply awesome!  Super COOL! I do hope the corporate world will move to Office 365 quicker so that we can use the full potential of Excel anywhere, not just at home. :p

 

I've encountered a strange behaviour in Data Validation using Dynamic Array.

 

What I did was to create a "Shrinking Dynamic Dropdown" - Once an item is selected, it will be gone for the next input.  With Dynamic Arrays, it can be  achieved easily in just a couple of minutes.  However I encountered an unexpected behaviour - if I input a value through manual typing, it would trigger a recalculation before validation... which makes my trick fail... The trick works well if I select an value from the dropdown...

In short, my observation is:

  • Using dropdown menu: Validation first, recalculation afterward
  • Using manual input: Recalculation first, validation afterward (and hence the validation failed the input)

I've described the situation in more details in my post: 

https://wmfexcel.com/2018/11/14/dynamic-shrinking-dropdown-with-dynamic-arrays-in-excel-365/

 

FYI, I didn't encounter this when using old tricks of array formulas.

 

I am not sure why it is... and I do not know where and who I should report this to... So I leave you a comment here.  :)

 

Cheers,

MF

Steel Contributor

Thanks @Sergei Baklan. Actually SUMPRODUCT isn't needed anymore. I used the following based on your idea:

 

=SUM((LEFT(Items[Item number],6)=LEFT(Items[@[Item number]],6))*1)>1

I think this not really intuitive though. I guess I have the mindset of how DAX functions work and when you return a "range" of data (table or column) you are returning the contents of that to the function, so it seems logical that COUNTIF(S) should accept the contents of the range and not just the range reference.

Steel Contributor

I'm trying to understand why this is happening. I have created a simple table in Excel, and I want the last column to be a running total, so everything the current row plus the results of the previous row as long as the previous row isn't the title row, which would cause an error. This formula also work 100% of the time when you insert and delete rows in the table.

 

This formula is in the Balance column of the table.

 

=SUM(Table1[@[Column1]:[Column5]])+IFERROR(OFFSET([@Balance],-1,0)*1,0)

 

When I create that formula in the Insider build, it works fine. When I open it on any other build (Monthly Targeted, and the iOS Beta of Excel - 2.19.18110500) it makes it a CSE array formula.

 

{=SUM(Table1[@[Column1]:[Column5]])+IFERROR(OFFSET([@Balance],-1,0)*1,0)}

 

But I can edit it on those platforms and make it a normal formula, and it works just fine.

 

So with Monthly Targeted (1811) I changed it to a normal non-CSE formula. If i change data with Insider, the formulas do not get converted back to CSE formulas, but if I edit the formula, it does.

 

Seems it is too aggressively converting the formulas when it isn't necessary.

That's interesting, but better to convert some extra and ensure compatibility (if not use new dynamic array functions). The point is it works in both versions.

 

By the way, you may simplify your formula a bit

=SUM(Table1[@[Column1]:[Column5]])+N(OFFSET([@Balance],-1,0))
Steel Contributor

Anyone have any idea how to sum a column that is the result of a FILTER() function?

 

I have a FILTER function that returns a variable number of rows, and 4 columns. The 3rd column has values and I just want the SUM() of that column.

If I put the FILTER() in K9, then the following works:

=SUM(OFFSET(K9#,0,2,ROWS(K9#),1))

I would like that in one formula though if possible, but OFFSET() requires a reference in the first parameter, not a formula that returns a table.

 

I know that DAX is messing me up here. This is a snap in DAX and I am thinking of tables, records, and fields.

 

EDIT: Figured it out. I can filter one column based on multiple other columns. I had it in my head I needed to filter the entire table. (DAX again....)

Iron Contributor

Ed, try to replace 1st K9# to K9 or $k$9

 

Henn

 

Steel Contributor

@Henn Sarv- that function worked as it was. Has to be K9# to get the whole array. I changed the formula to this:

 

=ROUND(SUM(FILTER(tblRoyaltyAccrual[<Accrual> / Payment],(tblRoyaltyAccrual[Vendor]=F9)*(tblRoyaltyAccrual[Date]<=dtCurrentMonthEnd))),2)

I just pull the [<Accrual>/Payment] column but filtered by [Vendor] and [Date], so I only get a single column in my results to SUM. I was returning the entire 4 column table in my FILTER() function before I figured it out.

Iron Contributor

2 missing formulas or I can't find

=First( spill , n )
=Last ( spill, n )
=Reverse ( spill )

NB! Goot to have =SKIP(spill, n) too or alternative allow OFFSET like thing against expression

I know how to do this using =offset but when I need to include this into one formula

actual request is how to summary last 3 filled cells in range (using one formula) 

Brass Contributor

Hi

This new set of functions seams just wonderful.

Is there any ETA for when they will be available to regular Office 365 users (I have the version 1811 on a Monthly update channel).

Iron Contributor

You have option to upgrade into insiders - available immediately

 

I have instruction (unfortunately in Estonian) how to do - see sarviktaat.wordpress.com

Brass Contributor

Thank you, I will have a look at the post. 

Copper Contributor

Is there a way to skip columns when using FILTER?  I have data in A>F but only want to return the filtered data from A,B,D,F.

@ChrisTank, it could be like

image.png

 

Copper Contributor

Hi,

 

What's the right channel to provide feedback on the new dynamic array features in Excel in insider builds? We have an issue where our custom functions (XLL functions created using ExcelDna) are occasionally detected by Excel as being array functions and get "converted" to CSE-array formula. It happens randomly and I have not found any way to fix files affected by this issue beside starting from an earlier backup of the file, or using a version of Excel that doesn't have the new dynamic array feature enabled.

 

Screen Shot 2018-12-11 at 09.20.44.png

 

I used the feedback button in Excel and also posted it here https://techcommunity.microsoft.com/t5/Excel/Dynamic-aware-Excel-incorrectly-flagging-our-custom-XLL... but I'm worried this will eventually hit customers using normal release builds of Office.

 

This creates a host of problems for us - our custom functions are RTD functions, and there's a bug or limitation in Excel where the RTD server does not disconnect when used in an array formula ( https://gist.github.com/govert/03df749f38b9582b1217 ). To be clear, our functions are not array functions, however since Excel *thinks* they are array function the RTD server does not properly disconnect from the cell. 

 

I think the root of the issue is that Excel looks at the return type of the function to decide if it's a function that can potentially return an array, and since the return type is just "object" it suddenly decides to make the cell formula an array formula. As per: https://support.office.com/en-us/article/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-..., "Whenever you write a formula in dynamic aware Excel, it determines if that formula has the potential to return an array. If it could return an array, we will save it as a dynamic array.". What I don't understand is why it randomly happens, and whether or not we can avoid this...

 

 

Copper Contributor

@Sergei Baklan  That works to an extent.  I wish I thought to share a screenshot previously.  The below is what I am trying to accomplish.  The filter formula would go in A22.  Ultimatley I would be placing it in a different sheet within the workbook and it would use the data table for the data to filter.

FILTER WITH INCONSISTENT COLUMN SKIP EXAMPLE.png

 

Is this even possible with the new array formulas?

 

Thanks in advance,

Chris

Steel Contributor

@GabrielMichaud- hoping you can get access to the feedback channel. Not sure what it is for direct access. The issue though is your XLL's are using implicit intersection and the new dynamic array calculations detect that and will either wrap in {} or wrap in =SINGLE(). The fix is to remove implicit intersection from your XLL functions. This is one reason MS is being really slow to roll this change out. They want to make sure that any addin inadvertently using implicit intersection is updated and re-released.

Copper Contributor
@EdHansberry can you clarify what you mean by implicit intersection? All our functions expect a single scalar value; we don't use range or references anywhere in our XLL. The one I put as a screenshot in my original message has two string arguments; and I only pass a single cell reference (A1). I was suspecting my problem had to do with the return type of the function rather than the arguments... The fact that the issue occurs randomly is a bit challenging for me to debug/troubleshoot as well, it's not consistent at all.
Steel Contributor

Read this thread @GabrielMichaud. There are a number of addins and formulas that are using implicit intersection and don't know it. Follow the links, including to Bill's article and video on implicit intersection.

@GabrielMichaud, as variant that could be like

image.png

 

if in J1:M1 is list of columns you'd like to extract in order in which you'd like to have them. The name of the source table here is "Test".

Copper Contributor

I don’t have any tables, array or reference that is more than 1 cell in my example:

 

=ACCOUNTNAME("Demo", A1)

 

Yet Excel thinks this is an array function and suddenly/randomly changes it into a CSE array formula.

@GabrielMichaud, sorry, that was reply to @ChrisTank

Copper Contributor

Any update on availability? currently on monthly targeted release and waiting patiently...… ever so patiently!

Copper Contributor

@Sergei Baklan  I wish my mind worked in the way needed for you to have figured that out.  Incredible.  

 

Thank you so much.  That solution is also great for showing me some new options.

 

 

Steel Contributor

Does anyone know of a way to reliably trap the use of these new functions in an older version of Excel? What is happening is the function becomes essentially static. This is what happens:

=UNIQUE(ObsoleteTable[item_no])

works in insider build. In older builds, this is what comes up:

{=_xlfn.UNIQUE(ObsoleteTable[item_no])}

which returns a #NAME error or the static value of the array from the last calculation in the version of Excel that supports it.

 

So I tried to trap #NAME with this:

=IF(ISERROR(UNIQUE(ObsoleteTable[item_no])),"test",UNIQUE(ObsoleteTable[item_no]))

In old versions, "Test" is properly returned. But in Insider Builds, "Test" is also returned if the last version to open the file is an old version, unless you press F2 on the formula to edit, then enter. Opening the file doesn't recalculate it, pressing F9 doesn't, and neither does CTRL-ALT-F9.

 

What I want to start doing is where I am using these in some files is displaying a message that "This version of Excel doesn't support all data in this workbook" in old versions, and works in new versions.

 

But given that the ISERROR() won't recalc in the Insider builds, this plan won't work.

 

Any ideas? This will be an issue for years to come if the behavior is the same. Internally we'll ultimately be fine as dynamic arrays roll out to Monthly builds, but as we share files with outsiders, they may get the wrong results if looking at a static array if they are on an older version of Excel.

 

Copper Contributor

Will it be possible for the user to turn off this feature if it interfering with something?

 

Will it be possible to mark some functions that we write (say written in VBA or an XLL) as not using this dynamic feature?

Steel Contributor

@ErikW - I seriously doubt it. This is a change to the calculation engine itself, not an addon like Power Query. My understanding is MS is working hard to ensure any widespread addins (XLL or otherwise) are updated so they aren't using implicit intersection, or if they are are using it purposefully and correctly.

Copper Contributor

In the current version you can display part of the full array, including a single cell, by entering with  Ctrl-Shift-Enter; so in effect that works as in earlier Excel versions.

I certainly hope that feature is retained, it will be very bad if it isn't.

 

It would also be good if we could re-size the displayed array by selecting the required range and pressing Ctrl-shift-enter again, or revert to the new behaviour by pressing just enter, but I haven't seen any response to that suggestion.

Copper Contributor

I just installed version 1901 of Excel for Office 365.  Why can't I use the new dynamic array formulas ( e.g., UNIQUE, FILTER)?

@Mark E Weisman , so far that's for Office Insiders (aka Insiders Fast), only Office 365 is not enough

Copper Contributor

@JoeMcDaid , I don't know whether you saw this post from me, but I think one of Excel's greatest MVPs might have made you aware of my recent dilemma.  Thanks for taking a look at it.  MOBMD

 

"Since this latest 2/1/2019 update, there is a new glitch with the Office Insider Dynamic Array functions.  It seems that many, many more VBA-written formulas are now erroneously auto-inserting the SINGLE function, apparently due to the Excel Calc engine assuming a need for Implicit Intersection.  This incorrect insertion of the SINGLE function is much more pervasive than it was as recently as the morning of 2/1/2019 (prior to the update) -- in the same workbooks, despite no interval change in the macros within those workbooks.  Is there a way to disable auto-insertion of the SINGLE function, at least until this glitch is corrected with the next build?"

Microsoft

@MOBMD There was a change that impacted macros injecting formulas into cells - the latest build is essentially interpreting them as old style formulas which is why you are seeing the SINGLE's appear. I'll have some info and further news on DA related enhancements in the next few days. 

Steel Contributor

Hoping the changes @JoeMcDaid really curtail the SINGLE() issue. 

 

I just found out a workbook with the simple formula 

 

=INDEX(tblCalendar[Years],Settings!C1)

got wrapped in Single when I opened it in my insider build. tblCalendar is a simple 2 column Excel table.

Microsoft

@Ed Hansberry That's expected. INDEX returns an array or range when the 2nd/3rd argument is 0. If Setting!C1 ever resolves to 0, that formula would have implicitly intersected in old Excel.

Copper Contributor

Is the same behaviour expected for custom functions with an undefined/variable return type? We have custom XLL functions that rely on a RTD server (built with ExcelDNA) and in an earlier build we were seeing functions getting wrapped in {} (as if they were array functions), even though we only return single values. I posted something about that problem a month or so ago and I had to revert back to the official

build since it was breaking our spreadsheets...

Copper Contributor

Is the same behaviour expected for custom functions with an undefined/variable return type? We have custom XLL functions that rely on a RTD server (built with ExcelDNA) and in an earlier build we were seeing functions getting wrapped in {} (as if they were array functions), even though we only return single values. I posted something about that problem a month or so ago and the explanation I got was

that my functions are using implicit intersection. Not clear if we have to change something or

if it’s a bug in the dynamic array features since it’s so random. I had to revert back to the official

build since it was breaking our spreadsheets...

Microsoft

@GabrielMichaud This is expected as UDF's have the ability to return ranges/arrays and trigger implicit intersection. That said, we will be announcing some improvements to the experience in the next few days - so watch this space. 

 

Steel Contributor

Thanks @JoeMcDaid. I think the confusion, at least on my part is why does this happen?

  • Create formula =INDEX(tblCalendar[Years],Settings!C1) in Insider build. Stays like that. It works without SINGLE()
  • Create formula =INDEX(tblCalendar[Years],Settings!C1) in other build, it works in non-insider build. When opened in Insider, it is wrapped in SINGLE(). Saving, closing and opening back in non-insider build SINGLE() is gone.
  • In some cases, the non-insider build is converted to a CSE funtion ={INDEX(tblCalendar[Years],Settings!C1)}

I know this is a transition issue, but it will actually last years as there are people with Excel 2013/2016/2019 that won't get this until they either upgrade to O365, or get Excel 2022 or whatever the next perpetual license version is.

 

It would be clearer to me if SINGLE() was required to get the function to work properly in the new Dynamic Array calculation engine, but in every case I've seen with my workbooks, the addition of SINGLE does nothing except perhaps render the function as an error in non-insider builds. I have more than one workbook in our org that I cannot open in the Insider build as it trashes a pretty long formula in a table (so it changes thousands of records) for use in any version that doesn't support Dynamic Arrays simply by wrapping it in SINGLE().

Copper Contributor

Further to Ed Hansberry's last post, it seems to me that the basic problem is that the dynamic arrays either return the full array, or they return the "spill" message.  That may be OK for some applications, but for many it makes them unusable, with the only workaround being to enter the array as a fixed size, with Ctrl-Shift-Enter.

 

I suggest the following changes that would provide all the benefits of the dynamic arrays, without the drawbacks.

1. When an array is too large to display in full, replace the "spill" message with a display  of as much  of the array as will fit, with a distinctive border to indicate that it is a partial display.

2. Retain the Ctrl-shift-enter functionality to allow display of partial arrays without the border.

3. Improve the Ctrl-shift-enter so that arrays can be re-sized to a selected range, or returned to dynamic mode by entering with just enter.

4. Drop the "single" function entirely, or at least don't insert it automatically.

5. Any arrays that return a single value, such as the Index examples, would display as normal, without the "single" function added.

Steel Contributor

@JoeMcDaid  - any progress on the updates? Excited to test it. And did you get my file that SINGLE() was corrupting formulas so non-Dynamic Array aware versions got errors instead of CSE style functions?

Steel Contributor

Did UNIQUE() change in the last week or so? I have a worksheet returning #VALUE now that wasn't when I created the formula a few months ago.

 

=COUNTA(UNIQUE(ObsoleteTable[item_no]))

 

The table has no records. It was returning zero, but now is 1 because UNIQUE() is returning #VALUE. I am not sure what it was returning when it was empty before, but the COUNTA() around the UNIQUE(no records) was returning zero. Pretty sure it was zero.

 

It is an error table and should have zero records. I know how to write it a different way, so not asking for help. Just want to know if UNIQUE() changed in the last few weeks.

Microsoft

@Ed Hansberry looks like we introduced a bug, I'll get that off to engineering. Thanks for the report!

Iron Contributor

Looks like every spilled formula, returning empty set (0 rows) produces some error - this might not be bug

=LARGE(SEQUENCE(3);4)     // return #NUM

=SEQUENCE(0)       // return #CALC

might be reasonable implement new error type #EMPTY or something like

Iron Contributor

OR for that kind calculation create =COUNTDISTINCT() new function

Copper Contributor

I'm super excited to receive this update.  I've been waiting since the announcement.  Really looking forward to playing around with the formulas and using it in our day to day files. :)

Copper Contributor

are these available now?

Hi @Jabraham - yes, but only for Office 365 Insiders Fast

Steel Contributor
I have similar concerns to @Doug Jenkins in relation to functions that return variable sized array results and so risk raising #SPILL errors.
 
It would be nice if it were possible to access the underlying array result using the # syntax without needing to accommodate all the spilled results - perhaps by introducing an 'Array' cell data type similar to the new Geography / Stocks ones? (Dynamic array variables of this kind can be created via the xlfSetName API function but something built-in would make things a lot easier.)
Copper Contributor

Any estimate on when this might be released to Production / Monthly channel in O365?

Version history
Last update:
‎Oct 05 2020 09:49 AM
Updated by: