This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Using dynamic arrays in Excel Can I use dynamic arrays? Introducing dynamic arrays (this blog) Filtering and sorting data Two more functions: RANDARRAY and SEQUENCE Implications of dynamic arrays

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.

Using dynamic arrays in Excel
Can I use dynamic arrays?
Introducing dynamic arrays (this blog)
Filtering and sorting data
Two more functions: RANDARRAY and SEQUENCE
Implications of dynamic arrays

Posted by Andy Brown on 14 February 2020

In this blog

The old days - array formulae
The new idea - spill ranges
Spill errors
Spill refs
Spill intersections

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.

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, 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).

This feature is only included to allow formulae written using arrays in old versions of Excel to work.

Parts of this blog
Using dynamic arrays in Excel Can I use dynamic arrays? Introducing dynamic arrays (this blog) Filtering and sorting data Two more functions: RANDARRAY and SEQUENCE Implications of dynamic arrays

Some other pages relevant to the above blogs include:

Microsoft Excel training classes

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Introducing dynamic arrays

The old days - array formulae

The new idea - spill ranges

Spill errors

Spill refs

Spill intersections

Head office

London

Manchester