Array Spill in Excel – another way to do formulas and calculations in Excel that can save you time. Have you used it?
This 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 training 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:
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:
Although the cells used here are relative cells it would work if one of the two cells in a formula is absolute.
See below:
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?