How to use the Spill function Excel (Excel)

I had neglected the "spill" feature implemented in Microsoft365 and Excel2021 for a while because I didn't really understand it, but when I tried it, I found it to be very useful. I found it to be very useful. Moreover, I think you can do a lot of things by applying this function.

First of all, before I forget, I would like to introduce this spill function here.

What is Spill?

What is a spill?Spill, overflow, scatterand other meanings.

The name is derived from the fact that the way the function displays values not only in the cell where the function was entered, but also in other cells where the function should have been copied is similar to the way water overflows from a cup.

basic functionality

For example, suppose you have an inventory control chart as shown in the figure below.

I would like to enter the function Unit Price (USD) x Exchange Rate x Quantity in the Amount column here.

How to input functions up to now

First, let's try to input the data as before.

=C5*$D$2*D5

To ensure that the references do not shift when the function is copied, the "D2" cell is marked with "$D$The trick is to type "2" to avoid errors.

Copy this function down to the bottom.

The correct calculation results were successfully output.

How to enter a function using a spiel

Next, try entering the data using the Spill function.

=C5:C10*D2*D5:D10

Since the function is not copied, the "D2" cell also remains a relative reference.

Press the "enter" key in this state.

Just like that, all the numbers were entered down to the last line.

Application (combination with INDEX and MATCH functions)

For information on how to combine the INDEX and MATCH functions, seeHow to use Excel (Excel) INDEX and MATCH functions in combinationPlease also refer to this article.

First, prepare a "client master" as shown in the figure below.

Next, we have prepared destination information data in which only the customer code is entered, as shown in the figure below.

Enter the following function in the "C3" cell of this layer destination information sheet.

=INDEX(Customer master! B2:F7,MATCH(B3:B5,Customer master! B2:B7,0),MATCH(C2:E2,Customer master! B2:F2,0))

Since there is no copying here either, there is no need to be aware of absolute or relative references.

Press the "enter" key in this state.

I was able to put values in all cells in one shot.

Conclusion

Advantages

• Just put the function in one cell to complete the table.
• No defects caused by a function being entered as a value input or a function disappearing in the middle of a function.
• No need to think in terms of absolute and relative references

demerit

• Error if another value is entered where it should be displayed in the spiel.
• Does not work with low version of Excel

I hope this article will be of help to you.