Insert rows without breaking formulas (using spil) Excel (Excel)

With Microsoft365 and Excel 2021 or later versions, you can insert, delete, and rearrange rows without breaking the formula by using a function called Spill. In this issue, we will introduce a specific example of this function.

For versions prior to Microsoft365 and Excel 2021How to Insert Rows in Excel Without Breaking Formulas.

Entering table formulas with the spill function

We have prepared a table as shown below.

The amount column in column "E" will contain the result of the unit price in column "C" x the exchange rate in cell "D2" x the quantity in column "D". Using the Spill function, enter the following function in the "E5" cell.

=C5:C10*D2*D5:D10

Then, as shown in the figure below, a single expression displays values in all rows.

Try inserting rows in this state.

If you insert a line

With rows 9-10 selected

Enter the line insertion shortcut "Ctrl" + "+" to insert two lines.

Enter the appropriate numerical values in the "B-D" columns of this inserted row, in the cells indicated by the blue boxes in the figure below.

The results of the calculation were then automatically displayed in the cell indicated by the red box.

As a test, let's check the formula in cell "E5" where the calculation formula is entered.

You can see that the range specification extends properly to the bottom row, including the cell in which the row was inserted.

Conclusion

If a table formula is inserted using a spill, the formula will not be corrupted if another operator inserts, deletes, or rearranges rows in the middle of the table without permission.

However, this is a method that can only be used with Microsoft365 and Excel 2021 or later versions.

For versions prior to Microsoft365 and Excel 2021How to Insert Rows in Excel Without Breaking Formulas.

Also, while the person who created the product can use it with care to avoid such a situation, he or she will inevitably be exposed to such risks if he or she tries to get others to use it as well.
In such cases, it is useful to use a "protection function" that restricts reordering, line insertion, and line addition.
For more information on specific methods, please contactHow to use the sheet protection function..

I hope this article will be of help to you.