1
00:00:00,590 --> 00:00:04,470
One of the most powerful features
of a spreadsheet is the ability to

2
00:00:04,470 --> 00:00:09,340
use the value in one cell to
calculate the value for another cell.

3
00:00:09,340 --> 00:00:15,210
This is called cell referencing,
where one cell references another cell.

4
00:00:15,210 --> 00:00:19,560
For instance, I might hard code data
in one place on the spreadsheet.

5
00:00:19,560 --> 00:00:21,910
And then in another
place use a function or

6
00:00:21,910 --> 00:00:24,576
formula to reference that hard coded data.

7
00:00:24,576 --> 00:00:28,843
You saw an example of that earlier,
when I showed you how to calculate sums.

8
00:00:28,843 --> 00:00:34,264
In that case, one cell referenced a bunch
of other cells to calculate the sum total.

9
00:00:34,264 --> 00:00:38,030
We did the same when calculating
averages and median values.

10
00:00:38,030 --> 00:00:43,220
That said, there are two different ways to
reference a cell, relative and absolute.

11
00:00:43,220 --> 00:00:45,040
Let's take a look at those now,

12
00:00:46,270 --> 00:00:49,840
relative referencing is the most
basic type of reference.

13
00:00:49,840 --> 00:00:54,200
A relative reference lets you identify
a cell in relation to its row or column.

14
00:00:54,200 --> 00:00:57,450
This is really handy if you want
to use the same formula for

15
00:00:57,450 --> 00:00:59,150
multiple rows of data.

16
00:00:59,150 --> 00:01:02,730
Also, if you add a row
above a row with a formula,

17
00:01:02,730 --> 00:01:06,260
relative references update
to match the new row order.

18
00:01:06,260 --> 00:01:10,410
Okay, that may sound a little confusing,
but let me show you an example.

19
00:01:10,410 --> 00:01:13,560
Here I have created a formula
that calculates the volume of

20
00:01:13,560 --> 00:01:14,560
different pieces of wood.

21
00:01:15,710 --> 00:01:19,340
In columns A through C, rows 6 through 19,

22
00:01:19,340 --> 00:01:22,860
we have hard coded data
about pieces of wood.

23
00:01:22,860 --> 00:01:23,450
In any event,

24
00:01:23,450 --> 00:01:27,040
we want to calculate the volume of
the wood in column D with a formula.

25
00:01:27,040 --> 00:01:30,578
Volume is calculated as height
times width times length.

26
00:01:30,578 --> 00:01:35,120
In row 5, for example,
the value in column D

27
00:01:35,120 --> 00:01:39,470
should be the A, B, and
C values multiplied together,

28
00:01:40,620 --> 00:01:45,390
A6 times B6 times C6 equals 480.

29
00:01:45,390 --> 00:01:51,740
In a spreadsheet, we refer to a cell
by its column letter and row number.

30
00:01:51,740 --> 00:02:00,510
So this is cell A6, this is cell B6,
and this is cell C6.

31
00:02:00,510 --> 00:02:07,388
The formula for
cell D6 should be A6 times B6 times C6.

32
00:02:07,388 --> 00:02:11,762
I can both enter text or
select the cells like so, see?

33
00:02:11,762 --> 00:02:19,750
I can type =A6*,
then I can click on this cell and

34
00:02:19,750 --> 00:02:29,000
B6 shows up and then I'm gonna enter
in C6 myself and we get 480 inches.

35
00:02:29,000 --> 00:02:32,500
Note that I can copy this
formula from cell D6 to cell

36
00:02:32,500 --> 00:02:38,000
D7 by clicking on the little
square in the bottom right

37
00:02:38,000 --> 00:02:42,070
of the cell called the fill handle,
holding it down and

38
00:02:42,070 --> 00:02:46,360
dragging it along
the cells I wanna copy to.

39
00:02:46,360 --> 00:02:48,780
Notice that the formulas in cell

40
00:02:48,780 --> 00:02:52,330
D7 automatically update
to reflect the new row.

41
00:02:52,330 --> 00:02:56,180
So what I've entered here
is A6 times B6 times C6.

42
00:02:56,180 --> 00:02:57,740
When I copy the formula down,

43
00:02:57,740 --> 00:03:03,450
automatically updates to
read A7 times B7 times C7.

44
00:03:03,450 --> 00:03:05,660
That's what makes this
a relative reference.

45
00:03:05,660 --> 00:03:08,010
The cell reference is
relative to the row and

46
00:03:08,010 --> 00:03:11,110
can change when the formula is
applied to a different row.

47
00:03:12,720 --> 00:03:16,378
So I copy this formula and paste it and

48
00:03:16,378 --> 00:03:21,689
it stays relative to the row
that it's referencing.

49
00:03:29,938 --> 00:03:34,263
Absolute referencing means that your
formula refers to a specific cell, row or

