Dynamic Arrays and New Functions in Excel!
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
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!
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. So please keep the great feedback coming!
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.
Comments are closed.