Google Sheets – Row Insert Reference Updating Behavior

google sheets

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.

Best Answer

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