Dynamic Arrays in Excel

Description
Dynamic Arrays (“DA”) allow you to work with multiple values at the same time.

Dynamic Arrays are only available in Microsoft Excel 365. The functions that are available are presented below:

FILTER
RANDARRAY
SEQUENCE
SORT
SORTBY
UNIQUE

If any value in the data set is changed the UNIQUE formula will automatically update the results.

When data changes the spill range (the area covered with data) will expand or contract as needed.

To refer to a spill range you can use the hash symbol (#).

There are cases where we need an array of random numbers. We can produce such a random array of numbers based on the formula RANDARRAY.

One of the most interesting formulas to work with arrays is the filter formula. This formula allows the user to create a pivot table, but with a formula instead of using the ribbon menu.

Finally we can also use arrays with simple operators such as additions, subtractions, multiplications and divisions (+ , - , * , / ).

Comparing this with “traditional” excel formulas, we would have to use 40 formulas to produce the same results. Even if we increased the years to 40 years, we would still use 4 formulas with dynamic ranges, instead of 160 formulas with traditional excel formulas.

This makes models easier to audit, saves time, and avoids errors.