Dynamic Arraysare one of the biggest changes ever to Excel’s formula engine.

This behavior is limited to the Office 365 version of Excel.

Remember also thatPivot Tablescan be used to solve many of these same challenges.

Click a function name for an overview and examples of usage.

In almost all cases, the formulas are more complex and clunky than an equivalent dynamic array formula.

At the same time, these formulas should work in almost any version of Excel.

Sorting

Dynamic Excelprovides two new functions to handle sorting with formulas:SORTandSORTBY.

Dynamic Excel provides a special function, just for this purpose: theFILTER function.

if you are using an older version of Excel, there are various ways to approach the problem.

Unique values

Dynamic Excel provides a dedicated function for working with unique values: theUNIQUE function.

In other versions of Excel, you’ll need to cobble together solutions based on several other functions.

Sequential values

One of the new Dynamic Array functions isSEQUENCE, specifically designed to generate numeric sequences.

Commonly, you’ll see solutions that use theROW functiontogether withINDIRECT.

Here are some examples:

Random values

TheRANDARRAY functionis also new in Dynamic Excel.

RANDARRAY can generate random decimal or integer values in columns, rows, or two-dimensional arrays.

This is a huge benefit when building formulas that need to perform random sorts, or random item selection.