COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Find out how dynamic arrays in Excel will change your (working) life!
Part three of a six-part series of blogs

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.

  1. Using dynamic arrays in Excel
  2. Can I use dynamic arrays?
  3. Introducing dynamic arrays (this blog)
  4. Filtering and sorting data
  5. Two more functions: RANDARRAY and SEQUENCE
  6. Implications of dynamic arrays

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?

Array formula in cell

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:

Array formula

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:

Curly bracket formulae

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:

New array formulae

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:

Spill over

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:

Formula in grey 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?

Obstacle to spill range

Hmmm ... we have a problem.

The answer is that we get a spill error:

Spill errors

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:

Select obstructing cells

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.

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

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:

A normal formula

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

 

Now type in a # character, to get this:

Creating a spill ref

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

 

Press Enter to create another spill range:

Creating a 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:

Spill range intersection

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.

This blog has 0 threads Add post