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 2

How to use relative references - example 2

Transcript 

In this lesson we'll take a look at another common use case for relative references—creating row and column totals in a table. These kinds of totals often use formulas that are identical except for the cell references.

Let's take a look.

Here we have a table that shows the number of widgets sold over a 12-month period. There are six different kinds of widgets, so the table has six rows of data. What we want to do is show the total number of widgets sold each month in row 13, and the total number of each widget type sold for all 12 months in column O.

First, let's add a total for each month on row 13. This is a job for the SUM function. To get the total number of widgets sold in January, we add the formula =SUM(C7:C12)

Our formula contains a standard relative reference, so we can just copy this formula across all 12 months using the Fill handle. If we spot check a few formulas, we see that the reference has been updated as expected in each column.

Now let's do the same thing to get a 12-month total for each widget type. Our first formula is =SUM(C7:N7) and gives us 55 for Widget A.

Again, this is a relative reference telling Excel to sum values in the 12 cells to the left, so we can just copy our formula all the way down the table.

The relative reference used by our formula has now been updated in each row.

To summarize, relative references are relative to the location of the formula they reside in. If the formula is copied to a new location, each relative reference is updated as needed to point to the same relative location.

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
Just wanted to say thanks for the awesome work you produce. Really handy formulae for all those tricky situations.
Pravin
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