BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Dynamic arrays are a new feature being rolled out in Excel 365. This blog explains how they work, and why they will make so many things in Excel easier.
Posted by Andy Brown on 14 February 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.
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.
If you delete the cells obstructing the spill range, it automatically then fills up with the appropriate values.
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.
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).
This feature is only included to allow formulae written using arrays in old versions of Excel to work.