Home
Microsoft

Preview of Dynamic Arrays in Excel

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 Blog GIF final.gifDynamic 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.
 
NoSpill Blog GIF final.gifSpilling 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.
 
 Integrated Blog GIF final.gifUsing 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.
 
 Spill Range Reference Blog GIF final.gifUsing # 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.
SINGLE – accepts a range or array and returns a single value using implicit intersection.
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
34 Comments
Regular Contributor

This is incredible. I cannot wait to try them out.

 

I am on Insider Fast in Office 365 but they don't exist for me yet. I think it is a shame that even if we sign up for Insider Fast the rollout of new features to try is spread out. I thought the point of Insider Fast was to get the latest publicly available features, not to sign up to be on a list to randomly get the latest features.

Senior Member

This is super exciting! I use the FILTER function in Google Sheets so having this functionality come to Excel is great.  I can already see several ways these new features are going to help out in my work, so I’m looking forward to trying them (and hoping to be in the initial Insider testing group to get them sooner!)

Frequent Visitor

Thank you - this is a very exciting feature.

I have a few initial questions:

  • Can dynamic arrays be used with RTD-based functions? Currently there are some bugs (including buggy RTD topic disconnect) when RTD is called from legacy CSE array formulae (even if the RTD call is through an indirection). It would be an immense help if there were RTD support in conjunction with the dynamic arrays, for async, streaming and other scenarios.
  • Do I understand correctly that any UDF defined in a .xll add-in which returns an XLOPER will automatically work with the dynamic array behaviour? Can I effectively recreate a "FILTER" or "SORT" function inside an add-in or do these functions themselves have internal magic too? Is "SINGLE" special or can I opt into implicit intersection behaviour from inside my array-returning UDF function by calling SINGLE via the C API?
  • Can dynamic arrays be used inside Tables? Currently array functions cannot be put in Tables. It would be great to have the auto-expanding and shrinking Table behaviour anchored on a synamic array function.
  • How does the # spill operator impact the reference passed to a function taking an XLOPER reference parameter in the C API? Is there any way to know that a parameter is a dynamic array (as opposite to a fixed, explicit range)?
  • How is the caller identified for dynamic array functions (via xlfCaller in the C API or Application.Caller in VBA)?
Occasional Contributor

This is awesome! I've been hoping that Excel would add the UNIQUE and SORT functions that have been available in Google Sheets, but I am even more impressed that Excel has done far more than just adding these functions. I can't wait to see what I can do with the new A1# notation (I'm really hoping it can be used in data validation dropdowns). And the SEQUENCE function is brilliant - ought to make creating advanced formulas easier (and hopefully without being volatile).

Valued Contributor

Awesome!

But, I hope you update Excel 2019 to include these new features!

Please do not just limit them to Excel for Office 365.

Occasional Visitor

 Loving the new spill concept and new functions. Will vastly improve dynamic named ranges so that no longer is it an offset with a calculated height and width but just $F$5#.

 

And thank you for native UNIQUE and SORT functions. I can't wait to retire my Excel VBA QuickSort / Unique library plus this reduces the number of functions I need to rewrite in the Javascript API, so double points.

 

 

Regular Contributor

Does someone that has these know if you can wrap other functions around them? For example, would =COUNTA(UNIQUE(A1:A10)) return the number of unique items in A1:A10?

The old way with the CTRL-SHIFT-ENTER would involve something like =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)

Occasional Visitor

So then, to get a concatenated list of the unique values in some range, just embed UNIQUE inside TEXTJOIN?

 

Brilliant!

Microsoft

@Ed Hansberry we're glad you like the look of the feature! Yes you can wrap functions. =COUNTA(UNIQUE(A1:A10)) will work as expected.

 

@Emma Bailey @Phil Trick Thanks! Looking forward to getting it out.

 

@Jon Wittwer It can be used with Data Validation. DV + UNIQUE + A1# = 👌 

 

@Haytham Amairah Dynamic arrays will not be in Excel 2019. Excel 2019 has just been released and this feature is only just making it into O365 insiders.

 

@Patrick Matthews Sure can! 

 

@Govert van Drimmelen Great questions! I'll follow up with the answers next week. 

Really powerful new feature,  absolutely loving it.     

