Dynamic Arrays in Excel: Explained in Simple English

We talk about what are dynamic arrays and how you can make your Excel life easy using them.

Before we talk about dynamic ranges, let’s first understand what arrays are in Excel.

Array VS Vector

An array is nothing but a collection of cells in Excel. Now this may seem a very simple definition which may be a bit hard to believe but that is what it is.

In the above image the highlighted portion is known as an array with 5X3 shape where we have 5 rows and 3 columns.

However there is a special type of array known as “Vector” which consists of 1 dimensional array i.e. either it will be 1 row or 1 column.

In the screenshot above, we see that there are 2 different vectors, one being a column vector and another being a row vector.

It’s important to understand this difference as in some formulas we can only use vectors and in some formulas we can only use arrays.

Moving on, now that we know what an array is we can now talk about dynamic arrays. Excel has a bunch of special formulas that instead of throwing one single answer will throw multiple cells as the final output.

All those cells are called as dynamic array. And the reason we call them dynamic is because depending upon the parameters mentioned in the formula the number of cells may change.

The other thing to note about them is that, the number of cells will always be in MxN shape i.e. we will always have a some rows and columns. In other words, we can never have an uneven shape of arrays.

Why should I use dynamic arrays?

Well one of the advantages that comes to my mind top of my head is that they make calculations much easier compared to using the traditional formulas.

For example, if I were to calculate a simple IF formula where I have to categorize the ages ,as adult or non adult ,in the adjacent column in the below cells using the traditional ways, then I have to write the formula in the first cell and then drag it down.

The formula would look something like this:

=IF(X3<=19,"Yes","No")

However if I were to use dynamic arrays approach then the formula would look like:

=IF(X3:X13<=19,"Yes","No")

.And the output would be a dynamic array (note that the dynamic arrays highlight when clicked on them to differentiate them from normal arrays)

Another advantage of using dynamic arrays is that it becomes easy to refer to such formulas in the workbook. Continuing with the above example, if I were to refer to the entire dynamic array then I can do it with

Y3#

Why can’t I edit part of the dynamic array?

If you are working with dynamic arrays, you won’t be able to edit part of the array i.e. either the entire array exists or it does not exist at all. One cannot edit any cells inside it.

What is #SPILL error?

When working with dynamic arrays, you may encounter a special error #SPILL. This means that in the cells were the dynamic array output was going to be, those cells are not empty.

In other words, if you want to use dynamic array the make sure that the cells where the answer would appear from the formula should be empty.

---

If you have any other questions related to dynamic arrays then do comment down below and I would be happy to help.