Say I have two a table like this:

   Name    B    C    D
1  Joe     15  17  =B1-C1
2  Mike    25  22 (auto fill)

And I need to insert a column between C and D like this:

   Name    B    C    D      E
1  Joe     15  17   31    =C1-D1
2  Mike    25  22   34   (auto fill)

And I want the last column change to C1-D1. Every time I need to fill in myself. Is it possible to just say:

My value = My 2nd left cell - My left cell

1 Answer 1


Use offset, with the base point being the same cell in which you enter the formula. So, in D1 it would be

=offset(D1, 0, -2) - offset(D1, 0, -1)

When a new column is inserted between C and D, the above becomes

=offset(E1, 0, -2) - offset(E1, 0, -1)

which still has the meaning "My 2nd left cell - My left cell".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.