Dynamic drop down lists are now simple

Many uses of Pivot Tables are now easily replicated in automatically refreshing formulas

Well done Excel Team!

Frequent Visitor

@Joe McDaid What would be the official way of checking whether an Excel instance hosting my add-in supports dynamic arrays? Is there a first Excel version that supports dynamic arrays and any later version will too, or will this be selectively enable depending on other factors? How should I check from inside my add-in?

Regular Contributor

Who do I have to bribe to get them to enable this on my Fast Insider build? I can send money via paypal, venmo, apple pay, etc. :-)

Occasional Visitor

I would love to know the roll-out timetable, I'm unfortunately not on the insider list but want to get all my work colleagues tee'd up at the right time to change all of our many spreadsheets - so much complexity can be removed, so much storage space can be saved!

Regular Contributor

Is anyone seeing a lot of formulas being wrapped with SINGLE() after getting the new functions?

 

I have a number of spreadsheets that return a single row table from Power Query, and then I refer to those tables in other formulas. So =TableName[FieldName] would give me the count of rows in a query, or the total of a value, etc. Usually used for error checking. One of my files this morning had 7 SINGLE() functions in it. some simple as above, some deeply nested in those stupidly long uneditable formulas I am prone to do over time.

 

Now those are all showing up as =SINGLE(TableName[FieldName])

 

Creating a new single row table in a new file, typing =Table2[data field] does not cause it to wrap in SINGLE(), however.... if I create the formula in Excel Online or an older version of Excel, =Table1[Test data] becomes =SINGLE(Table1[Test data]) when I open it in the new version.

 

EDIT: Is this the right place to discuss issues or questions about these new functions?

@Ed Hansberry, I guess that's due to algorithm of the conversion of old style expressions to new one. That's like ={1,2,3} in current Excel is converted to first element in background and returns 1. New Excel will return spill for the same formula. To return only first element we shall use =SINGLE({1,2,3}).

 

When we open in new Excel the file with such formula created in classic Excel it shows such conversion with SINGLE.  In classic Excel "single" is somewhere in background and not visible.

 

Table[Column] is an array which is returned to the range. For one element only it could be converted into one cell range and we see something as above.

 

All above is only my guess, I didn't dig too deep with investigations and have no Microsoft knowledge of things.

Regular Contributor

Thanks @Sergei Baklan - makes sense, and was along the lines of what I was thinking. I think this will cause a bit of initial confusion, and wonder if the conversion from old to new should be smarter and not put =SINGLE() every possible place. 

 

if =TableName[Field] gives same result as =SINGLE(TableName[Field]) then don't wrap it with SINGLE().

 

I suspect this is one of the biggest changes Microsoft has done with Excel in recent memory that has such implications to not change how calculations are expected to work as files are upgraded to the new calculation engine.

Regular Contributor

Wondering if this early in the process there is still time to get changes to these dynamic array functions. I think a super useful argument to these would be a TopN optional argument.

 

So =SORT(A1:A100,,,,10) would sort the data in A1:A100, but only return the top 10. The only way I can see to do that is to put the =SORT() funcition in Cell C1, highlight C1:C10, then edit, and old-school CSE the thing to a hard array.

I might be able to figure out a way nesting FILTER() and SEQUENCE(), but a TopN argument would make it so simple.

 

Oh wait. This works. Put some random text in R3:R20. This will return the top 10 items. this is ugly, with a capital U.G.L.Y. But... it isn't a CSE formula. I'm sure I could clean this up as I threw this together in about 10min, but still. TopN dude....

 

