ジャンル
日本語字幕付き動画
スピーチ
レクチャー
BBCで英語学習
TOEIC勉強用
TOEFL勉強用
IELTS 対策
NEW
編集部おすすめ
NEW
英語学習テクニック
映画と海外ドラマ
音楽で学ぶ英語
アニメ動画
子供向け英会話
バラエティ番組
レベル
初級: TOEIC 250-545
全て
ハウツーとスタイル
ブログ
自動車と乗り物
コメディー
エンターテイメント
ペットと動物
教育
ニュースと政治
旅行とイベント
科学と技術
テレビ番組
ゲーム
スポーツ
雅思 IELTS
映画
映画とアニメ
非営利団体と社会活動
音楽
予告編
中級: TOEIC 550-780
全て
ハウツーとスタイル
ブログ
自動車と乗り物
コメディー
エンターテイメント
ペットと動物
教育
ニュースと政治
旅行とイベント
科学と技術
テレビ番組
ゲーム
スポーツ
映画
映画とアニメ
非営利団体と社会活動
音楽
上級: TOEIC 785-990
全て
ハウツーとスタイル
ブログ
自動車と乗り物
コメディー
エンターテイメント
ペットと動物
教育
ニュースと政治
旅行とイベント
科学と技術
テレビ番組
ゲーム
スポーツ
映画
映画とアニメ
非営利団体と社会活動
音楽
予告編
スピーキングチャレンジ
コミュニティ
翻訳コミュニティ
Language
English
日本語
中文(繁體)
無料登録
Language
English
日本語
中文(繁體)
無料登録
20
、
Obama
モード
リスニング
中級
米
63
タグ追加
保存
Tweet
動画の字幕をクリックしてすぐ単語の意味を調べられます！
リピート
速度：
遅め
標準
ブログ用リンク
連続再生
全画面
英
英語字幕をプリント
単語帳を選択
単語帳読み込み中…
単語帳を選択
英検3級-5級単語
(
)
英検2級-準2級単語
(
)
英検1級-準1級単語
(
)
高校単語7000
(
)
TOEIC単語
(
)
動画で検索した単語
今まで検索した単語
保存した全ての単語
お気に入りフレーズ
字幕の修正報告
00:00:00,000 --> 00:00:04,740 Welcome to Excel Basics Number 1.
This is the first video in a series
of Excel Basic Videos 1 to 25.
Now, in this first video, we're going
to talk about the Excel Grid, Formatting, Formulas, Cell
References, and Page Setup.
Now, this is a continuation of my Office 2016 video series.
This is video number 13 in that series.
Now, here's our list of topics.
And there are a lot of amazing topics.
Even just this one first video will give you
a lot of basic skills to accomplish tasks in Excel.
Now, this is our goal.
We're actually going to create a grade sheet--
names, assignments, numbers, and then formulas for average,
total, and even percentage grade.
All right.
I'm going to close this.
And here's our system of folders.
And of course, now that we're on to Excel,
we're going to be saving all of our files to 04 Excel.
Now let's open up a blank Excel workbook.
I'm going to click on the green x.
Now, just as in Word and PowerPoint,
the blank workbook is highlighted.
I can either click with my mouse,
hit Enter, or use the Escape key.
And here's our blank workbook.
The very first thing we do is we look up to the title bar.
Book 1 is not a good name, so we use our keyboard for save as--
F12.
Now, we're going to navigate to our folder.
And there it is--
04 Excel.
We're going to click down in the file name and we're going
to call this EB for Excel Basics 01 dash--
and this is going to be a Gradebook.
There's our name.
The file extension by default in Excel is dot xlsx,
and that's fine for us.
I'm either going to click Save or hit Enter
to enact that Save button.
Now, I want to zoom in, but, of course, just
like int Word and PowerPoint, instead of using our Zoom
bar down on the status bar, I want to hold Control and roll
my wheel.
Now, the first thing we need to talk about
is the structure of Excel.
Notice up at the top, I hover my cursor--
DEF.
If I click on the E, that's a column.
Columns are represented by letters.
If I go over to the rows--
2, 3, 4 5, and click on 6, numbers represent the rows.
Now, the reason that's so important
is because the intersection of a column and a row
is called a cell.
Now, the name of that cell is E6.
We can actually see the name of this cell by looking up.
This is called the formula bar.
And all the way on the left in the formula bar,
if you hover your cursor, is the name box.
We can see that that cell is named E6.
So the column is the letter.
The row is the number.
And I can click in any cell--
click there, and I know it's G6.
Now, the reason that knowing letters are columns,
numbers are rows, is because later, we'll
have to refer to our cell in formulas.
And knowing that E is the column and 6
is the row will be very helpful.
So if that's a cell and these are all cells,
then all the cells together make up what's called a Worksheet.
Now, we call them sheet for short.
Now, notice the little plus right there.
Sometimes we need new sheets.
We might need one for January, February, March.
You can simply click the plus, and there's
a new sheet inserted.
Now I can click back between the sheets.
I'm going to click on sheet 1.
Now, these are called Sheet Tabs.
Click back on Sheet 1.
Sheet 1 is not a good name for our sheet tab.
So to rename our sheet in the sheet tab,
we simply double click.
Now we can name this--
I'm going to name it GradebookFall17.
Now, if I come up to the ribbons and try to use something,
everything is grayed out.
That's because-- just like over in Windows Explorer--
we have to hit Enter to register that name.
So I hit Enter.
Now column, row, cell, sheet.
That's called a Worksheet tab.
All of the Worksheets together make up
what's called a Workbook.
And a Workbook has a name.
The name of our Workbook is EB01 Gradebook.
So this is called a Workbook file.
Now, the next thing we want to talk about
before we start entering data and numbers and formulas
is just something very basic.
Hey, this cursor right here--
that white thick cursor with a black shadow--
it's called the selection cursor, and here is why.
If I click in the middle of a cell and hold the click
and drag--
oh, look at that.
I can select or highlight cells.
So this is the selection cursor.
Now I'm going to select cell A1.
There's two other cursors we want
to learn about in this video.
If I hover right at the edge, that's called a move cursor.
Now, most of the time, the move cursor will get us in trouble.
But we need to know the difference
between selection, move, and--
right in the lower right-hand corner, that little green box--
that's called a Fill Handle.
If you move your selection cursor or move cursor
right over the Fill Handle, you'll see a crosshair.
Now, I like to call it--
instead of a crosshair, I like to call it an Angry Rabbit.
Now, we'll learn a lot of amazing tricks that we can do--
not with our selection, not with our move,
but with our Angry Rabbit cursor.
Those three cursors, we'll see in this video.
Hey, let's use our selection cursor.
Oh, we already have cell A1 selected.
I'm going to type something.
I'm going to type Data.
Now, to put something in the cell--
whether it's text, number, a formula--
you have a number of different options.
If you want to put the thing in the cell
and move the cursor down, you use the Enter key.
If, on the other hand--
I'm going to select the cell.
And remember, just like Word, if we have a word selected
and I want to replace it, I do not need to hit the Delete key.
I simply start typing.
I'm going to type Name.
Now we have something in the cell,
and we do not want to hit Enter to put it in and move
our cursor down.
Because we want to enter data across the columns,
I want to put the thing in the cell
and move my cursor to the right by hitting the Tab key.
Now, we're going to fill this out later with student names,
but we need quiz 1, quiz 2, quiz 3, and then test 1,
test 2, test 3.
So in cell B1, I'm going to type quiz space 1.
Now, instead of using Enter to put the thing in the cell
and go down or tab to go to the right,
I actually want to put the thing in the cell
and keep the cell selected.
So to do that, we use Control Enter.
Now, if you remember back to Word,
we used Control Enter a lot for page break.
But we're going to use Control Enter even more over here
in Excel because a lot of times we
want to put something in the cell and keep the cell
selected.
Now the reason we want the cell selected is we
might want to add formatting.
Or in our case, we want to copy it.
Now, remember, that little thing on the lower right hand corner
is called a Fill Handle.
And if you move your cursor over the Fill Handle,
that's the Angry Rabbit.
Now click-- that's a left click--
and drag.
Notice it's giving me a green box covering C1, D1.
Now let go.
Look at that.
Excel has so many magic tricks for our Angry Rabbit.
Anytime you have text and a number,
if you use your Angry Rabbit to copy it,
it will increment the numbers.
Now let's do the same thing over here.
I'm going to click in cell E1 with my selection cursor.
Test space 1.
My goal is to put the thing in the cell
and keep the cell selected, so I use Control Enter.
Now, hover my cursor over the Fill Handle--
not the move cursor, not the selection cursor.
It's the Angry Rabbit.
Click and drag all the way to G1.
Let go and look at that.
Now, I've got to come down below here
and show you a couple of other amazing tricks for that Angry
Rabbit.
I'm going to click in cell A12 and type J-A-N--
that's short for January--
Control Enter to put the thing in the cell
and keep the cell selected, point to the fill handle.
And when you see your Angry Rabbit, click and drag.
Now, that is amazing.
I'm building my calendars over here in Excel,
definitely not in a Word table.
It gets better than that.
If you put any date into a cell--
10 slash 10 slash 2017--
that's a date.
I'm going to use Control Enter to put the thing in the cell
and keep the cell selected.
Now I'm in the hover my cursor.
And when I see my Angry Rabbit, I'm
going to click and drag down.
And look at that.
That is a quick way to get a bunch of dates--
for example, if you're building a schedule.
That is amazing.
The Angry Rabbit does many more tricks.
That's just a few of them.
Now let's continue with our Gradebook up here.
I'm going to use my selection cursor and select H1.
I'm going to type Total tab--
to put the thing in the cell and move to the right--
percentage grade, and now I'm going to hit Enter.
Now, we're going to have names here of students.
But I want a row at the top to tell me
the maximum score for each quiz and for each test.
And then I want to add them all up
so I know the maximum points possible in the class.
So right below Name, I'm going to type Max Tab.
Now, quizzes are worth 20 points each.
So 20 Control Enter-- because I want
to put the thing in the cell and keep the cell selected.
And let's see what happens if I try to copy this using my Angry
Rabbit--
1, 1, and let go.
Oh.
It's going to give me just the number 20, which happens to be
exactly what we want.
Up here, that trick worked because there
was text and a number.
Down here it worked because internally, Excel
is programmed to create lists of months and increment dates
by day.
But if you use your Angry Rabbit on just a plain number
or a plain text item with no number, then it just copies.
That's exactly what we wanted there.
Man, that Angry Rabbit does a lot of amazing tricks.
I'm going to type 100, Control Enter,
and I'm going to use my Angry Rabbit to click and drag.
So each one of the tests will be worth 100 points.
Now, down here, we'll enter in the student
scores for each assignment.
But before we do that, in the name column,
I need to write each student's name.
The first student--
Sioux, Enter because I want to put the thing in the cell
and move my cursor down.
I'm entering data vertically into a column.
The next student name--
Abdi.
Enter.
Then Chin.
Enter.
Tyrone.
Enter.
SheliaDawn.
Enter.
Mo.
Enter.
Gigi.
Oops.
Backspace, backspace.
Gigi.
Enter.
Now, that's the end of the student names.
But in the very bottom row, I'm going
to eventually create a formula to calculate the average.
So I actually need to put the word average here and Tab.
Now, I want to notice something about the text
or words that we entered and the numbers that we entered.
In Excel, the default alignment for text
is always going to be to the left.
The default alignment for numbers
is always going to be to the right.
Now, later in the class, we'll see the importance
of that default behavior.
Now, you could totally highlight, and in the Alignment
group, start changing the alignment.
But that's rarely a good idea unless you're
building the very final report for printing.
The fact that numbers are aligned to the right,
text is aligned to the left, will help us track down
errors later in this class.
All right.
Now, the next thing we need to do
is I actually want to add some style formatting to our grade
table.
Style formatting is-- for example, up in the Font group--
and by the way, this alignment has
nothing to do with paragraph-level formatting
like we learned in Word.
Font has nothing to do with character-level formatting
like we learned in Word.
Everything in Excel is cell formatting.
I click on a cell, or I highlight a bunch of cells,
and I add formatting to cells.
Now, the first thing I'd like to do
is notice at the top of each column, we have name, quiz 1,
quiz 2.
That tells us what goes in this column.
And I want to add some formatting that's
different for the first row with our column headers
than the rest of the table.
So with my selection cursor--
not the Angry Rabbit.
Selection cursor.
Click and drag.
We've selected all of our column headers.
Now I'm going to come up in the Font group,
and there's a dropdown that pours paint into our cells.
It's actually called Fill Color.
I'm going to select red.
Now I'm going to go to the Font.
And notice it has the letter A, so we
know that this is going to add color
to the text or the numbers.
Click the dropdown and select what you want.
Now, I'm going to keep it automatic here, and just--
I want to remind you something we talked about in Word.
If I print this out right now, it's
going to be hard to read the words with a red fill.
Now, the trick is-- and we learned this over in Word--
is if you squint your eyes and you cannot really see
the difference between the text and the fill,
then you know you need to increase the value difference.
Now, if I was going to use red, I would highlight all of this
and use font color white.
Now, I'm going to, with this highlighted,
come up and select that dark blue there.
Now, I'm also going to highlight with my selection cursor
the second row, because these numbers are not
going to change.
We're going to change all the numbers in here for scores.
But these I want a different color also.
That will tell us this is the max row.
So instead of going up to the fill bucket--
here's a great trick.
We saw this in Word also.
I can right click.
And look at that.
That's the mini toolbar.
And in Excel, it has a few different things.
I'm going to click the dropdown and select yellow.
Now, I would like to select the entire table.
And notice I'm using my selection cursor
to select the whole table.
Now, my goal is to add borders.
But I've got to show you a great trick.
If you have a table--
and notice, our table is defined by a bunch of column headers
until there's nothing, and then a bunch of data
until there's nothing.
If you have a table like that, or a region in your spreadsheet
like that, and you want to select everything,
there is a keyboard for that.
Now, I'm going to use the asterisk key on my number pad
and use Control Asterisk.
If you don't have the number pad,
then you have to use 8 on the regular number pad.
So you have to use Control Shift 8.
That is an awesome keyboard.
Imagine if we had 100 students, or 360 rows
for what's called an amortization table in finance.
The ability to select the whole table quickly with a keyboard
is awesome.
Now we can come up to the Font group
and click our drop down for all borders.
Now, the next thing we want to do
is we want to enter all of the student's grades.
Now, we could simply enter like this.
And I'm just going to do this quick--
3 Enter 3 Enter.
I'm just entering dummy data.
But notice when I get to the bottom,
then I have to take my cursor and come back up to the top
and start entering my data.
So when we have a rectangular range like this to fill in,
if we select it in advance, then--
watch this-- just hit your Enter key.
When it gets down to the bottom, it knows to jump to the top.
So I'm going to hit Enter Enter Enter.
And when I am at the bottom, Enter jumps to the top.
So this is going to be a entering data trick.
All right.
Are you ready?
I'm going to enter the scores for the first column.
Sioux got 15, Enter.
Abdi got 19, Enter.
Chin, 16.
Tyrone, 20.
SheliaDawn 19.
Mo, 19.
Gigi, 11.
Now when I hit Enter, it jumps to the top.
All right, I'm just going to enter the data,
and you can enter the data into your own sheet,
always hitting Enter.
And then the last score for quiz 3 for Gigi is 19.
When I hit Enter, it jumps up to test 1.
All right.
So we'll enter test 1 scores.
81, Enter.
92, Enter.
I'm going to enter all the test scores using Enter.
00:19:41,430 --> 00:19:44,770 And so when I enter the last test score,
I'm in the furthest cell away from our upper left hand
corner.
So when I hit Enter, it jumps back to the top.
So that is a great trick if you're entering data
into a rectangular range.
Now, our next goal is to create a formula that will add.
Now, I'm going to click in cell H2.
And our goal is to add all of these.
That means we get to create our very first formula.
Now, the very first formal, we're actually
going to use a Built-in Function called SUM.
If we go to Home and over to Editing--
I'm going to show you this only once because later,
we'll use a keyboard.
Up in Editing, there's the Auto Sum.
Now, hover your cursor over Auto Sum.
And notice that it says some, and then,
in parentheses, Alt Equals--
that's the keyboard.
Now, this first time, all I'm going to do
is click the Auto Sum.
Later, we'll use the keyboard.
And this is our first formula.
All formulas start with an equal sign
as the first character in the cell.
After the equal sign, you can put
all sorts of different things.
And throughout this class, we'll learn
lots of different formulas.
This particular formula uses the SUM function--
S-U-M. That's just a built-in function
that is programmed to understand that in this range of cells,
it's going to take all those numbers and add them.
Then, we have open parentheses for built-in functions.
And functions like SUM, AVERAGE, MAX to find the biggest,
PMT to calculate a loan payment.
But all functions will have open parentheses.
And then notice, it says number 1, number 2.
We're only using the first argument
because we have just one range.
But the function will always have this screen tip,
where it lists the different items that can
go into the built-in function.
For us, it's a range of cells.
Now, it says, B2--
that's the name of that cell right there--
and then colon, and then it has the name of that cell
right there, G2.
So it really means add up all of the cells
from that cell all the way to this last cell.
And then it has a close parentheses.
Now, I'm going to hit enter, and there it is.
Automatically, it calculates 360.
Now, I want to show you why we made this calculation in Excel
rather than used a handheld calculator.
There's our total 360.
But guess what.
We can change any one of these formula inputs--
because remember, if I put this cell in Edit mode
using the F2 key, that formula is looking
at that entire range of cells.
I'm going to click escape, but I can
change any one of these formula inputs,
and instantly, the formula result will change.
So for example, quiz 1, it's not worth 20.
It was worth 30 points.
Now, I'm simply going to select the cell,
type 30, and before I hit Enter, watch over here.
Now I'm going to hit Enter, and instantly it optics updates.
Test 1, it actually was worth 50 points.
So I type 5 0.
And when I hit Enter--
Enter-- the formula instantly updates.
That is really the beauty of Excel.
We can build a formula once.
Any time our formula inputs change, the formula updates.
Now, actually, the guys who invented the spreadsheet--
Bricklin and Frankston-- they created the first spreadsheet
called VisiCalc all the way back in 1980.
And they called it a visual calculator.
That way, I can simply type anything visually
into a input cell, and our calculation, when I hit Enter,
updates.
Now I'm going to change quiz 1 back to 20 and Enter.
Now I want to create the rest of our formulas
to add each student's total.
I'm going to click in cell H3.
Now, we're going to put the SUM function into cell H3
and then copy it down.
But this time, we're going to use the keyboard
to put the SUM function in.
And the keyboard is Alt equals.
Now, I put the equal sign--
in all formulas start with the equal sign--
SUM function, open and close parentheses, and look at that.
It put the range in.
Now, any time you have cells--
either a single cell-- we'll see that later--
or a range of cells in a formula--
we call those Cell References because the formula
is referring to the cells to go and get the data.
Not only that, but when you use the keyboard for the sum
function, notice that Excel guessed
what the range of numbers were.
Now, it usually guesses right, but you have to verify.
Once in a while, when we use the SUM function,
it might not grab the right number of cells.
So you always want to--
Alt equals-- verify that the range is correct,
and then you can enter your formula.
Now, our goal is to enter the formula,
and then keep this cell selected, and copy it down.
So I'm going to use Control Enter.
Now, I'm going to point to the Fill Handle.
And when I see my Angry Rabbit, I'm going to click and drag.
And just like that, that is so much faster
than a handheld calculator.
Now, I want to make sure when I copy
a formula to go to the last cell and use F2 to put
your formula in Edit mode.
And we're verifying that the formula is
looking at the correct cells.
Now I'm going to hit Enter.
All right?
So we have calculated the total for each student.
Now we need to come over here and we
need to calculate the average for quiz 1, quiz 2, 3, and so
on.
Now we're going to click in cell B10.
And if we're going to calculate the average,
we need to add everything up and divide by the count.
Now, luckily, like the SUM function for adding,
when we're calculating an average,
we can use the built-in function AVERAGE.
Now, there's no keyboard for the AVERAGE.
And in fact, there's 450 functions in Excel,
and the SUM function is the only one with a keyboard.
All right.
So that means we need to know how to type out our formula.
Well, if we're doing a formula in a cell,
the very first character in the cell has to be an equal sign.
As soon as we type an equal sign as the first character
in a cell, it tells Excel, this is a formula.
Now, just as they named SUM function SUM,
they gave the AVERAGE calculation
a smart name for the built-in function.
It's called AVERAGE.
Now, I want to type A-V after the equal sign.
And look at that.
There's a drop down with a list of possible functions that
start with A-V. Now, that little icon f of x--
that's the icon for built-in function.
Now, if we type E-R because we know the name of the function,
then, as soon as we see highlighted in blue the AVERAGE
function-- the function that we want--
I use the Tab key to enter that function in,
get the parentheses, and show our screen tip.
Now, I'm going to click Escape because I
want to do that again and show you something else.
Escape will always revert back to whatever was in the cell
before we put it in Edit mode.
So when I click Escape, because there was nothing when we
started, now there's nothing.
Now let's try that again.
Equals A-V.
Now, if you don't know the exact name of the function
and you're trying to guess, you can actually
go through each one of these functions
and read the screen tip.
Now, this says Returns the average
of the absolute deviations.
No, no, no.
That's for statistics.
So I'm going to use my down arrow
to go to the next function in the list.
It's highlighted blue, so a screen tip comes up--
returns the average arithmetic mean.
That's exactly what we want.
Now, if we were to down arrow and read each one,
the last one average ifs reads, finds average--
arithmetic mean--
for the cells specified by a given set
of conditions or criteria.
Now, later in the class, we'll use that function.
Now I'm going to up arrow.
Average is the one I want.
So I'm going to hit Tab.
Whenever you see your function highlighted in blue,
you use tab.
Now, just like the SUM function, it has number 1, number 2,
and we can put either single cells or ranges of cells.
Now, notice, when we open this up,
it didn't automatically highlight the cells
like the SUM function.
No problem.
Our selection cursor works perfectly.
I can click and drag.
Now, one thing about dragging is-- oh, I made a mistake.
Just don't let go of your click, and then it
doesn't matter where you are.
You can keep moving it until you get the correct range.
What you don't want to do is do not highlight the same cell
that the formula is in.
You do that, you will get a circular reference error.
So the dancing ants--
as long as you keep that click, they'll keep dancing.
And when you see the correct range, let go of the click.
Now, there we go.
Close parentheses.
We have the correct range.
Our goal is to put the formula in the cell
and copy it-- so I Control Enter, point
my cursor to the Fill Handle.
When I see my Angry Rabbit, I'm click and drag.
Now I'm going to drag it only over to test 3.
And just like that, it's calculated our average.
Now, remember, any time you copy a formula,
you have to go to the last cell and hit
F2 to verify that the range of cells or whatever formula
inputs are the correct ones.
And for us, those are correct.
Now I'm going to hit Enter.
Now, I'm going to come back over to cell B10
and select cell B10.
Now, notice, the formula shows us the formula result.
So the average for quiz number 1 is 17 points.
But any time you have a cell selected,
you can also look up in the formula bar,
and you'll be able to see the formula you created.
So simultaneously, we can see formula result and the formula
up in the formula bar.
Now, another important thing we need
to understand about Excel formulas is notice,
we enter that formula and copied it over.
Any time we copy a formula, we have
to think about the cell references carefully.
So I'm going to select B10 and use the F2 key to put
this cell in edit mode.
Now, notice, if we just read this formula exactly
left to right--
well, there is an equal sign.
All formulas start with an equal sign
as the first character in the cell.
We are using a built in function the function we're using is
average.
That function will add them up, divide by the count.
And then there's the open and close parentheses,
which all functions will have.
And then there is a range of cells.
Now, that range of cells can be read B3 to B9.
And for this one formula in cell B10, that's correct.
But because we're copying the formula,
we do not want to think of B3 to B9 as B3 to B9.
Really, what is it?
That range of cells is always going
to look at the seven numbers directly above the cell that
has the formula.
Now, if it really was B3 to B9 when we copied it, watch.
Tab F2.
If it really was B3 to B9, it would still say, B3 to B9.
But no.
Look at that.
That is simply amazing.
It totally moved from the B3 to B9 to C3
to C9, which is great, because then we
don't have to recreate our formula each time.
Now, I'm going to hit Tab and F2 a few times.
And I want you to watch how the blue range moves
as we copy our formula.
Tab F2.
Now that's the D column.
Tab F2.
That's E3 to E9.
Tab F2.
F3 to F9.
Now, there's a special name for this type of cell reference.
When we copy our formula and the cell references move,
we call those Relative Cell References.
That means from the formula's point of view--
relative to the formula, where am I going to look?
I'm always going to look seven cells above.
Tab F2.
So when I read this formula, I read
it equal sign, built-in function,
average, relative cell reference.
That's always going to look seven above.
All right.
Relative Cell References.
I'm going to hit Enter.
Now, that's going to become important,
because we're going to create our percentage grade formula.
And we're going to have to put relative cell references
and absolute cell references.
Now, before we learn about that new cell reference called
Absolute, let's just see if we can make a calculation here
that for Sioux will calculate her percentage grade.
Now, for every student in this column,
the calculation is going to be the same.
I'm taking the total points that they earned
and I'm going to compare it to the total points for the class.
Now, the way we do that is we take student's total score
divided by the total possible.
Now, I'm going to start with Sioux.
And this is the first time we're making a formula that doesn't
use a built-in function.
Of course, equal sign starts all formulas.
When you type that equal sign as the first character
in the cell, you're telling Excel, I want to do a formula.
Now, just as we did with the Average function,
we're allowed to take our cursor and click anywhere.
Now, that's not correct.
That's not correct.
That's not correct.
But the same thing holds, as we saw with our average function.
As long as the dancing ants are moving,
you're in full edit mode.
I definitely want Sioux's total.
If I mistakenly took her test 3 score,
man, she would not be happy.
Total-- now, that's a Relative Cell Reference.
As I copy this formula down, it totally will move,
which is what we want, of course, because as the formula
copies down, we need the next total for Abdi, then Chin,
Tyrone, and so on.
Now we need to divide so we're going to use the division
symbol forward slash.
There's a forward slash next to the Shift key.
But oftentimes, it's easier to use the number pad for our math
symbols.
There is a division symbol on the number pad.
So I'm going to divide it by.
And now, just as we did with the individual student's score,
I'm allowed to select the maximum total for the class.
Now, when I hit Enter, we're going to get a decimal.
And any time you compare two numbers using division,
where the numerator--
the top number-- can either be equal to 360 or the max,
whatever it is, or less, you're going to get
a number between 1 and 0.
So this means if we were to convert it to a percentage,
it would mean that this person got about 80%
of the total points in the class.
Now, we'll officially convert it to percentage number format
later.
But now, I want to come down and do this for Abdi.
By the way, look at that.
[INAUDIBLE] Abdi's name.
F2 to put it in edit mode, and backspace.
B-D-I tab.
Abdi.
All right.
Let's do the formula for Abdi.
Equal sign, and I can use my mouse to select a cell--
or range of cells--
divided by-- and I can use my mouse
to select the maximum total.
If I hit Enter, that's the correct decimal.
So Abdi got about 86%.
Now, let's try the next formula for Chin.
But instead of using our mouse to click and get
cell references, I'm going to click in I5.
Equal sign starts all formulas.
But now I want to use the arrow keys on my navigation pad.
I'm going to use left arrow divided by on the number pad,
and then up, up, up, arrow, and left arrow.
Oftentimes, if the cell references
you're trying to put into your formula
are close to the cell with the formula,
then your arrow keys can be faster than using your mouse.
Enter, equal sign, left arrow, division, up,
up, up, left, Enter.
Now, we do not want to do each one of these individually.
So we're going to highlight those formulas
we've just created and we're going to use the Delete key.
Now let's try this again.
Ready?
I'm clicking in cell I3.
Equal sign, left arrow, division, sign, up arrow,
left arrow.
Now I want to Control Enter and try and copy this formula.
Control Enter.
And then I'm going to click and drag.
Now, definitely, Tyrone is super happy.
He just broke the mold.
He just broke the rule.
Remember, if these points can only be 360 or less,
somehow, he got more than one.
Now, if we go to the cell and hit F2,
you can see the reason why.
These are both Relative Cell References.
That blue one is always looking one to the left.
And the orange one is always looking one
to the left and one up.
I'm going to click Escape.
Let's go to the top cell.
F2.
That one works.
But because these are both Relative Cell
References, Enter F2, that one's not going to work.
Now, I'm going to hit Enter, F2 Notice, Enter F2, those two
cells are moving the whole way down when we copy our formula.
And that's not what we want.
Escape.
Highlight these.
Delete.
Now, let's try this again.
Equals, left arrow, that's a Relative Cell Reference.
As I copy my formula down, I definitely
want the formula to see the blue cell to the left.
Now, division symbol, up arrow, left arrow.
We know that that's not going to work.
But now, there's a secret key on your keyboard
that will lock this.
So as we copy down, it will not move.
That H2 will always be looking at H2.
And the secret key is the F4 key.
I'm going to hit the F4 key.
Now, those dollar signs were arbitrary.
Back when the very first spreadsheet
was created, the guys who created it,
they just said, hey, we got to put some symbol there
to indicate that now this is locked.
When we copy the formula down, that orange cell will not move.
All right?
So you ready?
Control Enter.
And now Angry Rabbit.
Click and drag.
Always, when we copy a formula, we go to the last cell in here.
F2.
That is amazing.
We have one Relative Cell Reference and one Absolute Cell
Reference.
I often refer to this as a Locked Cell Reference
because it's locked on H2, wherever we copy this.
And think about that.
That's pretty amazing.
That means not only can we create an average formula,
where we had Relative Cell References,
and when we copied them, we wanted them to all be relative,
but now we have the ability to have a cell reference
that's not locked-- or Relatives Cell Reference--
and a cell reference that's locked--
or Absolute.
I'm going to hit Enter.
Now, before we add number formatting
to make these appear as percentages,
we've got to talk about--
in the Home ribbon tab Number group--
we've got to talk about what is Number Formatting?
Now, before we format these, I'm just
going to come over to cell K3, type January sales, Enter.
And January sales were $5,622.50.
Control Enter.
And now I'm going to format this so it shows up as a currency.
So no problem.
This is called Number Formatting.
That's different than our Style Formatting.
Number Formatting will only affect our numbers.
Now, there's a bunch of built-in options here.
Also, we have a dropdown.
[AUDIO OUT] I'm going to click on Currency.
There's also Accounting.
We'll talk about both of those in a later video.
But let's just select Currency.
And the first thing I want to notice
is Number Formatting already saved us a bunch of clicks.
We didn't have to type a dollar sign.
We didn't have to type a comma separator.
And we didn't have to type the zero.
That's three clicks that we saved.
So Number Formatting will help us reduce the number of clicks
when we're entering numbers.
Now, we have to look at this Displayed Number
and compare it to what's up in the formula bar.
Look at that.
There's no dollar sign, comma, or zero.
If we look at the actual cell where
the Number Formatting is applied,
this is only what is displayed.
This is what we see on the surface of the spreadsheet.
If we look up here, we can clearly
see that only 1, 2, 3, 4, 5, 6 characters
are actually in the cell.
Over here, we have 1, 2, 3, 4, 5, 6, 7, 8, 9 characters
displayed.
So many times, when you use Number Formatting,
there will be a disconnect between what
we see on the surface of the spreadsheet
and what's actually in the cell.
Now, here, it's not going to really cause any problem.
Is just awesome that we saved some clicks.
But let's apply some more Number Formatting Up
in the Number Group--
wow, look at that.
The decreased decimal.
I'm going to click it once and twice.