Microsoft Adds Dynamic Arrays and New Functions in Excel 365

[Brink]

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.

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. 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.

Source: Dynamic Arrays and New Functions in Excel! - Office Insider Blog

Tweet

— Twitter API (@user) View on Twitter