How to avoid #ref error while deleting the rows in Excel?

Excel will display a notice known as a #REF error (the "ref" stands for reference) if a formula makes reference to a cell that does not now exist. This is typically the result of removing cells that a calculation is referring to. A competent financial analyst will be familiar with locating and correcting #REF problems in Excel, which are discussed in greater detail below.

Avoid #ref Error while Deleting the Rows in Excel

When you refer a cell to another cell, the cell that you are referring to will display the #REF error if the row that the cell is referencing has been deleted. For an example, now that I have your attention, I will explain how to avoid the "#ref error" and automatically refer to the following cell while deleting a row.

Let’s understand step by step with an example.

Step 1

Let’s assume we are referring the cell value A7 into the cell B2 by writing the formula in cell B2 as =A7, then the cell value in the B2 is reflected as 10. Refer to the below screenshot.

Step 2

Now, if we delete the row A7, then the reference cell, i.e., cell B2 will show the reference error as #REF!. Below are the screenshots given for the same.

You cannot use the generic formula =cell reference since doing so would result in the #REF error; instead, you will need to use a different formula.

Step 3

Choose a cell that is adjacent to the one you need to refer a row to using a formula, and then type the row number difference that exists between the cell in question and the reference cell. For example, I want to make the reference A7 in B2 and then type 5 into C2. Check out this screenshot below.

Step 4

Then, in the cell that is labelled B2, input the formula, and then hit the Enter key. Please refer to the below screenshot for same.

Formula

=OFFSET(A2,C2,)

Step 5

Now, as you delete row 7, the content of the cells in that row will be changed, and they will obtain the value from the new row 7. As shown in below screenshots for the same.

Conclusion

In this tutorial, we explained how you can avoid #ref error while deleting the rows in Excel.