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
Copper Contributor

I have been waiting patiently since this was announced in September, I'm on the insider program and have been checking every month but nothing yet.

Any update on when these new functions will become a reality?

cheers.

@mdallura , do you see Office Insider if you check File->Account for your Excel?

image.png 

Iron Contributor

Today I start for intent get dynamically set of random numbers

some attempts:

=SEQUENCE(10)*0+RANDBETWEEN(10;20)

or

=SEQUENCE(10)*0+RAND()

NO help :(

Any idea - how to get set of random numbers dynamically

OR random subset of actual numbers dynamically




 

Microsoft

@Henn Sarv DA's calculates in a 1x1 context, they then spill. This means that RAND and RANDBETWEEN will only provide 1 value which is what you are seeing. This is why we released RANDARRAY(). Give it a shot, it's very flexible.

Iron Contributor

Thänks - Randarray is exact what I needed

Iron Contributor

Is there something to do to jump over gap of missing functions

 

like

 

=UNION(C1#;E1#)    // option to union 2 arrays
=INTERSECT(C1#;E1#)
=EXCEPT(C1#;E1#)   // might be limited for 1-column arrays

and is there some option to get more easy something like:

=INDEX( M16#;INT(SEQUENCE(ROWS(M16#)*COLUMNS(M16#);1;0)/COLUMNS(M16#))+1;MOD(SEQUENCE(ROWS(M16#)*COLUMNS(M16#);1;0);COLUMNS(M16#))+1)

for example =FLAT(M16#)

might be some option for direction (vertical / horizontal)

Iron Contributor

Strange behaviour

On the page containing several dynamic array formulas when I select some empty cell and click on formula tab "show  dependences", I see following picture:

dependencies.jpg

 

NO one referenced cells don't contain any reference to pointed cell

N2:= UNIQUE(Kuu#) // Kuu is reference C2
C2:= Kuupäev#-DAY(Kuupäev#)+1 // Kuupäev is reference B2

B2:= MIN(Männiku[Kuupäev])+SEQUENCE($L$1+TODAY()-MIN(Männiku[Kuupäev]))-1 // Männiku is table on other sheet not containing external formulas 

What is the reason of those dependency arrows. I can provide the workbook containing the case - not confidential


Iron Contributor

follow up to previous - to repeat the possible bug

on the cell A3 I create formula =SEQUENCE(10) 
I assign name "seq" to cell A3

on the cell c1 I enter some number - let it be 7 (no important)
on the cell c3 I enter formula =FILTER(A3#;A3#>5)+$C$1

and now I ask to trace dependenc for C1 - I get normal one arrow to C3

now I change the formula on C3 to =FILTER(seq#;seq#>5)+$C$1 // name# references on formula

 

and now the trace dependenc for C1 looks like:

dependencies2.jpg

So looks like name# reference influences the full story (calculations works fine - only trace arrows) 

Copper Contributor
@JoeMcDaid is there any update when this might be available on MacOS (insiders build or otherwise?). Thanks! J
Steel Contributor

@jamescrowley - this has been in the Office 365 Insiders build of MacOS for months. January 2019 at least. I've been playing with it on both Windows and Mac for quite a while. The only insider platforms it isn't on is iOS and Android.

Sorry for being boring, more exactly that's for Insiders Fast. 

Office 365 Insiders considers these channels under its umbrella

 

FAST

What it's called on your platform:

  • Insider - PC
  • Insider Fast - Mac, Android, and Windows Mobile

Best for people who want to use the very earliest builds to identify issues and provide feedback about new features still in development.

It's ideal for those who don't mind the bit of risk involved in using unsupported builds.

SLOW

What it's called on your platform:

  • Monthly Channel (Targeted) - PC
  • Insider Slow - Mac, Android, and Windows Mobile
Copper Contributor

@Ed Hansberry @Sergei Baklan thanks for the response! The thing is, I switched to the fast insiders option in Excel but it said there were no updates available, but the formulae don't work... Any idea if I am missing something obvious ? Thanks!

@jamescrowley , what do you mean exactly under "don't work"? For example, if you start typing =seq do you see function name or it's no prompt?

image.png

(screenshort is for Windows, for Mac it shall be similar)

Steel Contributor

@jamescrowley give it 2-3 days. The feature is turned on at the server. So you can be using Excel and it will just start working. I don't know why they roll out some features to insiders this way, but they do, and Dynamic Array formulas was done this way.

My understanding is that not all insiders will have access yet.   It’s being phased in as part of testing.  Some are “lucky” and will have it, some won’t

 

Things may have changed by now but that’s how it started off anyway.

Copper Contributor

Any news on when this becomes available to the general public? I.e. 365 users? I'm currently using 365 ProPlus if that is of any help.

Copper Contributor

I would love to be using the new features, but despite having the current version (Excel 365 for Windows - v 1907 build 11901.20176), I am not seeing the features you list. Please help!

Steel Contributor

@Derek White - you have to either have the Insider build, which is on 1909 now, or the Monthly Targeted build, which is on 1908 right now. Even if you have the Monthly build and it is on 1908, I don't think the dynamic arrays have mode it to the normal monthly cycle. In Excel, go to the File, Account page and look at About Excel. The last line will tell you what channel you are on. If it doesn't say "Monthly Channel (Targeted)" or "Office Insider" you'll either need to switch to one of those (Targeted is more stable than Insider) or wait a bit longer.

Copper Contributor

I really have trouble with how UDFs are treated by this design, especially when a model is created on a machine that supports DA and then later viewed on a machine that doesn't... or vice-versa.

 

For example, say I am a software vendor writing the FooMaster add-in, which contains a single (awesome) UDF called Foo.  Foo always returns a double value.  There are no arrays involved.  FooMaster has been around for 30 years and has thousands of customers who have used it for forever.

 

Problem Scenario #1 (Minor)

  • Fred makes a workbook in Excel 2016 that calls =Foo() in some cell and saves it, just like he always does.
  • Fred sends his model to Wilma who is using an Excel with DA support.
  • Wilma opens the model and sees =@Foo() in a cell.
  • Wilma is confused because she's not seen the @ symbol before.  Office 365 just was updated and all of sudden Fred's models are all weird.
  • She does some reading learns it has something to do with the handling of arrays and says to herself... "But Foo has nothing to do with arrays!  What is going on?  The FooMaster people must have messed up!  Or Fred's been smoking something with Barney again."

 

Problem Scenario #2 (Major)

  • Harry has just gotten a DA enabled version of Excel.
  • He makes a new workbook with "=Foo()" in it.  (He doesn't type =@Foo() because why should he?  Foo has nothing to do with arrays and he's been typing these function this way for forever.)
  • He sends his workbook to Hermione who's still slumming with Office 2016.
  • Hermione opens the model, and sees a CSE array!  Hermione freaks out because she hates CSE arrays because they are scary.
  • Worse, FooMaster has a bunch of routines that scan workbooks looking for =Foo() functions so they can be tabulated and edited.  But FooMaster doesn't handle CSE array functions.
  • All of a sudden FooMaster workbooks can no longer be freely exchanged between users!

 

One possible cure for this would be to let a UDF creator be able to specify that a function will never return an array.

 

Thank you for your consideration.
Erik

 

 

So lovely. Looking forwards to use the array functions soon

Copper Contributor

My office 365 education don't have dynamic array

Copper Contributor

I've really appreciated the FILTER function. I may have found a bug when I try and concatenate a cell reference with a wild card search. 

Here's what returns the error message #CALC!

=FILTER('Registration Form'!N1:AB999,'Registration Form'!AA1:AA999=A1&"*")

I'm expecting an array of 5 form entries.

I know I can search using this format (concatenating a cell reference with a wild card) because the code below produces exactly what I would expect: the value 5.

=COUNTIF('Registration Form'!AA:AA,A1&"*")

Is this a known bug, or have I entered something incorrectly?

@JoeMcDaid

 

Copper Contributor

@Erik_Westwig, have you received any response from Microsoft? I raised concerns about the same thing about a year ago and am still not satisfied with the behavior in the current version. I am very worried that this will be released as-is and that spreadsheets created with our custom functions will suddenly show up as CSE array functions when they only return scalar values.

 

We use real-time data servers (RTD) for recalculation and there's a bug in older versions of Excel where the RTD server does not disconnect and will never refresh the data unless you restart Excel. I know this issue has been fixed in newer versions, however my worry is users opening the same files in older versions and running into all sorts of issues because everything is now marked as a CSE-array function...

Copper Contributor

@GabrielMichaud- No I'm afraid I haven't heard anything.  The whole compatibility issue with DA and UDF functions seems to me to be a very big mess, and I share your concern that they will just ship it like it works now.

Copper Contributor

I am still not seeing Dynamic Array features. My Excel version is 1910 (Build 12130.20390 Click to Run) Monthly Channel.

Office Updates are set to automatically download and install.

@Derek White , deploying on Monthly Channel started only few days ago. I don't know how many users are covered and how much time it takes. My guess it's not significant per cent and entire deployment could take months.

 

Build number doesn't matter. Only channel matters, after that is the lottery when Microsoft flick the switch for you.

As variant you may receive any new functionality even without build update, the code could be already within your build. Just be online to be activated.

Iron Contributor

Hey people You know things,

I have one conceptual (or technical) question about those dynamic arrays.

How correctly format cells filled (spilled) with dynamic array formulas so all and only spilled cells will be formatted accordingly

I know currently 3 options:
* manual formatting after spilled area change
* formatting all candidate range (column or row) accordingly
* using conditional formats with Applies to =xxx#

unfortunately this last one DONT WORK. Applies to C16# will be changed to regular range and not any more dynamic and exact same happend with INDIRECT

clipboard_image_1.png



clipboard_image_0.png

@Henn Sarv - yes, conditional formatting works only with static ranges. At least so far.

Copper Contributor

Hi,

 

I am running Office 365 Personal,

Version 1911 (Build 12228.20332 Click to Run),

Office Insider:

  • You are signed up for the Monthly Chanel (Targeted)
  • You will receive new builds of Office once or twice a month

What can I do, to get DA Excel version to test it? :)

Steel Contributor

@Gradjevinac - it should be in the Monthly Targeted channel unless they have halted it for some reason. But even at that, it can take a few days to show up after a fresh install or a move from one channel to a faster channel.

@Gradjevinac and @Ed Hansberry 

 

DA is slowly being release across the Monthly Targeted so not everyone gets hit with it in one go.  You can't control it

 

Also Check out Excel Online as it should now be showing up there

Also, rather than Monthly Targeted you could switch to Office Insider build 1912 to get access right now in the desktop version

@Wyn Hopkins , my understanding DA is slowly being release across the Monthly channel. It shall be available on Monthly Targeted, deployment for this channel started in June 2019 as I remember and now it shall be fully covered.

Yes Monthly too sorry, but also given it isn’t showing up for @Gradjevinac I’m thinking they are still testing with insiders too.  Just a thought. I’m not sure if / when a channel is “ fully covered”.   I’d be interested to know

Iron Contributor

just few days ago I installed (using ODT) on some training coputer InsiderFast and got Dynamic Arrays immediately
on other computer I had to wait few days

 

Wyn, I also don't know for sure about Monthly Targeted, just my guess - for 6 months and partly having now DA on Excel Online and Monthly, I assume Monthly Targeted is covered. At least on English SKU. With this case I'd support Henn, perhaps some time gap between installing new build and activation of the functionality on it.

Copper Contributor

Hi,

 

Thank you for explanations and answers. Just to give feedback, I just got DA Excel version. :)

 

Copper Contributor

@Erik_Westwig I would like to see an official answer from Microsoft regarding UDF and dynamic-array aware Excel. I am concerned about the impacts this will have on our product especially for users that open files with older versions of Excel. Just like you, I think there should be a way to let a UDF creator be able to specify that a function will never return an array. I raised similar concerns about a year ago when this was first introduced and I have yet to see any response :(

Iron Contributor

Nice to know - DA formulas avalilable in Excel Online

Sad to say - some things don't work as supposed

clipboard_image_0.png

Copper Contributor

@GabrielMichaudI did have a nice discussion with @JoeMcDaid on this topic.  He did hold out a little hope that we might be able to mark XLL functions as "non-array returning" but my impression was this might be a tall order.  There are some headaches with it for them... they have to make the decision how to process the wb as it opens and if a workbook opens when the XLL isn't yet loaded they won't know how to process it.

Copper Contributor

@Erik_Westwig thanks for the update - I did not realize that they were doing something on workbook open. When I first played with dynamic array-aware Excel last year it was not consistently updating my formulas to CSE-arrays so I assumed it was only doing it when you actually modified the cell where the UDF is used. 

Microsoft

@GabrielMichaud Erik is correct, we do the translation of all formulas into their modern equivalent at the time of file open. In order to do the translation, we need to know with 100% certainty that a function will always scalars and never arrays. For our first party functions we know this but we can't be sure for UDF's. Why? UDF's can be updated, enabled or disabled between loads - all of this means we cannot be certain it will always (and forever) return a scalar when we load the file. So, we are conservative and assume it could return an array. This developer focused page may provide more context: Range.Formula vs Range.Formula2

Copper Contributor

Thanks @JoeMcDaid 

 

Steel Contributor

Most users in my tenant are on the semi-annual channel. I thought semi-annual channel was updated in January. Would these features be expected by end of month? Thanks!

@Christine Green , of course Microsoft knows better, but that's my vision:

- DA only now (in Jan) appeared on monthly channel. Not sure it's 100% covered, but close to that base on feedback.

- if nothing critical next will be Semi-annual Targeted updated in March

Thus Semi-annual will be updated only in Jan 2021 since it's updated each Jan. That's a normal procedure, if only Microsoft doesn't do an exception in such case.

Copper Contributor

Are there any plans to allow formatting a dynamic array as a data table? We have built custom functions that return arrays in a table-like format and it would greatly improve usability if we could format that as a data table, especially when it comes to formulas and calculations that a user can add around that data table.

Copper Contributor

I am not the power user that most of the respondents are.   The new spill feature is absolutely devastating to my simple formulas.  As an example, I have a formula which calculates a value.   The cell in which the formula resides is a named cell - "Installed."   Further down on my worksheet, I simply want to enter the formula "=installed."  Because I have text data five or six columns over on the same row, I get the spill formula.  

 

I don't want the spill formula.  I want the number [which is 39] without having to manually type the number.   

 

MS support worked in my spreadsheet this morning for an hour before telling me they could not help me.

 

What are the options available to resolve this error.

Microsoft

@jbrooksws I'm sorry to hear about the issues you are encountering. Could you try adding an @ symbol before 'installed'? So =@installed? I think your formula must be relying on 'implicit intersection'. You can get more info on it here.

Iron Contributor

Is that by design or for future improvements but I can't use $A$!# like references in charts series 

I tried both - direct

=SERIES(;B$1$#;C$1$#;1)
and through defined name

=SERIES(;RidaB;RidaC;1)

something similar limitation still with table formulas on data validation ranges

Copper Contributor

@Henn Sarv I noticed the same thing as well with pivot tables. We have custom functions returning 2D arrays and we’d like to use it with the # syntax as data source for the pivot...

Copper Contributor

@jbrooksws - It sounds like your range name refers to a range of cells, rather than a single cell.  Entering =@rangename does not seem to work, but the two options below do:

1. Press F2 (edit), then Ctrl-Shift-Enter.  This is the old way of returning an array.  It does not auto-resize, but if you only want the one cell that is not a problem.

 

2. Edit the range name so it only refers to the cell you want.  If you didn't originally create the range name yourself I wouldn't recommend changing it because it may affect other formulas or functionality, but if you are sure the name isn't used elsewhere this should fix your problem.

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