How to Insert Rows in Excel Without Breaking Formulas

When a row is inserted into a table that contains a function, the function is often corrupted, and we will show you how to repair it.

If you have Microsoft365 and Excel 2021 or later versions, there is a way to use the spill function to create a table without collapsing formulas. For more information.Insert rows without breaking formulas in spiel Excel (Excel).

Example of formula breakdown

For example, suppose you have the deposits and withdrawals data shown above. Suppose that after creating this table, you notice that the data for January 3, 2024 is missing.

Normally, these tables can be added down and down, so there should be no need to insert rows, but if you insert rows without thinking

As you can see, there is no function in the inserted row, and the row below skips the inserted row and refers to the value in the row one above. If we enter the data for January 3, 2024 in this state, and enter the function in the balance column only, we get

Thus, on January 4, 2024, the 100,000 yen deposited on the previous day is counted as if it had never been received. Such a large difference in numbers would be disconcerting, but a small amount of money would seem to be fine at first glance.

Other factors, such as deleting rows, rearranging, and so on, can also cause misalignments, and this is one example of a formula that can be misaligned.

If you are the creator of the product, you can use it with care to avoid such a situation, but if you want other people to use it, you will inevitably be exposed to such risks.
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..

Fix a collapsed function

Select cell "E8" with the correct formula and drag the lower right part of the cell down with the mouse.

At this point, the key is not to stop at "E9" where the line is inserted and the function is blank, but to copy it to "E10".

The line insertion is now correct.

If you do not know where it is collapsing

If we look again at the previous example of a broken formula, this time we can tell something is wrong at a quick glance because of the exaggerated number in line 9, but in most cases it is difficult to notice when it is a small number.

In such cases, it is quicker to copy the top formula to the bottom.

In this example, only the first row has no balance reference, so the formula is different, so in this example, select cell "E4". If you move the mouse cursor to the lower right corner of that cell, the mouse cursor will change to a "+" symbol.

If you double-click in this state

It automatically copies down to the last line containing the same function.

The formula has now been corrected.