How to use relative references - example 2

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.