X Y
A 14 3
B 13 5
C 15 4
D 12 2

12

As you can see, the above are in 3 columns and A is in Row B1. I have
inserted a new row directly above the total 12, now the new blank row is in
Row B6 but the formula in Row B7 is showing = B5. My question is, why can't
it be showing =B6 since the new row has been inserted in row 6. the formula
should change but it's showing showing = B5

Just wondering any excel experts can tell me the ansser

Thanks
Corinna

Corinna,
If your original formula was in B6 then for your task you should have
used:

=OFFSET(B6,-1,0)

The formula you used points to B5. Inserting a row below does not
affect B5. The moved cell still points to the original source.

HTH
Kostis Vezerides