Skip to main content
  • Login
Cart
Exceljet logo Exceljet
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
Cart
  • Login
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles
  • Login

You are here

Home › Videos › What's a relative reference?

What's a relative reference?

Transcript 

What's a relative reference? A relative cell reference points to an address that is relative to the location of the reference. When a formula is copied to a new location, relative references are automatically updated. Relative cell references are standard in Excel. When you want to perform similar calculations in different cells, they are exactly what you want and will save you a lot of time entering and maintaining formulas.

Let's take a look.

In this worksheet we have two highlighted cell references.  Let's create a formula that adds them together. We can enter =B9 + D6

We get a result of 35, as expected. And any change to cell B9 or D6 is reflected immediately.

The cell references in this formula are relative references. What this means is that Excel evaluates the reference B9 as the cell two rows up, and two rows to the left.

And Excel evaluates the address D6 as the cell five rows up.

Let's copy our formula to a different cell to see relative references in action.

At the new location, we get a result of zero. Let's check the formula to see why that is. We see that Excel has updated the relative references. The formula is still adding the cell two rows up and two rows to the left, to the cell five rows up. Both cells are empty, so we get a result of zero.

Let's copy and paste a few more times.

In each case, we see that Excel has updated the references in the formula to point to the same *relative* locations in the worksheet.

What will happen if we paste the formula into cell L5, where there is no cell five rows above?

In that case, we'll get a REFERENCE error. If we check the formula, we can see that the first reference is still valid, but the second reference is not.

Excel will show a REF error whenever it can't locate a cell reference.

Master Excel with practice. Download this worksheet (and dozens more) in our Core Excel course. Learn by doing.

Dave Bruns Profile Picture

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Microsoft Most Valuable Professional Award  

Exceljet Training

  • Excel 101
  • Excel Fundamentals
  • Conditional Formatting
  • Essential Formulas
  • Advanced Formulas
  • Pivot Tables
  • Excel Tables
  • Excel Shortcuts
  • Excel Charts
  • Course Bundles
Your site has been my go-to Excel site for years. I share it with my MBA students and anyone who asks me about Excel.
JD
More Testimonials

Get Training

Quick, clean, and to the point training

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas
Exceljet logo Exceljet
Quick, clean, and to the point

Resources

  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Articles

About Us

  • About
  • Testimonials
  • Topics
  • Contact
  • Donate

Newsletter Sign-up

Work faster in Excel. Join more than 100,000 people who get weekly tips from us.
© 2012-2025 Exceljet. Terms of use
  • Visit our Twitter page