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 › How to use relative references - example 1

How to use relative references - example 1

Transcript 

Now that we've looked at the basics of relative references in formulas, let's look at a common use case for relative references—calculating a total price from a unit price and quantity across a number of line items.

Let's take a look.

In this worksheet, we have a list of materials to build a dog house in column B, and the cost of each item in column D. We also have the quantity needed for each item in column C.

Let's create a formula that calculates the total cost for each item, taking into account the quantity. This is a perfect application for relative cell references.

To start off, we'll create our first formula in cell E6. We want to multiply the quantity times the unit cost, so our formula is = C6 * D6.

Note that C6 and D6 are relative references which is Excel's default behavior.

For our first item, we get a total of $25.00 since 2 x 12.50 = 25. If we change the quantity, we see the total recalculate as expected.

To fill in the rest of the formulas, we could just enter each formula manually. But that's a lot of busy work and it isn't necessary. Instead, let's take advantage of relative references and just copy and paste the formula down the rows. Control-C to copy; Control-V to paste.

If we check the copied formulas, we see that they have been updated as needed. Each formula still refers to column C and D as before, but the row numbers were automatically updated when the formulas were pasted.

Note that we could have used the Fill handle to copy the formulas as well. If we undo the paste using Control-Z, we can drag the Fill handle to copy the formulas. Or, we could just double-click the Fill handle. Either way, the result is the same.

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
Thank you, this is an excellent site to get an understanding of all the Excel formulas. Especially for brushing up or just rechecking the formulas.
Priti
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