I have a Google Spreadsheet with one column and two rows:

cell A1: 2
cell A2: =A1 (displays as "2" because of the reference to A1)

If I insert a row between these two rows (by right-clicking on row 2, and selecting "insert 1 row above"), a blank row is inserted such that I now have no value in cell A2. But in cell A3, I still have the value "2", and the formula of cell A3 is still "=A1".

Is there a way to make the formula in A3 point to the value of the newly inserted row ("=A2"), similar to the default behavior in Excel? I'm finding it extremely tedious to have to go and adjust all the references each time I insert a row somewhere in the middle of my data.

2 Answers 2


=OFFSET(A2,-1,) should also work.


After some poking around, I found what appears to be the answer:

INDIRECT("A" + ROW()-1) 

yields the value of the row above, and maintains this even if I insert a row. This is the behavior I was looking for.

  • You can mark your own answer as Accepted. Commented Sep 15, 2014 at 12:12

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.