Create a Durable Relative Cell Reference

Published on
7,631 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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month