50
00:03:34,263 --> 00:03:38,255
column and will never change as you
copy it across different cells.

51
00:03:38,255 --> 00:03:41,105
This can come in handy if you want
to quickly test out different

52
00:03:41,105 --> 00:03:44,125
values to see how they
affect multiple formulas.

53
00:03:44,125 --> 00:03:48,020
For example, say I wanted to see how
changes to overall sales volume would

54
00:03:48,020 --> 00:03:49,800
affect sales in each region.

55
00:03:49,800 --> 00:03:52,750
I could have a single cell that
let me plug in different values,

56
00:03:52,750 --> 00:03:54,723
like 10%, 20%, or 30%.

57
00:03:54,723 --> 00:03:59,720
and see how those values affect regional
sales calculations across multiple rows.

58
00:03:59,720 --> 00:04:03,020
Let's walk through a simple
example with our wood spreadsheet.

59
00:04:03,020 --> 00:04:07,110
Say I wanted to know how the volumes
change if every piece of wood had the same

60
00:04:07,110 --> 00:04:09,216
height as the piece of wood in row 6.

61
00:04:09,216 --> 00:04:14,730
That's easy to do using absolutely referenced
cells. We can do that here in column E.

62
00:04:18,020 --> 00:04:20,958
I enter the same formulas as I did in D6,
but

63
00:04:20,958 --> 00:04:24,620
this time I'm gonna put a dollar
sign in front of the A.

64
00:04:24,620 --> 00:04:34,000
So A6 times B6 times C6, =A6*B6*C6.

65
00:04:34,000 --> 00:04:36,260
And then I'm gonna put a dollar

66
00:04:36,260 --> 00:04:41,655
sign in front of the A and
in front of the 6.

67
00:04:43,250 --> 00:04:46,540
And this is how you create
an absolute reference for

68
00:04:46,540 --> 00:04:49,800
that particular cell, and only this cell.

69
00:04:52,870 --> 00:04:59,540
The dollar sign A and dollar sign 6
mean I'm absolutely referencing A6.

70
00:04:59,540 --> 00:05:04,302
Then, as I copy this formula down,
you'll see that

71
00:05:04,302 --> 00:05:09,727
the volumes in each row don't
match the values in column D.

72
00:05:12,181 --> 00:05:19,000
You can see that unlike D10,
E10 still has A6

73
00:05:19,000 --> 00:05:23,930
in the formula,
because we put the dollar signs in.

74
00:05:23,930 --> 00:05:29,780
In other words, in this cell, the first
value is an absolute reference to cell A6,

75
00:05:29,780 --> 00:05:33,580
and the other two are relative
to cells B and C in row 10.

76
00:05:33,580 --> 00:05:39,060
You can, and often will, use both types
of cell references in a single formula.

77
00:05:39,060 --> 00:05:42,050
With absolute references
you don't always have to

78
00:05:42,050 --> 00:05:45,230
reference absolutely the column and
the row.

79
00:05:45,230 --> 00:05:48,410
You can do either or
in what is known as a mixed reference.

80
00:05:48,410 --> 00:05:52,990
So you can absolute reference
just the row, or just the column.

81
00:05:52,990 --> 00:05:55,110
And I will walk through
an example of this right now.

82
00:05:55,110 --> 00:06:01,050
To illustrate this,
I'm going to show you the volumes for

83
00:06:01,050 --> 00:06:04,020
each piece of wood,
calculated as a percent of total volume.

84
00:06:05,200 --> 00:06:08,210
So first I'm gonna show
what is the total volume.

85
00:06:11,300 --> 00:06:13,770
Sum up all the different cells here and

86
00:06:13,770 --> 00:06:18,090
get to 2,780 inches,
that's the total volume.

87
00:06:19,180 --> 00:06:26,840
Now, I can do this mixed
reference by going this cell,

88
00:06:26,840 --> 00:06:31,530
D6, divided by the total volume and
absolute referencing it.

89
00:06:36,130 --> 00:06:40,810
Then I can copy this down, and

90
00:06:40,810 --> 00:06:44,520
I'm going to turn this into a percent
by using the formatting toolbar.

91
00:06:47,520 --> 00:06:51,376
So, if I select all of
these then look down,

92
00:06:51,376 --> 00:06:55,457
if this is correct, it should show 100%.

93
00:06:55,457 --> 00:07:00,283
That is correct, but I've just
used an absolute reference again,

94
00:07:00,283 --> 00:07:04,018
and I wanted to show you how
to use a mixed reference.

95
00:07:04,018 --> 00:07:08,050
So what's another way I can do this?

96
00:07:08,050 --> 00:07:14,394
I can do this by divided
by the sum of all of these.

97
00:07:18,672 --> 00:07:23,200
And I can do the absolute reference,
but I don't necessarily need to here.

98
00:07:23,200 --> 00:07:24,382
I'll do a mixed reference.