• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Microsoft Adds Dynamic Arrays and New Functions in Excel 365


Brink

Administrator
Administrator
mvp
Posts
23,833
#1
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

 

My Computer

System One

  • OS
    64-bit Windows 10
    Computer type
    PC/Desktop
    System Manufacturer/Model
    Custom self built
    CPU
    Intel i7-8700K OC'd to 5 GHz
    Motherboard
    ASUS ROG Maximus XI Formula Z390
    Memory
    16 GB (8GBx2) G.SKILL TridentZ DDR4 3200 MHz
    Graphics Card(s)
    ASUS ROG-STRIX-GTX1080TI-O11G-GAMING
    Sound Card
    Integrated Digital Audio (S/PDIF)
    Monitor(s) Displays
    3 x 27" Asus VE278Q
    Screen Resolution
    1920x1080
    Hard Drives
    1TB Samsung 970 EVO Plus M.2,
    250GB Samsung 960 EVO M.2,
    6TB WD Black WD6001FZWX
    8TB WD MyCloudEX2Ultra NAS
    PSU
    OCZ Series Gold OCZZ1000M 1000W
    Case
    Thermaltake Core P3
    Cooling
    Corsair Hydro H115i
    Keyboard
    Logitech wireless K800
    Mouse
    Logitech MX Master
    Internet Speed
    1 Gb/s Download and 35 Mb/s Upload
    Browser
    Internet Explorer 11
    Antivirus
    Malwarebyte Anti-Malware Premium
    Other Info
    Logitech Z625 speaker system,
    Logitech BRIO 4K Pro webcam,
    HP Color LaserJet Pro MFP M477fdn,
    Linksys EA9500 router,
    Arris SB8200 cable modem,
    APC SMART-UPS RT 1000 XL - SURT1000XLI,
    Lumia 1520 phone

Users Who Are Viewing This Thread (Users: 0, Guests: 1)