Excel – How to add/remove a row without affecting formula in a column in Excel

microsoft excel

I have column B with a formula full of IFs with each cell referenced to the cell above it.
From time to time, I need to delete some rows. Once a row is deleted, the formula in column B gives #REF for the remaining cells beneath the deleted row.
How can I delete a row and the formula is automatically updated by referencing the new cell above it?
Also, when adding a row, can the formula automatically take place in cell B without me adding it each time?

Best Answer

Depends on your situation and layout really. Depending on that, there are 2 options.

Commented out You could use absolute references which I think is what you want, such as


The $ means it sticks to that cell, what ever the value!

However, depending on what your deleting, you may find the use of Indirect useful

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.


enter image description here

More information and quote and image source

Related Question