Welcome to Excel Basics video number seven.
Hey, as we've talked about in this class all
the way from the beginning, keyboard shortcuts are fast.
And if you want to download this Excel workbook file here,
or the PDF files, you can click on the link below the video.
Hey, and we're on the sheet within this workbook called
keyboards.
Now, the first keyboard we want to look at
is I've got this data set here.
And I need to create some formulas
in a pivot table over here.
But I have no idea how tall this data set is.
No problem.
I click on the top cell, and use the keyboard
to jump to the bottom, Control down arrow.
Just like that, it keeps going until it
bumps into an empty cell.
Now, if there's empty cells in the data set,
it would have stopped above that empty cell.
Now, watch what happens if I Control down arrow again.
It keeps going down, and because it can't find anything,
it just stops at the bottom of the spreadsheet.
Now sometimes, we get lost in a spreadsheet like this.
And all I want to do is jump back to cell A1.
The keyboard, Control, Home.
Now, I'd like to highlight all the way to the bottom.
Whereas over here, we did Control arrow,
if I want to highlight all the way down to the bottom
and do some formatting, I have to add the Shift key to it.
So now if I hold Control, Shift, down arrow, instantly,
I highlight all the way to the bottom.
Now, both of these two examples--
Control, down arrow, and Control, Shift, down arrow--
work going down.
But those same keyboards work if you use the right arrow, left
arrow, up arrow, or down arrow.
Now, the next thing I want to do is I actually
I want to jump back to the active cell at the top
of this highlighted range.
The keyboard to jump back to the active cell--
Control, Backspace.
I love that.
I've highlighted somewhere, and I just
want to get back to the top.
Now, I want to decrease the decimals.
And last video, we saw how to create a custom quick access
toolbar.
And we talked about the importance
of putting the buttons that you use
most often at the very front of the quick access toolbar.
And here's why.
When we hit the Alt key--
we've seen this many times in the class so far--
screen tips pop up.
We know if we hit N, it jumps to the Insert Ribbon tab.
But now we see that hitting Alt also
enacts numbers for all the buttons in our quick access
toolbar.
So since decrease decimal is one, now I'm going to hit one.
Now, we want to remember that Alt keyboards are succession
keys.
That means if I want to decrease the decimals, I hit the Alt key
and let go.
Then, I tap the 1 key and let go.
Noticing that increased decimal is the second button,
I can Alt 2.
I'm going to do Alt 1 in succession
to get back to zero decimals.
Now, our next task is to highlight the whole table
and add borders.
So there's definitely a keyboard to highlight the whole table.
And I don't want to use Control A, because
in advanced Excel when you use the macro recorder, that
doesn't work as well as Control Asterisk.
Now, I'm going to use the number pad Asterisk, Control Asterisk.
And instantly, my whole table is highlighted.
Now, many of you got this--
Control 8, and you did the keyboard for outline symbols.
So if you're going to use the standard keyboard,
it's Control, Shift, 8.
Now, I can add borders-- whether up in the font group,
or right click.
There's our mini toolbar, all borders.
Now, another one of the greatest keyboards ever in Excel--
out of 450 functions, the sum function
is the only one with a keyboard.
And the keyboard is Alt, Equals.
Now, that Alt, Equals, you actually
have to hold Alt and Equals down at the same time.
And there's our sum.
Now, I need to highlight the entire range.
And watch this-- right in the middle of creating a formula,
I can use Control, Shift, down arrow
to highlight and insert the range into the formula.
Control, Shift, down arrow.
Now, I immediately want to jump back to the active cell.
So Control Backspace.
That keyboard works whether we're
in the middle of creating a formula,
or just highlighting a range like we did earlier.
Now I hit Enter.
Now, we want to create a sum ifs report, where
we have daily sales totals.
So I'm going to click on the top cell.
Equals, S-U-M-I, and I notice the function I want with
the sum ifs with the S, is the second one.
So I use the down arrow to select the function in blue.
Once it's selected in blue, another great keyboard--
Tab to put the function into the cell.
Now, I need to highlight the sum range.
Those are all the numbers I potentially want to add.
So I'm going to click in the top cell, use the keyboard
Control, Shift, down arrow.
But I'm copying this formula, and I need to lock it.
So now, I use F4 key to put the dollar signs
and convert that to an absolute cell reference.
Now, I'm going to type comma to get to criteria range 1.
I do need to copy sum ifs down and get totals
for 10/20, 10/21, and 10/22.
But that's the criteria.
Since it says range, that means I need to give it
the entire date column.
So I click on the top cell, Control, Shift, down arrow.
F4 to lock it.
Now, I comma and criteria 1.
I'm using the keyboard--
the arrow key-- to insert the cell directly one to the left.
That relative cell reference will work, close parentheses.
And now, another great keyboard--
Control Enter.
Because my goal is to put the thing in the cell
and keep the cell selected.
Now, why do we need to do that?
Because we're immediately going to copy the formula.
There's my fill handle.
I'm going to move my selection cursor over.
And when I see my crosshair, double click and send it down.
Now, I want to jump to the bottom using Control, arrow.
I want to put the cell in edit mode using the F2 key.
I don't know what I'd do without the keyboard F2.
Now, I'm verifying, because any time we copy a formula,
you've got to go to the bottom and F2 to verify.
Now, I'm going to hit Enter.
There's a bunch of amazing keyboards.
Alt and V, we saw that one last video.
That's for inserting a pivot table.
And Alt PSP, we saw that in Word and Excel
to open up page setup.
All right, so in this video, we simply
saw a bunch of keyboards-- most of which
we've already seen before.
And we use keyboards because they are fast.
They help us format a column--
jump to the bottom, enter our formulas,
lock our cell references-- more quickly than if we did it
without keyboards.
All right, if you like that video,
be sure to click that thumbs up and leave a comment and sub,
because there's always lots more videos to come from Excel
is Fun.
Next Excel basics, we'll actually
talk about default alignment.
Yes, default alignment can help us quite a lot in Excel.
All right, we'll see you next video.