1
0:00:00,50 --> 0:00:01,30
- [Instructor] Whenever you're trying

2
0:00:01,30 --> 0:00:02,60
to add up numbers in a column

3
0:00:02,60 --> 0:00:03,60
or a row,

4
0:00:03,60 --> 0:00:04,80
or come up with an average

5
0:00:04,80 --> 0:00:05,90
of those numbers,

6
0:00:05,90 --> 0:00:07,30
you rely on two of Excels

7
0:00:07,30 --> 0:00:09,00
most commonly used functions;

8
0:00:09,00 --> 0:00:10,90
sum and average.

9
0:00:10,90 --> 0:00:12,40
Now there's a menu button,

10
0:00:12,40 --> 0:00:13,70
there's a keystroke shortcut,

11
0:00:13,70 --> 0:00:15,00
and also some different

12
0:00:15,00 --> 0:00:16,10
selection techniques that

13
0:00:16,10 --> 0:00:17,30
are gonna make these functions

14
0:00:17,30 --> 0:00:18,80
easier to use.

15
0:00:18,80 --> 0:00:19,80
We're looking at the worksheet

16
0:00:19,80 --> 0:00:21,30
Sum, average in the workbook

17
0:00:21,30 --> 0:00:24,70
O3, creating formulas and functions.

18
0:00:24,70 --> 0:00:25,60
First, as a reminder,

19
0:00:25,60 --> 0:00:27,50
on the formulas tab in

20
0:00:27,50 --> 0:00:29,50
the ribbon, in the menu system,

21
0:00:29,50 --> 0:00:31,90
you will see ultimately

22
0:00:31,90 --> 0:00:33,30
all Excel functions.

23
0:00:33,30 --> 0:00:35,00
There are over 450 of them.

24
0:00:35,00 --> 0:00:37,00
And no body needs to know them all,

25
0:00:37,00 --> 0:00:38,20
that's for sure.

26
0:00:38,20 --> 0:00:39,10
If you're looking for a

27
0:00:39,10 --> 0:00:40,40
particular function,

28
0:00:40,40 --> 0:00:42,20
you're not always sure where to begin,

29
0:00:42,20 --> 0:00:43,40
is it math and trig?

30
0:00:43,40 --> 0:00:45,00
Well maybe, maybe not.

31
0:00:45,00 --> 0:00:46,40
Slide over a function,

32
0:00:46,40 --> 0:00:47,60
you'll see a description.

33
0:00:47,60 --> 0:00:49,10
Nobody quite approaches

34
0:00:49,10 --> 0:00:51,70
looking for functions in that way.

35
0:00:51,70 --> 0:00:52,70
Most of the time we do it

36
0:00:52,70 --> 0:00:53,80
based on context.

37
0:00:53,80 --> 0:00:55,10
We want to add up data

38
0:00:55,10 --> 0:00:57,50
right here in cell G2,

39
0:00:57,50 --> 0:01:00,00
on this worksheet called Sum, average.

40
0:01:00,00 --> 0:01:00,90
We're in the workbook

41
0:01:00,90 --> 0:01:03,30
O3, creating formulas and functions.

42
0:01:03,30 --> 0:01:04,70
We can type in,

43
0:01:04,70 --> 0:01:05,50
and you might've seen

44
0:01:05,50 --> 0:01:06,60
this in an earlier movie,

45
0:01:06,60 --> 0:01:08,10
equal S U M.

46
0:01:08,10 --> 0:01:09,50
But on the left side of

47
0:01:09,50 --> 0:01:10,50
the formulas tab,

48
0:01:10,50 --> 0:01:12,40
here's a button called Autosum.

49
0:01:12,40 --> 0:01:13,80
I'm going to click it.

50
0:01:13,80 --> 0:01:14,80
And we see what happens,

51
0:01:14,80 --> 0:01:15,80
Excel is about to add

52
0:01:15,80 --> 0:01:16,60
up that data,

53
0:01:16,60 --> 0:01:17,40
that looks good,

54
0:01:17,40 --> 0:01:18,60
we'll press enter.

55
0:01:18,60 --> 0:01:19,90
On the home tab as well,

56
0:01:19,90 --> 0:01:21,20
a tab we often have active.

57
0:01:21,20 --> 0:01:22,60
On the right side of the home tab

58
0:01:22,60 --> 0:01:24,60
is Autosum as well.

59
0:01:24,60 --> 0:01:26,00
Now after using that for a bit

60
0:01:26,00 --> 0:01:27,80
and recognizing how quickly it works,

61
0:01:27,80 --> 0:01:29,80
let's delete this cell.

62
0:01:29,80 --> 0:01:30,60
Let's do it again,

63
0:01:30,60 --> 0:01:33,50
this time let's click Autosum twice.

