Array Spill in Excel – have you tried using this before?

Array Spill in Excel – another way to do formulas and calculations in Excel that can save you time. Have you used it?

Array Spill in Excel: Excel iconThis week’s hint and tip is about the array spill feature in Excel. We are looking at how you can use this feature in Excel to save you time when creating formulas. This is covered on our Master Class Excel Silver course, but we decided to do a hint and tip on it as well. We are going to go through it now below.

 

What is array spill?

Array spill is a feature where formulas in Excel can be spilled easily into adjacent cells across or down instead of copying them using the fill copy button. This can often be a useful way of creating a large amount of formulas quickly and efficiently. Spill means that a formula has resulted in multiple values and those values have been placed in the neighbouring cells.

The associated video below explains this well using 2 examples. You can also download the spreadsheet in the video by clicking here (clicking here will download a copy to your computer so you can try it out!).

 

Example 1

The first example looks at multiplying sales unit by quantity in 2 different ways.

To see the different formulas see below:

Array Spill in Excel: Example 1

The ‘conventional way’ is by entering a formula into B5 and then copying it across row 7.

The newer way is by entering a formula like the one in B7 which spills across automatically to column G. The formulas can only be edited in B7, the other associated formulas spilled across are greyed out.

 

Example 2

Another example is show in the IF statement exercise below:

Array Spill in Excel: Example 2

Although the cells used here are relative cells it would work if one of the two cells in a formula is absolute.

See below:

Array Spill in Excel: Example 3

 

The video below goes through the Array Spill examples that you can see in the spreadsheet above. It talks you through how this array spill feature can be used in Excel to copy formulas in your spreadsheet. We hope that you find the video useful and enjoy learning about it!

Take a look below at the video to find out more!

For more information of some of these features see the content of our Master Class Silver Course here.

 

We hope you liked this hint and tip on the Array Spill in Excel feature, why not take a look at our previous one on the FILTER and VLOOKUP Functions in Excel?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy