How to understand dynamic arrays in Excel

Many of my favorite new Excel features like FILTER(), UNIQUE() and SEQUENCE() are so-called “dynamic array” functions. But other than being really great, what does it mean to be a dynamic array function? How do they compare to “static” arrays? Let’s find out in this blog post.

What is an array in Excel?

First things first: an array is simply a collection of values in Excel. This could be as simple as the numbers 3, 4 and 7 in cells A2:C2.

Arrays versus ranges

A2:C2 is an example of a range, yet we’re referring to it as an array. What’s the difference? The range is the actual set of cells and their physical location, whereas the array is the actual data in those cells. You can read more about the difference here.

Array references

Static array references

Now, let’s use cell references to refer to the underlying array in A2:C2. In classic Excel, this would be done by referring to the range, then pressing Ctrl + Shift + Enter to indicate you are referring to an array of values rather than just one:

Excel only shows one piece of data per cell, so while you’re referring to an array of three, only one cell is populated. You can fix this by highlighting E2:G2 and running the same formula:

Here you’re starting to see the shortcomings for how Excel traditionally handled arrays: references were clunky to write and manage with the Ctrl + Shift + Enter closer and unable to automatically adjust for the correct number of cells. Due to this behavior we could call these “static” array references.

Dynamic array references

“Modern” Excel negates these shortcomings with dynamic arrays. All that’s needed now, for example, to refer to A2:C2 is to simply type =A2:C2 and hit Enter:

Not only are the cumbersome keystrokes gone, but Excel dynamically detects the length of the array.

Array functions

Static array functions

Now that you see the difference between static and dynamic array references, let’s look at functions taking those references. Remember — an array function can return multiple and variable pieces of data. How is this handled differently in classic versus modern Excel?

For an example, we are going to use a static array formula to list the unique values found in a range (h/t ExcelJet). Don’t worry too much about how this formula works — you’ll see a much more sensible alternative in a bit.

For now, just take a look at how naive static arrays are to the number of pieces of output it should return:

Cumbersome and stupid — you get enough of that from following me! I don’t think you want that in your formulas too.

Dynamic array functions

Let’s see how dynamic array functions fare here. Pretty well — in fact, there’s even a UNIQUE() function to take care of this:

Again, notice that the range of output cells is automatically detected and filled down. This will instantly update with any changes to the source data:

N x the output, N x the fun with dynamic arrays

In this demo you learned about how to refer to and operate on arrays in Excel, and why dynamic arrays are such a step forward.

Have you refactored any static arrays to dynamic? What use cases have you found for UNIQUE() or other dynamic array functions? Let me know in the comments.