Microsoft brings dynamic arrays to all Excel 365 users

Microsoft Excel is the world’s foremost spreadsheet application. Yet, Microsoft concedes that Excel’s formulas still have some limitations. The most notable is that a formula can only output data to one cell in an Excel spreadsheet.

So users need to re-enter, or copy, the same formula into multiple cells to apply it to a range of data within a column or row.

However, the big M has now announced it plans to enhance Excel 365 formulas with dynamic arrays that enables single formulas to return multiple values across a range of cells.

Microsoft actually first announced dynamic arrays for Excel in September 2018. Then Mr. McDaid, a Microsoft Program Manager, provided a preview for dynamic arrays on Microsoft’s website.

That page showed how dynamic arrays work with spilling that places multiple formula output within a blank cell spill range. However, dynamic arrays have only be available for MS Office Insider users. How to fix corrupted Excel files on Windows 10

Mr. McDaid has now announced that Microsoft plans to expand dynamic arrays so that they’re broadly available for all Excel users. Mr. McDaid stated:

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. […] While its currently only available for testing on Windows and Mac, dynamic arrays support is coming to all platforms.

Thus, dynamic arrays will become a standard Excel tool for formula. Users will then able to utilize dynamic arrays with numerous functions that return arrays.

Microsoft has confirmed that users can also utilize dynamic arrays with new FILTER, UNIQUE, SORTBY, SORT, SEQUENCE, SINGLE, and RANDARRAY Excel functions.

Dynamic arrays will certainly come in handy for manipulating spreadsheet table column data. Then users can apply function formulas to range of data within a column to a secondary column without needing to drag the formula down to copy it across a range of cells.

Instead, users can just enter the function as a dynamic array that will automatically apply formula output across a column for a selected range of cells.

Microsoft hasn’t provided any details for when exactly Excel users can expect dynamic arrays to be broadly available.

At the moment, the big M is still fine tuning dynamic arrays with feedback from a small insider user group.

RELATED ARTICLES TO CHECK OUT: 

• Word, Excel, and Outlook get new AI-powered features this month
• Office 365 lets you better control what data you share with Microsoft