Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
You can calculate data by making references to specific cells — for example, you can sum all the values in cells 2 through 30 in a specific column. Spreadsheets let you reference cells by their row number and column letter using several different methods. Learn how in this video.
Example Files
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
Types of References
-
Relative: identifies a cell in relation to its row or column. For example
A2means the cell in column A, row 2. Relative references update when you copy or move them. - Absolute: identifies a cell in a specific row, column or both. Use absolute references when you don't want a row reference or column reference to update when you move of copy the reference. You can specify a specific cell by providing an absolute reference to a specific row and column. This is useful if you want to use a cell in multiple calculations throughout a sheet — for example, a cell that contains a sales tax rate that's used in multiple places to calculate sales tax for multiple items.
Examples of Absolute References
-
$A$2refers to the specific cell in columnAand row2. Copying or moving this reference will always point to cellA2. -
A$2refers to the cells specifically in row2. The row number,2in this example, won't update when copying or moving this reference. However, the column reference might update. -
$A2refers to the cells specifically in columnA. The column letter,Ain this example, won't update when copying or moving this reference. However, the row reference might update.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
One of the most powerful features
of a spreadsheet is the ability to
0:00
use the value in one cell to
calculate the value for another cell.
0:04
This is called cell referencing,
where one cell references another cell.
0:09
For instance, I might hard code data
in one place on the spreadsheet.
0:15
And then in another
place use a function or
0:19
formula to reference that hard coded data.
0:21
You saw an example of that earlier,
when I showed you how to calculate sums.
0:24
In that case, one cell referenced a bunch
of other cells to calculate the sum total.
0:28
We did the same when calculating
averages and median values.
0:34
That said, there are two different ways to
reference a cell, relative and absolute.
0:38
Let's take a look at those now,
0:43
relative referencing is the most
basic type of reference.
0:46
A relative reference lets you identify
a cell in relation to its row or column.
0:49
This is really handy if you want
to use the same formula for
0:54
multiple rows of data.
0:57
Also, if you add a row
above a row with a formula,
0:59
relative references update
to match the new row order.
1:02
Okay, that may sound a little confusing,
but let me show you an example.
1:06
Here I have created a formula
that calculates the volume of
1:10
different pieces of wood.
1:13
In columns A through C, rows 6 through 19,
1:15
we have hard coded data
about pieces of wood.
1:19
In any event,
1:22
we want to calculate the volume of
the wood in column D with a formula.
1:23
Volume is calculated as height
times width times length.
1:27
In row 5, for example,
the value in column D
1:30
should be the A, B, and
C values multiplied together,
1:35
A6 times B6 times C6 equals 480.
1:40
In a spreadsheet, we refer to a cell
by its column letter and row number.
1:45
So this is cell A6, this is cell B6,
and this is cell C6.
1:51
The formula for
cell D6 should be A6 times B6 times C6.
2:00
I can both enter text or
select the cells like so, see?
2:07
I can type =A6*,
then I can click on this cell and
2:11
B6 shows up and then I'm gonna enter
in C6 myself and we get 480 inches.
2:19
Note that I can copy this
formula from cell D6 to cell
2:29
D7 by clicking on the little
square in the bottom right
2:32
of the cell called the fill handle,
holding it down and
2:38
dragging it along
the cells I wanna copy to.
2:42
Notice that the formulas in cell
2:46
D7 automatically update
to reflect the new row.
2:48
So what I've entered here
is A6 times B6 times C6.
2:52
When I copy the formula down,
2:56
automatically updates to
read A7 times B7 times C7.
2:57
That's what makes this
a relative reference.
3:03
The cell reference is
relative to the row and
3:05
can change when the formula is
applied to a different row.
3:08
So I copy this formula and paste it and
3:12
it stays relative to the row
that it's referencing.
3:16
Absolute referencing means that your
formula refers to a specific cell, row or
3:29
column and will never change as you
copy it across different cells.
3:34
This can come in handy if you want
to quickly test out different
3:38
values to see how they
affect multiple formulas.
3:41
For example, say I wanted to see how
changes to overall sales volume would
3:44
affect sales in each region.
3:48
I could have a single cell that
let me plug in different values,
3:49
like 10%, 20%, or 30%.
3:52
and see how those values affect regional
sales calculations across multiple rows.
3:54
Let's walk through a simple
example with our wood spreadsheet.
3:59
Say I wanted to know how the volumes
change if every piece of wood had the same
4:03
height as the piece of wood in row 6.
4:07
That's easy to do using absolutely referenced
cells. We can do that here in column E.
4:09
I enter the same formulas as I did in D6,
but
4:18
this time I'm gonna put a dollar
sign in front of the A.
4:20
So A6 times B6 times C6, =A6*B6*C6.
4:24
And then I'm gonna put a dollar
4:34
sign in front of the A and
in front of the 6.
4:36
And this is how you create
an absolute reference for
4:43
that particular cell, and only this cell.
4:46
The dollar sign A and dollar sign 6
mean I'm absolutely referencing A6.
4:52
Then, as I copy this formula down,
you'll see that
4:59
the volumes in each row don't
match the values in column D.
5:04
You can see that unlike D10,
E10 still has A6
5:12
in the formula,
because we put the dollar signs in.
5:19
In other words, in this cell, the first
value is an absolute reference to cell A6,
5:23
and the other two are relative
to cells B and C in row 10.
5:29
You can, and often will, use both types
of cell references in a single formula.
5:33
With absolute references
you don't always have to
5:39
reference absolutely the column and
the row.
5:42
You can do either or
in what is known as a mixed reference.
5:45
So you can absolute reference
just the row, or just the column.
5:48
And I will walk through
an example of this right now.
5:52
To illustrate this,
I'm going to show you the volumes for
5:55
each piece of wood,
calculated as a percent of total volume.
6:01
So first I'm gonna show
what is the total volume.
6:05
Sum up all the different cells here and
6:11
get to 2,780 inches,
that's the total volume.
6:13
Now, I can do this mixed
reference by going this cell,
6:19
D6, divided by the total volume and
absolute referencing it.
6:26
Then I can copy this down, and
6:36
I'm going to turn this into a percent
by using the formatting toolbar.
6:40
So, if I select all of
these then look down,
6:47
if this is correct, it should show 100%.
6:51
That is correct, but I've just
used an absolute reference again,
6:55
and I wanted to show you how
to use a mixed reference.
7:00
So what's another way I can do this?
7:04
I can do this by divided
by the sum of all of these.
7:08
And I can do the absolute reference,
but I don't necessarily need to here.
7:18
I'll do a mixed reference.
7:23
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up