Excel now has Dynamic Arrays – Windows, Mac and more …

Office-Watch.comLast updated: 14 December 2019

Microsoft Excel,Microsoft Office,Microsoft Office for Mac,Office 365,Office Mobile - iPad, Android, phones

After many months of testing, all Excel 365 apps now have dynamic arrays (except Excel Online) though some are just for Insiders at present. Dynamic Arrays are a major and powerful expansion of Excel’s powers. The new abilities, functions and error messages might seem scary but are a great leap forward for Excel.

All Excel 365 incarnations, except the browser-based Excel Online, now have dynamic arrays – not just Windows. Dynamic arrays are in the July updates of Excel 365 for Windows, Mac, Apple and Android devices (see below for the full list). Even the forgotten Windows Mobile version of Excel 365 now has Dynamic Arrays.

What are Dynamic Arrays?

Arrays have been in Excel for a long time. Excel Array formulas for everyone Array Formula with IF filter

Dynamic Arrays are automatic, resizable arrays. Instead of making arrays with the Ctrl + Shift + Enter keycombo, dynamic arrays are made with new functions like Sort(), Sortby() and Filter() as well as existing functions that work slightly differently.

Dynamic arrays will revolutionize the way even simple lists are managed and displayed. For big tables and data updated from outside sources (like the Stock data type) it’s a huge improvement.

One formula = many results.

Any Excel user knows that a cell formula makes a result which appears in that cell.

Dynamic Arrays mean a single cell can put results into many adjoining cells. Microsoft calls that ‘spill’ as in – results ‘spill’ into other cells.

‘Dynamic’ or changeable in two ways:

The array of results can expand or shrink depending on the number of cells needed.
The function is updated with each recalc. Existing table functions like sorting and filtering don’t update as data changes.

They are better than the old Excel arrays because they can resize automatically, have a visible boundary on the worksheet and come with cool new functions.

Excel experts are having a fun time exploring the complexities of Dynamic Array. New possibilities for worksheets or simplifying and speeding up existing workbooks (simpler formulas, dumping now unnecessary VBA).

We’ll focus on how Dynamic Arrays can greatly improve even simple Excel lists and tables.

Dynamic Arrays and Tables

At the moment, an Excel list or table can be sorted and filtered from the pull-down list.

That works but isn’t dynamic or recalculated. If a list is changed the sorted list gets out of order. At worst, a filtered list (e.g. ‘Top 5’) can show the wrong results. VBA or PowerQuery can work around those limitations but not in any simple and fast way.

Here’s a quick example. A list of students and test scores in orange at left. The blue heading lists at right are sorted and filtered versions of the original table using just five dynamic array functions.

It takes only 5 formulas (in cells F2, I2, L2, O2 and R2) with dynamic arrays, instead of all the clumsy, error prone nonsense we had before.

And, if the original table is changed (extra student, spelling mistake or test re-graded) the other lists are all updated automatically.

With one table of data you can now ‘slice and dice’ the list in many different ways without touching the original table AND know the sorted/filtered lists are always up to date.

It’s a lot faster which is a big deal for much, much longer lists.

New sort and filtering functions

Dynamic Arrays introduces six new functions which are new and faster ways for mostly existing Excel features. Here’s a summary of the new functions with links to the official Microsoft explanation.

FILTER() filter a table, range or array to show only some items from the source.

SORT() to sort a table, range or array.

SORTBY() sorts a table, range or array based on a one part of the source. For example, sort a list of class test results by student scores.

UNIQUE() a list of values without any duplicates.

SEQUENCE() makes series of numbers (e.g. 1, 2, 3) across multiple columns or rows. Seemingly irrelevant, Sequence() can be combined with Index() to make ‘Top 5’ or ‘Bottom 10’ filtered lists.

RANDARRAY() makes random numbers across many columns or rows.

Already in Use

Office Watch readers are already using dynamic arrays because we’ve already added coverage and examples to our ebook Real Time Excel. Excel’s Stock Data type updates global stock and index prices on a near real time basis. Tables using that changing data need a way to be resorted automatically.

Dynamic Arrays does that quickly and easily. Lists of most valuable stock holdings, greatest price change and many other variations can be setup knowing they are updated automatically.