Preview of dynamic arrays in Excel

New features coming to Excel soon.

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.

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.
• SINGLE Accepts a range or array and returns a single value using implicit intersection. (Superseded)
• RANDARRAY Returns an array of random numbers between 0 and 1.

For more information, also see Mr. Excel: Major calc engine change.