Cell Referencing

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

Types of References

Relative: identifies a cell in relation to its row or column. For example A2 means 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$2 refers to the specific cell in column A and row 2. Copying or moving this reference will always point to cell A2.

A$2 refers to the cells specifically in row 2. The row number, 2 in this example, won't update when copying or moving this reference. However, the column reference might update.

$A2 refers to the cells specifically in column A. The column letter, A in this example, won't update when copying or moving this reference. However, the row reference might update.

0:00

One of the most powerful features
of a spreadsheet is the ability to

0:04

use the value in one cell to
calculate the value for another cell.

0:09

This is called cell referencing,
where one cell references another cell.

0:15

For instance, I might hard code data
in one place on the spreadsheet.

0:19

And then in another
place use a function or

0:21

formula to reference that hard coded data.

0:24

You saw an example of that earlier,
when I showed you how to calculate sums.

0:28

In that case, one cell referenced a bunch
of other cells to calculate the sum total.

0:34

We did the same when calculating
averages and median values.

0:38

That said, there are two different ways to
reference a cell, relative and absolute.

0:43

Let's take a look at those now,

0:46

relative referencing is the most
basic type of reference.

0:49

A relative reference lets you identify
a cell in relation to its row or column.

0:54

This is really handy if you want
to use the same formula for

0:57

multiple rows of data.

0:59

Also, if you add a row
above a row with a formula,

1:02

relative references update
to match the new row order.

1:06

Okay, that may sound a little confusing,
but let me show you an example.

1:10

Here I have created a formula
that calculates the volume of

1:13

different pieces of wood.

1:15

In columns A through C, rows 6 through 19,

1:19

we have hard coded data
about pieces of wood.

1:22

In any event,

1:23

we want to calculate the volume of
the wood in column D with a formula.

1:27

Volume is calculated as height
times width times length.

1:30

In row 5, for example,
the value in column D

1:35

should be the A, B, and
C values multiplied together,

1:40

A6 times B6 times C6 equals 480.

1:45

In a spreadsheet, we refer to a cell
by its column letter and row number.

1:51

So this is cell A6, this is cell B6,
and this is cell C6.

2:00

The formula for
cell D6 should be A6 times B6 times C6.

2:07

I can both enter text or
select the cells like so, see?

2:11

I can type =A6*,
then I can click on this cell and

2:16

B6 shows up and then I'm gonna enter
in C6 myself and we get 480 inches.

2:24

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:35

of the cell called the fill handle,
holding it down and

2:41

dragging it along
the cells I wanna copy to.

2:46

Notice that the formulas in cell

2:48

D7 automatically update
to reflect the new row.

2:52

So what I've entered here
is A6 times B6 times C6.

2:56

When I copy the formula down,

2:57

automatically updates to
read A7 times B7 times C7.

3:03

That's what makes this
a relative reference.

3:05

The cell reference is
relative to the row and

3:08

can change when the formula is
applied to a different row.

3:12

So I copy this formula and paste it and

3:16

it stays relative to the row
that it's referencing.

3:29

Absolute referencing means that your
formula refers to a specific cell, row or

3:34

column and will never change as you
copy it across different cells.

3:38

This can come in handy if you want
to quickly test out different

3:41

values to see how they
affect multiple formulas.

3:44

For example, say I wanted to see how
changes to overall sales volume would

3:48

affect sales in each region.

3:49

I could have a single cell that let me
plug in different values like 10%, 20% or

3:54

30%.

3:54

And see how those values affect regional
sales calculations across multiple rows.

3:59

Let's walk through a simple
example with our wood spreadsheet.

4:03

Say I wanted to know how the volumes
change if every piece of wood had the same

4:07

height as the piece of wood in row 6.

4:09

That's easy to do using absolute reference
cells, we can do that here in column E.