Create a Durable Relative Cell Reference

A2 = A1

That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this:

B2 = B1

That's all fine and good, but if you then insert a new row above row 2, you'll find:

A3 = A1
B3 = B1

This is intentional.  Now your cell formula is referring to a cell "up 2" from itself, rather than "up 1"  I call this non-durable.  The relative relationship can be affected by inserting and deleting rows.

I often find non-durable relative references to be problematic when I have a formula that references the cell above it, and I want it to stay that way.  As I insert rows into my data (or delete them!) I want each row to refer to the row above it, period.

A2 = OFFSET(A2,-1,0)

This relative formula is durable.  It refers to the row above it, and it will ALWAYS refer to the row above it, no matter how many rows you insert or delete.

- Brian Withun

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.