64
0:01:33,50 --> 0:01:34,40
Now again you can use it on

65
0:01:34,40 --> 0:01:35,90
the home tab or on the

66
0:01:35,90 --> 0:01:36,90
formulas tab.

67
0:01:36,90 --> 0:01:38,80
Click, click, there it is.

68
0:01:38,80 --> 0:01:40,10
Becomes a little bit faster.

69
0:01:40,10 --> 0:01:41,40
And there's a keystroke shortcut.

70
0:01:41,40 --> 0:01:42,30
Alt, equal.

71
0:01:42,30 --> 0:01:44,20
Sometimes that'll be handy.

72
0:01:44,20 --> 0:01:45,70
The Autosum button is designed

73
0:01:45,70 --> 0:01:47,10
to add up data from the left

74
0:01:47,10 --> 0:01:48,20
or from above.

75
0:01:48,20 --> 0:01:49,90
So I've got a situation here,

76
0:01:49,90 --> 0:01:51,80
I wanna add up these numbers here.

77
0:01:51,80 --> 0:01:53,50
I'll click that Autosum button.

78
0:01:53,50 --> 0:01:56,00
Looks good, click it again, all set.

79
0:01:56,00 --> 0:01:57,50
Remember, could've double clicked it.

80
0:01:57,50 --> 0:01:59,20
Here's another thought, unnecessarily better,

81
0:01:59,20 --> 0:02:01,30
if you select the data ahead of time,

82
0:02:01,30 --> 0:02:03,60
then you can click Autosum once.

83
0:02:03,60 --> 0:02:05,20
We've got a total.

84
0:02:05,20 --> 0:02:06,00
Remember you can use it

85
0:02:06,00 --> 0:02:07,20
in either of its locations

86
0:02:07,20 --> 0:02:08,50
on the right side of the

87
0:02:08,50 --> 0:02:10,20
home tab or the left side

88
0:02:10,20 --> 0:02:12,50
of the formulas tab, same button.

89
0:02:12,50 --> 0:02:13,40
Now, in regards to the

90
0:02:13,40 --> 0:02:14,30
keystroke shortcut,

91
0:02:14,30 --> 0:02:15,90
sometimes that's going to be better.

92
0:02:15,90 --> 0:02:17,00
We've got this data highlighted.

93
0:02:17,00 --> 0:02:18,90
Now granted you're moving

94
0:02:18,90 --> 0:02:20,00
your hand from the mouse to

95
0:02:20,00 --> 0:02:21,70
the keyboard, but it's Alt, equal.

96
0:02:21,70 --> 0:02:24,70
A keyboard shortcut for Autosum.

97
0:02:24,70 --> 0:02:26,40
Here's a rare situation.

98
0:02:26,40 --> 0:02:27,90
You've got data above

99
0:02:27,90 --> 0:02:30,90
the active cell and to the left,

100
0:02:30,90 --> 0:02:32,40
I'm in cell H7.

101
0:02:32,40 --> 0:02:33,70
So what happens here if

102
0:02:33,70 --> 0:02:34,80
we try Autosum?

103
0:02:34,80 --> 0:02:36,90
Using either the keystroke shortcut

104
0:02:36,90 --> 0:02:40,40
or the button, Excel looks upward first.

105
0:02:40,40 --> 0:02:41,40
Now if you say I wanna add

106
0:02:41,40 --> 0:02:42,90
up the cells to the left,

107
0:02:42,90 --> 0:02:44,50
what do you do with the mouse?

108
0:02:44,50 --> 0:02:46,10
Highlight these three cells.

109
0:02:46,10 --> 0:02:47,30
Other words, you redirect it

110
0:02:47,30 --> 0:02:48,80
then press enter.

111
0:02:48,80 --> 0:02:50,20
And this list down below,

112
0:02:50,20 --> 0:02:51,90
if you wanted totals just

113
0:02:51,90 --> 0:02:52,70
on the right side,

114
0:02:52,70 --> 0:02:53,90
highlight these cells,

115
0:02:53,90 --> 0:02:55,40
use either technique,

116
0:02:55,40 --> 0:02:57,70
Alt, equal or the Autosum button.

117
0:02:57,70 --> 0:02:59,10
I'm pressing Alt, equal.

118
0:02:59,10 --> 0:03:00,10
That works fine.

119
0:03:00,10 --> 0:03:01,30
And similarly we can do the

120
0:03:01,30 --> 0:03:02,70
same thing across the bottom.

121
0:03:02,70 --> 0:03:04,00
Alt, equal.

122
0:03:04,00 --> 0:03:05,40
Or that button.

123
0:03:05,40 --> 0:03:06,60
Now I'm going to undo

124
0:03:06,60 --> 0:03:07,60
my last two actions

