Making Magic Dynamic Arrays

In this article, we’ll explore this new, powerful feature of Excel. What is it? At a rudimentary level, it’s a feature where you enter a formula in a cell, and the result of that formula shows up in many cells! The range where it shows up is called the spill range. Let me show you what I mean with a simple example, using the new SEQUENCE function:

The formula was simply entered in cell A1, but you see the result in A1:A5. Also notice there’s a light box around A1:A5. If I click on A2, note the difference in the formula bar:

It looks disabled, or greyed out. Any of the cells A2:A5 would show the same thing. What if there were an “X” in A4 before entering the formula?

You get a new error, #SPILL! which means there’s no room to show the result. Let’s look further at the syntax of the SEQUENCE function: =SEQUENCE(rows, columns, start, step). This means if I enter =SEQUENCE(3,4,2,7), I’d expect to see 3 rows X 4 columns, starting with 2 and increasing by 7:

If I refer to cell A1 in another cell (like F1), it works as usual:

But I can reference the spill range by appending a “#”:

I can also branch to the spill range via F5:

The SEQUENCE function can also be put inside other functions:

Here are the first 12 principal payments in one formula:

It’s too bad only the first cell gets formatted, but formatting the rest is pretty straightforward.

In earlier versions of Excel, you would see the following when entering the =A1:A10 in cell C3:

 —it’s called implicit intersection. If you entered the formula in C4, you’d see a “d”. In the latest versions, to replicate this behavior, you’d have to enter =@A1:A10. (In early versions of dynamic arrays, you’d have to enter =SINGLE(A1:A10) but that now gets changed to =@A1:A10 anyway!) Here’s what you see in the current version:

It's as if you entered =A1#.

Okay, it’s time to look at other new dynamic array functions. They are SORT, FILTER, UNIQUE, SORTBY, and RANDARRAY. Let’s start with SORT. This is nearly the same as using the SORT command from the ribbon, but it’s dynamic! That is, of the data changes, or is added to, you needn’t revisit the command –- the function recalculates! Have a look:

…and if I change cell A7 from “good” to “bad”, the range automatically re-sorts:

Let’s look at the parameters for the SORT function:

=SORT(array, sort_index, sort_order, by_col)

The array is the range to sort; sort_index is the column to sort by; Sort_order is 1 for ascending and -1 for descending; by_col is FALSE to sort by rows (usual) and TRUE to sort by columns. Here’s an example of a multi-level sort – sorting Name within Region:

The inner sort –SORT(A2:C19,2) sorts the array solely by name. Passing this to another Sort then resorts that by Region!

The SORTBY function enables you to sort a range by values outside the range, as in the following example.

SORTBY has this syntax—=sortby(array, by_array1, sort_order1, [by_array2], [sort_order2], …)

Here, we used the values in column D to sort the values in A:B. The 1 in D2 indicates this should be the first – Tom; the 2 in D7 indicates Srivan should be next, as you can see in F3:G3.

The FILTER function enables you to have a dynamic filter, instead of using the filter from the ribbon. The syntax is =filter(array, include, if_empty). That is, you specify what the range is you’re filtering, the rule determining what to show, and what to display if nothing is found.

Here’s a small example:

The range being filtered is A1:D12. The “include” argument is A1:A12=F1 which winds up being an array of TRUEs and FALSEs. Wherever this is a TRUE, it shows in the filter. Note that neither range needs to be an absolute reference. Simply by changing the value in F1, we get a new list:

And if we put in an invalid name:

If we left off the “No Match”, we’d see this:

You can simulate multiple filter criteria (like AND and OR) by using * for AND and + for OR as seen in these examples:

First, OR:

The formula says filter the array based on column A being “Daily” and column C being “Central”. If either is true, it shows in the filter.

Note carefully the use of the parentheses. (A1:A12=F1) and (C1:C12=F2) each produce an array of TRUE/FALSE, When added together, it becomes an array of 1’s and 0’s. The 1’s are what’s shown in the filter. By simply changing the + to *, we have an AND condition:

Let’s look at the UNIQUE function, using the same data:

We see only the unique values from column C! (I manually colored cell F1 to match).

Lastly, the RANDARRAY function. The syntax is =RANDARRAY(rows, columns, min, max, integer) – all the parameters are optional. If I just enter =RANDARRAY(), I get the same as =RAND():

If I specify 5 rows:

3 columns and values from 40 to 75:

And lastly, I want all integers:

Hope you enjoyed this article.