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.
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.
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.
And look at that.
Now, the Displayed Number is totally
different than the actual number that
sits underneath in the cells.
So that means you have to be careful
when you use Number Formatting.
And we'll see lots of examples later in the class.
And we'll learn how to be perfectly careful when
we use Number Formatting.
Now, one thing you want to avoid saying is-- remember,
we use decreased decimals--
there's the actual number.
0.5 is still in the cell.
So you don't want to say this rounded the number,
because it didn't.
All it did was display the number differently
than the underlined number.
Now, we're going to be careful here and increase
our decimals-- click, click--
and get back to our properly displayed currency.
Now, Number Formatting can do all sorts of amazing things.
Let's highlight all these numbers, and up in the number
group, I'm not going to click that button.
If you click that button, it will display percentage Number
Format with zero decimals.
If that's what you want, you can use that button.
I'm going to come up and use the dropdown and select Percentage.
You can already see a preview.
It will show two decimals.
I'm going to click that.
And there they are.
Now, we want the ability--
if you want more decimals, you can use these buttons.
And later, we'll learn about the Format Cells dialog box.
But just be aware, once we applied that Percentage
Number Formatting, those unrounded decimals
are still underneath in the cell.
Now, for us, since we created those decimals with formulas,
we cannot look up in the formula bar to prove that there's some
rounded, large set of decimals there.
This just displays it that way.
If, in fact, you have a formula and you really
want to see what's in the cell, you
have to reverse the Number Formatting.
And the way you do that is you go up to the Number Group.
And we click the dropdown.
And the General Number Formatting
is the Eraser that will erase any Number Formatting that we
may have applied to the cell.
So when I click general, even though there's a formula there,
we can see all the decimals.
Now I'm going to Control Z to undo
that because I want to leave these as formatted percentages.
Now, there's two more things we're
going to learn in this video.
The next thing is when I print this,
I would like to print all of this,
not any of this other stuff off to the side.
Now, what many people do in Excel is they use Control P.
And that opens up the Print dialog box and Print Preview.
Now, lots of times, people don't bother to look over here.
They just click the Print button.
They go to the printer, and then it's
printed across a bunch of pages.
It's not what they wanted.
So Control P, always look at the preview.
This definitely is not what we want.
And not only do I want to get rid of all that stuff,
but if you look down here, there's
actually stuff on page 2.
And if I click forward, there's that little extra bit
off to the side.
Now, if we want to get out of Backstage view here--
or print--
we click escape.
Now we're going to talk about Page Setup.
Now, we did a lot of Page Setup in Word.
And in Word, there was a different ribbon tab.
Over here in Excel, it's called Page Layout.
I'm going to click on Page Layout.
But look at this.
Just like over in Word, there's a group called Page Setup.
Now, the buttons and the things we
can do in Excel for Page Setup are much
different than over in Word.
But Page Setup group--
there's our dialogue launcher.
Now, just as we learned over in Word,
if you use a particular feature a lot,
then you want to teach yourself the Alt keyboards
to open up a particular feature.
Now, if we hit the Alt key, notice
that it gives us a single letter in a screen tip for each one
of the ribbon tabs.
So if I want to open up Page Layout,
I have to click P. I'm going to tap P. Now
I see that the dialog launcher has SP,
so in order to open that up, SP.
Now I'm going to click Escape because we all
saw this a bunch of times earlier in the class.
The same keyboard in Word works here in Excel.
When we want to get to Page Setup Dialog Box
to have complete control over how this sheet prints,
we use the keyboard Alt PSP.
Now, there's four tabs--
Page, Margin, Header and Footer, and Sheet.
We're going to start with Page.
I definitely want this to print out landscape.
Now, the way you can decide is usually
if it's wider than it is tall, then landscape will look good.
If the page is too big and you want to fit it,
then we can do one page wide by one page tall.
Now, this wasn't over in Word.
But over here, we can definitely use it.
That's not what we want here, though.
I want to increase the percentage.
Now, I'm not exactly sure which percentage I need here,
so I'm just going to try 125.
If it's not exactly what I want, I'll
come back and fix it later.
Now, I'm going to go over to margins.
And just like in Word, we can set the margins for top,
right, bottom, left.
We also set how far from the edge of the piece of paper
the Header or Footer will be printed.
Now, most of the time those are fine.
If you really need to fine-tune it, use those.
Otherwise oftentimes, we can just say, center on the page.
Now, if you have a small table, it kind of
looks funny if it's centered vertically also.
So we'll just say, horizontal.
Now we go over to Header and Footer.
And in Excel, we have Headers that the top, a dropdown
for certain options, Escape, and then a button
to get to Custom Header.
Then down at the bottom, we have a preview of our Footer,
a dropdown for certain items, Escape, and then a button
to get to the full Custom Footer.
Now, we're going to click on Custom Footer.
But before we do that, there's a great dropdown.
And we saw this over in Word.
If we select page 1 of question mark, that's the same as page
x of y that we saw over in Word.
I'm going to click on page 1 of question mark.
Now, already, we see a preview there.
Now, we only have one page here.
But I'm going to show you this great often used Footer
here, even though we have only one page.
If we had 10 pages, it would say, 1 of 10,
2 of 10, and so on.
Now, I'm going to click Custom Footer.
Right in the middle, it shows you the secret code
that will dynamically create our Footer
for however many pages we have.
Now, there's a left, center, and right section.
You can actually just type something like, Your Name.
You could actually highlight your text
and click the Font button.
And you could add something like Bold and 12.
Click OK.
Oftentimes, companies will put their company name here.
We can also come to the right section.
And there's a bunch of options here.
That will print out just the single page number.
That will print out the total number of pages.
That will print the date-- and I'm
going to click on that for date.
That will print the time.
That will print the full file path name.
That will print the Excel Workbook name.
There.
I'm going to click Insert Sheet.
But before we do that, I'm going to click right
before the code for the current date.
And now I'm going to click the little icon for please
give me the sheet name.
But now, I'm immediately going to type Space Dash Space.
You also could insert a picture.
Now, I'm going to click OK.
Hey, look at that.
There's our preview.
The last tab in Page Setup--
Sheet.
There is an awesome option.
Print Area.
Now, this is a text box.
And that little icon means that we can collapse this text box
and highlight cells in the sheet.
Now, all we have to do is make sure our cursor is
in print area, then simply click in cell A1.
And with our selection cursor, click and drag.
It's the same as our formulas.
If we make a mistake, just don't let
go until you highlight just the range you want.
Now, later in the class, we'll see how
to use rows to repeat at top.
Now, just as an example, if we had 100 students,
I could highlight just row 1 and 2 right there,
and on every page, it would print those Headers at the top.
Columns can be repeated also.
Here are some other options for different items you can print.
We don't know what comments or errors are yet,
but later we will.
And then if you have many pages on one sheet,
you can decide to print down and then over, or over and down.
I'm going to reclick that.
All right.
Page Setup.
Now I'm going to click OK.
Now I'm going to Control P. And that is looking beautiful.
Now, I got to tell you a little story.
Back in 2001, I had a tax accounting job
at Broderick Consulting in Berkeley, California.
And the first day I came in, Mary Broderick-- who
was an awesome accountant-- asked
me to work on some Excel spreadsheet
where I needed to enter data and print it out.
And all I did-- this was like an hour into my job--
I typed everything in, did a little Page Setup like this,
printed it, and brought it over to her desk.
And she was totally impressed, just with this simple Page
Setup that it looked so nice and professional.
Not only that, but fast forward to today,
October 14th, 2017, one of the students
in my class, Enoch yesterday, said
he did exactly the same thing at work.
Did some simple Page Setup, printed it out,
and his boss said, wow, that looks really great.
So simple task like this, yet very important and can get you
noticed at work quickly.
Now, I'm not going to click Print.
I'm going to click Escape.
Now, another potentially important thing
about a template like this is if I click in this cell
and look up in the formula bar, that's 100,
click in that cell, that's 55.
But notice that is a formula.
So is this.
And if I click down here for the average row, that's a formula.
Sometimes, it's nice to format the cells that
have formulas differently than the cells that have raw data.
Now, I have a convention that I've
been using for decades to help me so I don't accidentally
come when I use this next time and highlight everything
and delete.
My convention is I'm going to highlight
all the cells with formulas.
Now, we're going to use the Control
key to select a second range--
not next to each other.
Back in Word, in Windows Explorer,
we use the Control key many times
to select items not next to each other.
But now we have those ranges selected.
Right click.
And I'm going to use the mini toolbar.
Click the dropdown for my paint bucket to fill with color.
I don't see the color I want.
So I'm going to click More Colors.
And in the standard, I'm very carefully
going to click that green right there.
Now, you can pick whichever color you want.
Click OK.
But that is going to be helpful.
Now, I clearly know where the cells with formulas
are, and the ones that do not have formulas.
Now our last task is, now that we have our template--
I can see down here, this is for Gradebook Fall 17--
but I want to copy the entire sheet over so that I can use it
for winter 2018.
Now, the beautiful thing about copying an entire sheet
is that it will copy everything here--
the formulas, the formatting, and the Page Setup.
If I were to simply highlight this, copy, paste over here,
it wouldn't paste the Page Setup.
So watch this.
The long way to copy a sheet is to right click the sheet--
don't right click the cell or over here.
You want to right click the actual sheet and point to Move
or Copy.
Now, the nice thing about this Move or Copy dialog box
is that I'm allowed to Move or Copy this to somewhere
in this workbook-- or if I click the dropdown, a new workbook.
Or if I had multiple other workbooks open,
I could select any one of those.
Now, for us--
I'm going to click Escape--
we want to copy it to a new location in this workbook.
Now we need to select where we're going to copy it
within this workbook.
And notice it says before sheet.
So it's going to be copied before sheet 4.
And most important-- we need to check Copy.
If we don't check Copy, it will move it.
But I want a copy so I check it.
Click OK, and there's my new sheet.
Now I can double click and rename this Winter 2018
and Enter.
Now, the fastest way to copy this
to some other location in this workbook is this--
we're going to point to this sheet,
and I want you to click and drag up.
Now, notice there's a piece of paper sitting under my mouse.
And if I come over to the side, there's a little black arrow--
little black downward-pointing arrow.
That says I'm going to move it here.
Now, right now-- watch this--
I'm going to move it.
I can see the arrow pointing when I let go of my click,
it moved it.
Now I'm going to move it back.
Click, drag up, I see the piece of paper,
I see the downward-pointing black arrow, I drop.
That moved it.
The way you copy it is I click, drag up,
I see that piece of paper, but now I hold control.
The control gives me a plus.
That plus says I'm copying it.
Now, I'm going to make sure my triangle is
pointing after winter, and I let go of my mouse--
not control key.
Now I let go of control.
And there I have copy.
Now, that might take a little practice,
but it is the fastest way to copy a sheet.
Now, I'm going to double click this and call this--
and I'm going to call this spring 2018 and Enter.
All right.
That was an epic introduction to Excel.
We saw how to enter text data and number data.
We saw how to do Stylistic Formatting.
We saw how to create formulas using
built-in functions like the SUM, like the Average function.
We also saw F2 how to create formulas that have Relative
and Absolute Cell References.
We talked about Number Formatting
and how Number Formatting was a facade.
We did control P--
beautiful Page Setup-- and Escape.
We even saw how to copy sheets over.
Now, our next introduction video will
be all about adding and counting with different types
of formulas.
All right, if you like this video,
be sure to click that thumbs up, comment,
and sub, because there's lots more videos to come.
All right, we'll see you next video.