[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Create a Durable Relative Cell Reference

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

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Join & Write a Comment

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month