Introducing dynamic arrays

To understand what dynamic arrays are, and how to use them, let's look first at how things used to work.

The old days - array formulae

What would you expect to happen when you press Enter for the following formula?

We're trying to show the contents of C2 to C11 in a single cell - surely you can't do this?

In old Excel, you'd expect to see an error (you can't squeeze the contents of 10 cells into one).  The only way round this would be if you selected all 10 cells, typed in the formula shown and pressed SHIFT + CTRL + Enter:

In old Excel, you could only fill multiple cells like this by creating what's called an "array formula".

This would create a formula with curly brackets:

The curly brackets signify that this is a special array formula. Dynamic arrays make array formulae like this redundant!

The new idea - spill ranges

In new Excel, if you type in this formula and press Enter:

Here we're entering the same formula, but because this is the new version of Excel the results will be very different.

You get a spill range like this:

The formula has spilled into the cells beneath it.

If you select any cell apart from the one at the top, Excel shows the formula in faint type:

This shows that although the formula for this cell is =C2:C11, you can't edit or delete this cell in isolation.

Spill errors

The first obvious question to ask is this - what happens if there's something in the way of the spill range?

Hmmm ... we have a problem.

The answer is that we get a spill error:

Excel knows it can't show the value of the formula in all of the spill range's cells, so it shows a spill error instead.

It's easy enough to use the error message to select the offending obstructing cell:

Choose this option to highlight the cell or cells which are getting in the way of the spill range.

Spill refs

You can reference any spill range by suffixing it with a # character.  To see how this works, assume you've created a spill range (I've also coloured it to highlight it):

The spill range, as created above.

Now go to any blank cell (it doesn't have to line up in the same row or column) and type in a reference to the top cell in the spill range:

This is a normal formula!  So far, anyway ...

Now type in a # character, to get this:

Instead of referencing cell D2, the formula now references the spill range beginning at cell D2.

Press Enter to create another spill range:

The new formula shows the values from the old spill range.

Spill intersections

One other bit of theory - sometimes you will want to pick up a single value from a spill range.  To do this you can use the @ character:

Here the @ character tells Excel to pick out the corresponding row from the spill range (in this case the formula will show 7 when you press Enter).