=LEFT(FILTER(R3#&SEQUENCE(10,1,0.00001,0.0001),NOT(ISNA(R3#&SEQUENCE(10,1,0.00001,0.0001)))),FIND(".",FILTER(R3#&SEQUENCE(10,1,0.00001,0.0001),NOT(ISNA(R3#&SEQUENCE(10,1,0.00001,0.0001)))))-2)

Microsoft

@Ed Hansberry Great to hear you now have access!

 

Old Excel would silently apply implicit intersection logic to its formulas to force single value results. Dynamic Array Excel no longer needs this as it can output arrays natively. So instead of implicitly intersecting, DA Excel calls out where this could have happened in old formulas using the SINGLE function. In your example, =TableName[Field] is a  full column reference that only has one value but if you ever added a new row to that table, that very same reference would return multiple values and, in old Excel, it would have triggered implicit intersection. To ensure your formula continues to operate as originally authored, we show where implicit intersection would take place using SINGLE. You are free to remove the SINGLE from your formula if you do not want implicit intersection to apply.

 

TopN sounds like a great function candidate. Our initial focus is on getting Dynamic Arrays out with the 1st wave of functions but we are looking forward to unlocking more scenarios with future functions. If there is a function you'd like to see, please add it to uservoice so that we have a place to discuss the design and the community can vote. 

Regular Visitor

Can these functions be used in the Criteria Range of an Advanced Filter

Sam

Regular Contributor

Done @Joe McDaid  - https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35667973...

 

I wasn't thinking of a separate TopN function, just an optional parameter in the Dynamic Array functions that it would make sense for. SORT and FILTER could definitely use it for example.

Thank you @Ed Hansberry, voted

Regular Contributor

So I guess #CALC! is a new error type when the arrays go awry?

 

Actually looks like #UNKNOWN! and #FIELD! are also new for Array errors. This page needs to be updated. I'd like more explanation on what these error types are to trap things with a bit less trial and error.

Regular Contributor

I think this is a bug with SEQUENCE().

  1. Create a table with 10 rows not including the header.
  2. Have at least 2 columns, say Col1 and Col2
  3. Fill column 1 with jibberish.
  4. Put this in the first row of Col2: =SEQUENCE(ROWS([Col1])

It returns #SPILL! instead of a sequence of numbers that matches the number of rows in the table. ROWS([Col1]) is correctly returning 10. Replace ROWS() with 10 and it works, but it is no longer dynamic.

 

I submitted a frown face for this.

 

Yes, #CALC! is a new one and not documented yet

By the way, =LARGE(array,SEQUENCE(N)) works as TopN

image.png

@Ed Hansberry, how did you receive =SEQUENCE(10) in the table without errors? SPILL doesn't work with tables https://support.office.com/en-us/article/-spill-error-table-formula-8bfa1758-1be4-42f4-a974-c4d4bc51...

Regular Contributor

Hrm.... Now I am trying to recreate what I did. SEQUENCE(1) works, but not anything >1. Maybe I was doing some inside of and some outside of a table.

 

As for your LARGE() function. Very nice. Much nicer and cleaner. 

Regular Visitor

1. Can the new array formulas be used in the Criteria Range of an Advanced Filter 

2. Can SORT be used as below to take advantage of the Binary search option

MATCH(B1,SORT(A1:A10),1)

New Contributor

Hi,

I Can't see Functions like Sequence, Filter in Office 365 Insider! Could anyone please help here?

 

Regards,

Mohit

Hi @Mohit Verma, not everyone gets everything straight away in Office Insiders Fast,  things are gradually released.

 

It can be frustrating having to wait

New Contributor

Hi @Wyn Hopkins.. Thanks for the update! I will be looking forward to get this update. Super excited to explore new features! :)

 

Regards,

Mohit Verma

Regular Contributor

@sameer bhide

  1. The array functions can be used in the criteria section of the Advanced Filter feature, but the new array functions will return results that dynamically expand and contract, so make sure the range in the criteria will dynamically expand as well. By default it uses absolute references.
  2. You can use the arrays in a Match, though in your example, sorting isn't necessary to match. But it would work great with a FILTER() for example.
Regular Contributor

Does anyone know a way to find Excel files in Office 365 with CSE formulas in them? Searching for { and ={ returns nothing or in the case of Delve, errors. I'd love to see what has been done in the org over the past 2 decades and see what can be replaced with the new functions.


Regular Contributor

Hmmm... so in New Excel,

 

=SUM(A1:A10*B1:B10) replaces =SUMPRODUCT(A1:A10,B1:B10)

 

Will SUMPRODUCT() and other functions that ignore implicit intersection be depreciated as existing functions start ignoring it as well - where you'd need to use SINGLE() to continue using it? (Not dropped, but where things like CONCATENATE() are not encouraged as CONCAT() is the new way to do it.)