Create a Durable Relative Cell Reference

Published on
7,757 Points
5 Endorsements
Last Modified:
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
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free