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 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 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 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 range
New Functions
Here is the full set of functions that will be accompanying dynamic arrays.
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.
• 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:
You can learn more about dynamic arrays from these resources:
- the spilled range operator and how to use it
- the various dynamic array behaviors
- how dynamic arrays compare to legacy array formulas
- new #SPILL error and scenarios that can trigger it
- implicit intersection and dynamic arrays
- dynamic arrays in prior versions of Excel
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
Program Manager, Excel
Updated Oct 05, 2020
Version 11.0
JoeMcDaid
Microsoft
Microsoft
