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. 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.