125
0:03:07,60 --> 0:03:10,60
and highlight the cells this way.

126
0:03:10,60 --> 0:03:11,50
We can put totals on the

127
0:03:11,50 --> 0:03:12,60
right and the bottom.

128
0:03:12,60 --> 0:03:13,60
Either with the button

129
0:03:13,60 --> 0:03:15,00
or the keystroke shortcut.

130
0:03:15,00 --> 0:03:15,90
So here's Autosum.

131
0:03:15,90 --> 0:03:16,90
Click it once,

132
0:03:16,90 --> 0:03:17,80
there we are.

133
0:03:17,80 --> 0:03:19,30
We've got our totals.

134
0:03:19,30 --> 0:03:21,00
And there's another variation on this.

135
0:03:21,00 --> 0:03:21,90
Sometimes you'd rather

136
0:03:21,90 --> 0:03:23,10
do an average.

137
0:03:23,10 --> 0:03:24,60
So, once again I'll press

138
0:03:24,60 --> 0:03:26,60
Control Z to undo the last action.

139
0:03:26,60 --> 0:03:27,90
If you'd like to get an

140
0:03:27,90 --> 0:03:29,00
average of these both

141
0:03:29,00 --> 0:03:30,30
on the right and on the bottom,

142
0:03:30,30 --> 0:03:32,70
Autosum, in both of its locations,

143
0:03:32,70 --> 0:03:34,00
is accompanied by a tiny

144
0:03:34,00 --> 0:03:35,10
little drop arrow.

145
0:03:35,10 --> 0:03:36,00
Click it,

146
0:03:36,00 --> 0:03:37,70
and there's the choice called average.

147
0:03:37,70 --> 0:03:39,10
Let's do an average.

148
0:03:39,10 --> 0:03:40,60
Later we'll adjust the formatting,

149
0:03:40,60 --> 0:03:42,80
but we click on this cell,

150
0:03:42,80 --> 0:03:43,90
I'll double click right here

151
0:03:43,90 --> 0:03:45,00
to see it larger.

152
0:03:45,00 --> 0:03:46,10
And we get an average.

153
0:03:46,10 --> 0:03:47,50
Press escape, same thing

154
0:03:47,50 --> 0:03:48,50
up here is I'll double click

155
0:03:48,50 --> 0:03:50,50
and across the bottom.

156
0:03:50,50 --> 0:03:52,60
So that's gonna be handy too.

157
0:03:52,60 --> 0:03:53,90
So although you can use

158
0:03:53,90 --> 0:03:55,20
the drop arrow to get the average,

159
0:03:55,20 --> 0:03:56,30
sometimes it will be a bit

160
0:03:56,30 --> 0:03:58,10
faster simply to type it.

161
0:03:58,10 --> 0:03:59,40
I'm gonna do an average here

162
0:03:59,40 --> 0:04:00,90
in cell B9.

163
0:04:00,90 --> 0:04:01,80
Equal average.

164
0:04:01,80 --> 0:04:03,40
Another shortcut is your typing.

165
0:04:03,40 --> 0:04:05,20
As soon as you see the function

166
0:04:05,20 --> 0:04:06,00
in the list here,

167
0:04:06,00 --> 0:04:07,00
now we're not seeing it yet,

168
0:04:07,00 --> 0:04:08,60
so as I put in V, there it is,

169
0:04:08,60 --> 0:04:09,90
I could double click,

170
0:04:09,90 --> 0:04:12,70
or tab, average pops into place.

171
0:04:12,70 --> 0:04:13,80
Then we'll highlight the data

172
0:04:13,80 --> 0:04:15,60
and enter, you don't have

173
0:04:15,60 --> 0:04:16,70
to put in the right parenthesis

174
0:04:16,70 --> 0:04:17,90
when you're only using

175
0:04:17,90 --> 0:04:19,40
the function by itself.

176
0:04:19,40 --> 0:04:20,60
So both of those functions

177
0:04:20,60 --> 0:04:22,30
are widely used as one of many

178
0:04:22,30 --> 0:04:23,60
many functions in Excel.

179
0:04:23,60 --> 0:04:25,90
And if you have extra time

180
0:04:25,90 --> 0:04:27,50
you can and you will discover

181
0:04:27,50 --> 0:04:29,30
over time, as you work with data,

182
0:04:29,30 --> 0:04:30,90
there are functions for calculating

183
0:04:30,90 --> 0:04:33,70
median, counting cells that have data,

184
0:04:33,70 --> 0:04:34,80
counting number cells,

185
0:04:34,80 --> 0:04:36,90
come up with the second largest.

186
0:04:36,90 --> 0:04:38,40
So lots of different functions

187
0:04:38,40 --> 0:04:43,40
related to working with values in Excel.