Welcome to Office 2013 Video Number 24.
Hey, if you want to download this workbook,
and this workbook is for Videos 23 to 27, click on the link
below the video or go to our class website.
Hey, we're still studying Excel.
This is Excel 2013 Basics Number 6.
Now, this video-- we're going to look at a few keyboard
shortcuts.
And why are keyboards so important?
Because keyboard shortcuts can make you fast.
This is a sure method to impress in your job interview
or when you're at work.
All right, we want to remind ourselves
about Control selection trick and our Shift selection trick.
We've seen this a bunch of times in all of the programs
we've learned, but if I select a cell
and hold Control, when I click, click, click,
I get to highlight things not next to each other called
non-contiguous.
But for this video and for a lot of Excel,
one of the most powerful keyboards involves click,
and before you click on the last one, you hold Shift.
All right?
That's our Shift selection trick.
We also want to remind ourselves-- keyboards,
when you see them written, Control+C means that you hold
the key simultaneously.
When you use an Alt keyboard shortcut, Alt-P-S-P,
we learned this in Word, and we'll see that this keyboard
also works in Excel.
That is Page Setup dialog box.
But those commas mean that you hit them in succession.
So you hit the Alt and then the P and then the S
and then the P. You don't hold them down like you
would a Control keyboard.
All right, here's a list of the keyboards we're going to learn,
and the first question is-- this is unrelated--
hey, what do the railroad tracks mean?
It means the column is not wide enough,
so I'm going to click and drag.
Now our first question is how tall is this data set?
So last couple of videos, we used SUMIFS and pivot tables,
and we had small data sets, but most of the data
sets you're going to get in a job
are not very small, so I don't want
to have to click and drag or use my scroll arrow.
I simply want a quick way to jump
to the very last part of this data set, the last row.
And the keyboard is Control Arrow.
Now what Control arrow does is-- we're selected D21-- it just
jumps to the bottom, and how does
it know where the bottom is?
It finds the first empty cell and stops above.
So Control-Down Arrow.
Control Up Arrow would jump to the top, right?
It jumped until it saw an empty cell and jumped back.
Control Down Arrow, boom.
Saw an empty cell, jumped one below.
Now, this works in any direction.
If I Left Arrow-- Control-Left Arrow, Control-Right Arrow,
the arrow with Control work in any direction.
Now if you Control-Arrow and there's nothing,
it just keeps going until it finds
the edge of the spreadsheet.
Control-Home, which is not in my list, that
brings you all the way to A1.
All right, so that's great.
Control-Down Arrow, wow, look at that.
All the way to row 602.
Control-Up Arrow.
Our next keyboard shortcut is selecting.
Now remember, if I click right on this cell while holding
Shift, boom, it highlights everything in between.
So we're going to combine.
Control-Down Arrow to jump, but if you hold Shift,
it highlights.
And here's our goal.
We need to decrease decimal.
So are you ready?
Select cell D21, Control-Shift-Down Arrow.
That's my favorite keyboard in all of Excel.
It is just amazing.
It works when you're highlighting cells.
It'll work in formulas.
It'll work in dialogue boxes.
Now last video, we saw how to add buttons to the QAT,
and we want to learn how to access those QAT
buttons using Alt.
So we learned back in Word that when you hit the Alt key,
the ribbons have little screen tips.
And you can get to each one of these ribbons
like Home using H, N for insert, A for data,
but the Alt keys have numbers.
That's why I always put the ones that I use the most
right at the left front of the QAT.
So now it's just Alt-1, and instantly I
have decreased decimals.
So this is the second one.
So Alt-2 would increase.
Alt-1-1 would give me two decreases.
So Alt keys to get to your QAT.
Now another great keyboard is Control-Shift-Down-Arrow
or in any direction, highlights in a particular direction,
but what about the whole table?
We actually saw this earlier in this class,
but it's an important one, Control-* Now you either use
Control-* on the number pad or Control-Shift-8.
Whichever one you do, it'll highlight the whole table.
It looks in all direction until it finds complete rows
and columns of empty cells or if there's row headers or column
headers.
Now our goal here is to add a border, so instead
of highlighting with our cursor and clicking and dragging
forever-- ugh, how many of us have done that?
That is painful.
No more.
Control-* or Control-Shift-8.
And now we can right click and go to our mini toolbar,
and there's our border button.
And I'm going to say, how about all borders up here?
Here
All right so our next keyboard, the SUM function,
is the only function of the 450 functions that has a keyboard.
Alt=.
And if you forget on a test or whatever,
Home and hover your cursor over SUM button,
and you'll see Alt= in that screen tip.
All right, you ready?
Alt=, absolutely amazing.
Now let's use-- now let's see that
our Control-Shift-Arrow key works in a formula.
So I select cell D21.
Control-Shift-Down Arrow.
Oh, that is amazing.
That would take a long time to highlight all the way down.
Now I'm going to Shift-Enter.
Instead of Enter, that would have brought the cell down,
Tab would have gone to the right,
Shift-Tab goes to the left, Shift-Enter goes up.
And of course we know Control-Enter
keeps the cell selected.
I'm going to hit F2.
That is simply amazing.
Let's try our SUMIF formula like we did the last two videos,
so =SUMIFS.
And here's the deal.
We have some big ranges to put into this formula.
So the SUM range as we saw, those are the numbers to add.
I click on top cell, Control-Shift-Down Arrow
and now F4.
Now F4 is one of the keyboards we learned.
We've used it in the last three videos.
I don't have it in this list, but that's a pretty awesome one
there.
That adds the dollar signs to make that absolute.
Notice how it also jumped the screen back in view.
Those are the numbers to add, but if we
want just the sales for this particular date,
we have to give it a criteria range.
That range says give me the whole column of dates.
So I click on the top cell Control-Shift-Down Arrow-F4
to lock it.
Now I type a comma and I'm going to use my Left Arrow
key to get my cell reference.
That's a relative cell reference.
That's exactly the same formula we
did last video except for what?
It was much faster.
We had a huge data set, didn't want
to highlight all the way down.
I use Control-Shift-Arrow key and also the F4 key.
Close parentheses, Control-Enter, double-click
and send it down.
I'm going to check the last cell, F2.
I'm verifying that my ranges are looking good.
All right, so keyboards really can help us,
Control-Shift-Arrow key in formulas
and with highlighting the cells and doing something
like formatting.
Now what about a pivot table?
We saw pivot table last video, and we always
selected a single cell.
It's a proper data set.
Insert pivot table.
If this is something you do day in and day out,
that's not the way you want to do it.
You want to select a single cell,
and when you're teaching yourself the Alt keyboards,
you hit Alt, you look up and say, oh, it's
N, so I tap an N. Then I see that it's V,
so I type a V. Just like that.
Now remember, if you're doing pivot tables day in
and day out, the first 100 times you go to Insert pivot table,
but at some point in your job, you're like,
OK, I've got to learn the keyboard,
then you do it slowly.
Alt-N-V. Now if you're in 2013, this keyboard is only Alt-N-V.
In earlier versions, 10 and 7, it was Alt-N-V-T. All right,
are you ready?
Alt-N-V. That is amazing.
A keyboard to get to the pivot table dialogue box.
Now I'm going to select Existing and Location,
just as we did last video.
I'm going to be risky here.
I'm going to click right in my spreadsheet,
making sure there's nothing below and click OK.
All right, so now I'm going to drag my date,
and notice this is to the rows area,
because I've looked at my report and I say I want my dates,
my criteria in rows.
Wow, that is amazing.
And then I take my sales and drag it to values.
Again, later in the class, we'll have
a video that shows how to change the function
and format your pivot table.
Keyboard Alt-N-V.
And then the last keyboard we want to see in this video
is Page Setup.
If we Control-P-- oh, this is going to be terrible.
Look at that.
1 of 14 pages.
Well, I have my data set there, and I only want my report.
When I print this I only want this little table right here,
so Page Layout, Page Setup Group, Dialogue Launcher?
Forget it.
After doing this 20, 30, 40 times every day,
day in and day out, I'm going to teach myself the keyboard.
Alt and I see that Page Layout is P. I see that it's S-P.
And even though that seems like a long keyboard, Alt-P-S-P,
it's much faster than going from the keyboard to grabbing
your mouse and going up to the Page Layout ribbon tab.
Ready?
Alt-P-S-P. And now I can do Landscape.
I'm going to make this one real big here.
I'm going to do Margins, horizontal.
I'm going to do only a custom built-in Footer.
Page 1 of 1, and then I'm going to come to Sheet,
and I'm going to say Print Area, and I'm
going to select just that right there.
Click OK.
Now when I Control-P there is my report.
That's a silly report.
That's too small there.
All right, so keyboards, they are your ticket
to speed and efficiency in Excel.
We'll see you next video.