Hi I’m Joe McDaid, a Program Manager working on new calculation experiences in Microsoft Excel. I wanted to share some details on an exciting enhancement to Excel’s calculation engine that will supercharge your formulas.
Formulas just got better
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 (or “spilled”) to the grid. One formula, many values. This will allow you to build more capable spreadsheets, faster, with fewer formulas and less chance of error.
Using Dynamic Arrays
Dynamic arrays support is built into the calculation engine, so all you need to do is write a formula that returns multiple values. This may sound complex but it’s surprisingly easy. Here’s an example:
- Type some names into cells A1, A2 and A3
- In B1 type =SORT(A1:A3) and hit enter
- BOOM! you have a sorted list of names
In addition to the new SORT function, dynamic arrays are accompanied by the all new UNIQUE, FILTER, SORTBY, SEQUENCE and RANDARRAY functions.
Availability
Dynamic arrays are currently available to all Insider users on Excel for Windows and Insider Fast users on Excel for Mac. While its currently only available for testing on Windows and Mac, dynamic arrays support is coming to all platforms.
UPDATE! Dynamic Arrays are now also available to Insiders on iOS and Android running the July update!
Feedback
As we prepare to make dynamic arrays generally available to all Excel 365 users, we’ve been actively listening to community feedback. Based on the feedback we’ve received to date, we’ve already taken up some improvements as detailed here. The best way to provide feedback on Dynamic Arrays and the six new functions is to use the Send-a-Smile (🙂) and Send-a-Frown (🙁) buttons in Excel.
Learn what other information you should include in your feedback to ensure it’s actionable and reaches the right people. We’re excited to hear from you!
Sign up for the Office Insider newsletter and get the latest information about Insider features in your inbox once a month!