Deleting rows breaks formulas

Question

I am certain that in the past I didn't have this "problem." Let's say I have 3 columns: A, B, and C. I have a lot of rows of numbers for these columns. Columns A and B have numerical data manually entered in their cells. Each cell in column C is a SUM of the cell to the left and the cell above. For example, C15 would have "=SUM(B15,C14)".

What I EXPECT is that when I right-click a row and delete it, such as 15, that the data from rows 16 and beyond would shift up and the "new" C15 cell would maintain the formula of "=SUM(B15,C14)". However, instead, all C cells 15 and below break. C15 looks like this: "=SUM(B15,#REF!)".

Maybe I am losing my mind, but I would bet a year's pay that in the past, deleting a row that had a formula above and below it would not break all formulas below the row I deleted.

I didn't use OFFSET or anything like that. I just remember Excel being smart. I've looked all around the interwebs, including SO, and all the answers to similar questions seem to imply I'm losing my mind.

If someone can help out, that'd be awesome. Thanks for your time.

Answer 1

Excel has never worked like this. I expect it to as well and have run into this problem many times. The only solution I have been able to apply is the OFFSET as you suggested. Offset becomes relative only to itself. If you delete a row, it will still use the same offset reference. Clearly, this can bring up a whole new set of problems. Sample compares the values of two cells. One just to the left to the cell above it. If they are same, then an X is placed in the cell. If you delete any rows (perceived duplicates) then the formula adjusts and does not #REF. =IF(OFFSET(B2,0,-1) = OFFSET(B2,-1,-1), "X", "") where B2 is the cell the formula is entered in.

Answer 2

Rather than deleting the row, instead try this "Stomp the Row" pattern. This will not work in all instances, but works well for situations where each row refers only to the row above it or refers absolutely for refs elsewhere.

1. Copy all of the rows below the row you wish to delete, including one blank row from below your block of non-blank rows. (Don't cut)
2. Then select the row you want deleted and
3. Paste the rows.

The blank row from the bottom prevents a duplication of the final row and the top row stomps on top of the row you had targeted for deletion. Optionally, and in case of confusion, undo and redo repeatedly while scrolling to various spots to check that you did it right.

You can use the same technique to delete multiple rows as well, but make sure to include enough blank rows from the bottom to prevent leaving behind duplicates at the bottom. A surplus of blanks at the bottom doesn't hurt, so I usually include extra.

Hope that helps. =]