WEBVTT

00:02.560 --> 00:07.360
OK guys welcome to the second lecture in this bonus section where we're going to have a look at all

00:07.360 --> 00:09.320
the keyboard shortcuts.

00:09.320 --> 00:14.260
Now one of the first things that we looked at was how to get Excel to automatically populate a bunch

00:14.260 --> 00:14.990
of data.

00:15.040 --> 00:18.310
If it was a natural sequence such as days of the week.

00:18.580 --> 00:22.310
So if I typed Monday in there you'll remember that we use the drag handle.

00:22.390 --> 00:27.280
Now if we hover over the bottom right hand corner of the cell that we've selected we can see that the

00:27.280 --> 00:34.030
cursor changes and if we left click and drag down we automatically get Excel to fill that sequence in

00:34.030 --> 00:35.850
for us automatically.

00:36.040 --> 00:40.950
So if you type in days of the week or months of the year just type your first entry.

00:41.070 --> 00:48.070
So the drag handle and drag either down or across or up or to the left and it will automatically populate

00:48.070 --> 00:49.230
that sequence for you.

00:49.300 --> 00:54.550
Okay so use the drag hand also automatically populate natural sequences.

00:54.730 --> 01:01.990
Now the second you so the drag hand or if you already have data in a column is if you type January in

01:01.990 --> 01:07.690
this column here now because we already have some data in this column Excel knows how far we want to

01:07.690 --> 01:10.740
go down and naturally we don't have to drag it.

01:10.740 --> 01:15.550
It will automatically work it out by finding the last cell immediately to the left.

01:15.640 --> 01:21.100
So the other use for the drag hand of when you already have data is again hover over it but this time

01:21.100 --> 01:26.590
instead of drag in just double click and it will automatically fill that sequence down.

01:26.620 --> 01:28.540
As far as your data set goes.

01:28.810 --> 01:29.340
OK.

01:29.550 --> 01:31.440
So all run the subject of double clicking.

01:31.550 --> 01:33.510
Let's just type a number in there.

01:33.520 --> 01:36.600
Now this could be any value or any formula.

01:36.690 --> 01:42.130
And again to get Excel to copy it all the way down to the bottom of your data set double click and they

01:42.130 --> 01:45.930
will automatically populate down for as far as your data goes.

01:46.090 --> 01:49.580
So double click him or automatically copy all of that down.

01:49.600 --> 01:53.380
As far as your dataset goes No because we typed a number in there.

01:53.380 --> 01:58.490
That wasn't a natural sequence so Excel just repeated the number again and again and again.

01:58.630 --> 02:04.750
If we actually wanted to say put the 5 times table in what we need to do is give Excel a couple of values

02:05.050 --> 02:10.780
so we can recognize the sequence and this time just left click and drag over the two values that you've

02:10.870 --> 02:11.670
entered.

02:11.950 --> 02:17.170
So no Excel can spot the sequence and this time if you double click it will automatically populate the

02:17.170 --> 02:19.570
rest of that sequence naturally say.

02:19.600 --> 02:23.260
That's Hey quickly get the five times table just hit 5 and 10.

02:23.260 --> 02:24.300
Highlight the two.

02:24.470 --> 02:29.570
Double click the drag mark and it will automatically populate the entire sequence down for you.

02:29.710 --> 02:33.850
The other thing we looked at was copying right and that's very simple to do.

02:33.850 --> 02:36.500
The keyboards sure Kittie's control in are.

02:36.670 --> 02:37.920
So what this is going to do.

02:37.930 --> 02:44.430
It's going to look at this cell and automatically populate this cell with its value so control and fills.

02:44.620 --> 02:45.430
Right.

02:45.460 --> 02:48.060
They can do one at a time control and are.

02:48.100 --> 02:52.370
Or if you wanted to do a bunch of data just highlight all of your blank cells.

02:52.430 --> 02:56.180
Control them are and they go it copies everything and write.

02:56.500 --> 03:00.220
And similarly you can copy down using control in D.

03:00.420 --> 03:06.670
Now notice there that it didn't extend the sequence control and literally will copy the cell immediately

03:06.670 --> 03:12.130
above and paste into the cell you've selected so control and D copies down.

03:12.190 --> 03:17.740
And again if you wanted to do a few cells you could just highlight them by holding the Shift control

03:18.010 --> 03:25.330
and day and they go you copy down a case so that's a use the auto fill sequence and use the drag handle

03:25.330 --> 03:27.720
to automatically populate a bunch of cells.

03:27.970 --> 03:29.210
And that's Use control.

03:29.260 --> 03:34.110
Ah to copy right and control and D to copy down.

03:34.600 --> 03:38.190
Right let's have a look at cutting copying and pasting.

03:38.410 --> 03:43.570
So you'll remember if we wanted to copy all this data so we can just left click and drag over it hit

03:43.570 --> 03:50.140
Control in C and control and see copies and then we could come over to our first blank column.

03:50.140 --> 03:52.770
Now we have two ways of getting the data in here.

03:52.780 --> 03:59.260
Notice that we have dancing in the data we've just selected in columns say if we wanted just paste it

03:59.260 --> 04:02.580
once so we can just hit enter and they go.

04:02.590 --> 04:08.440
We have successfully copied that data and pasted it somewhere else but we have two options of paste

04:08.440 --> 04:09.340
in the data.

04:09.610 --> 04:15.320
We can hit Control one to see again to copy it and instead of hitting enter and then a hit Control in

04:15.340 --> 04:17.260
V to paste.

04:17.260 --> 04:23.500
No notice that the dancing and so still appear in around the data in Column see what that means is that

04:23.500 --> 04:25.210
that's been retained to the clipboard.

04:25.210 --> 04:28.810
So if I was to cite the next cell along and hit control them v..

04:28.810 --> 04:33.970
Again I can copy it again and I can keep doing this for as often as I want to.

04:33.970 --> 04:37.480
So while the in answer there and I hit control them v..

04:37.540 --> 04:42.220
We can always keep copying the data because it's been retained in the clipboard.

04:42.460 --> 04:46.810
If you want to get rid of the dancing and after you've copied it a bunch of times and pasted it a bunch

04:46.810 --> 04:49.110
of times just hit the escape key.

04:49.330 --> 04:52.110
So the difference between control and V.

04:52.220 --> 04:55.700
Enter is when you enter it empties the clipboard.

04:55.720 --> 05:01.010
You can only paste it once if you want to paste it multiple times hit control and v..

05:01.180 --> 05:01.770
Which is the key.

05:02.010 --> 05:03.720
Short cut for paste.

05:03.810 --> 05:10.770
Now the other thing we looked at was how to cut data and cutting you'll remember cuts the data from

05:10.770 --> 05:13.660
the original place and puts it somewhere else.

05:13.740 --> 05:19.650
So you actually delete the data that you're quoting and the keyboard shortcut for Curt is control and

05:19.740 --> 05:23.270
X and X looks a little bit like a pair of scissors.

05:23.430 --> 05:25.470
That says Remember the difference.

05:25.470 --> 05:28.350
And again just click over to your first blank cell.

05:28.450 --> 05:30.130
Hit control and V.

05:31.110 --> 05:36.500
And you can paste it in there or similar you can just press enter and that will also pace it in there

05:36.860 --> 05:42.510
so control when ex cuts the date at which removes it from its original place and allows you to put it

05:42.510 --> 05:43.600
somewhere else.

05:43.620 --> 05:43.990
OK.

05:44.100 --> 05:46.760
That's cutting copying and pasting.

05:47.110 --> 05:52.860
Okay now again have a look at the keyboard shortcuts for formatting and those are these cells just up

05:52.860 --> 05:53.450
here.

05:53.730 --> 05:55.960
So they're very easy to remember.

05:55.980 --> 06:00.780
If you hover over them you'll remember that your tooltip will always tell you in brackets what your

06:00.780 --> 06:02.590
keyboard shortcuts is.

06:02.700 --> 06:09.390
And these three are really simple to remember to make something bold hit control and be bold.

06:09.390 --> 06:10.350
Begins with.

06:10.350 --> 06:16.370
Be nice and easy if you want to make something italicised control in.

06:16.680 --> 06:20.970
Which is very simple to remembering aim because italics begins with I.

06:21.030 --> 06:28.140
And if you want to underline something hit Control new pictures underline begins with you and you can

06:28.140 --> 06:29.640
have any combination of those.

06:29.640 --> 06:34.380
If you want it to have something in bold and italic slamming just move the cursor you can hit control

06:34.410 --> 06:40.260
and obey and control when I add it or have it in bold and italics and if you want it to finish is off

06:40.260 --> 06:48.120
by underlining it hit control and you and they go yet all three formats so control and B makes something

06:48.120 --> 06:51.320
bold because bold begins with B Control end.

06:51.360 --> 06:57.050
I make something in italics because italics begins with I need to underline something.

06:57.040 --> 06:57.660
Control.

06:57.690 --> 06:58.410
And you.

06:58.500 --> 07:01.180
Because underlying begins with you.

07:01.470 --> 07:07.350
Okay earlier we also have a look at what we did if we made a mistake and we use the undo button which

07:07.350 --> 07:08.990
was this one just up here.

07:09.160 --> 07:15.030
But you remember if we have over it again we can see that the keyboard sure quoties control Z.

07:15.100 --> 07:20.520
Now that's because it un does whatever we've just done so to undo the things that we've just done be

07:20.520 --> 07:27.660
applied some formatting control and Z and that will automatically let you undo what ever you just did.

07:27.660 --> 07:29.230
If you've made a mistake.

07:29.460 --> 07:34.440
Okay so the keyboard shortcut for undoing is control and Z.

07:34.440 --> 07:39.400
If you decide that you actually didn't make a mistake and you want to redo what you've just done.

07:39.420 --> 07:44.640
Again if you're not sure of what the keyboard shortcuts just hover over the redo and you can see the

07:44.650 --> 07:49.340
e get the tooltip which tells you the keyboard you're just up there and control.

07:49.380 --> 07:55.590
NY allows you to redo what you've just done so the easy way to remember that or the way I remember that

07:55.800 --> 08:00.460
is by saying Yes I actually did want that after all so control and y is.

08:00.480 --> 08:06.870
Yes I did want that after all okay when you working with large data sets you may want to instantly locate

08:06.870 --> 08:09.040
a particular piece of data.

08:09.060 --> 08:13.760
So for argument's sake let's imagine that we wanted to find Saturday in our day to assess.

08:13.770 --> 08:15.160
Quite easy to say at the moment.

08:15.180 --> 08:20.730
It's quite small but the keyboard should quit for finding you wouldn't be surprised to hear because

08:20.730 --> 08:27.960
Find begins with F is control an F and that will bring up this dialogue box just here and you can see

08:27.960 --> 08:35.520
that we've already typed in Saturday santu day and you can hit find next and it will automatically skip

08:35.520 --> 08:39.860
around the spreadsheet and highlight each instance of that entry in there.

08:39.870 --> 08:44.610
Now there another thing you can do here and that is hit the Replace which will allow you to replace

08:44.610 --> 08:46.720
something with something else.

08:46.740 --> 08:52.740
So if I want it to replace all of Saturday with apples I just type apples in here.

08:52.920 --> 08:55.250
I could type anything like say oranges.

08:55.350 --> 08:58.110
I don't know why we replace ancestor with oranges.

08:58.170 --> 09:01.140
If you want to do them all at once hit replace all.

09:01.170 --> 09:06.270
If you want to do the wanted time just hit replace and it will go through them in sequence or they just

09:06.270 --> 09:08.650
replace all and they go way down.

09:08.820 --> 09:15.270
So to find some thing the cable show quit is control an F because Find begins with f..

09:15.310 --> 09:21.000
If you want it to instantly have it so it opens up with this replace dialogue box highlighted control

09:21.010 --> 09:22.170
an H.

09:22.170 --> 09:25.600
An easy way to remember that is have begins with H.

09:25.770 --> 09:29.620
And you may want to have something in place of something else.

09:29.700 --> 09:31.060
So control an F.

09:31.140 --> 09:33.470
Find Control in H.

09:33.570 --> 09:36.590
Let you have something so you can replace.

09:36.990 --> 09:41.370
Okay and the final few just to control excel on its own.

09:41.370 --> 09:47.100
We could use a couple of other keyboard shortcuts and if we wanted to open a new file we could hit the

09:47.100 --> 09:49.850
keyboard shoket Control in N..

09:50.030 --> 09:53.850
And that will automatically open a new instance of Excel.

09:54.030 --> 10:01.850
So if you want a new workbook control an N will give you a new word book because new begins with N.

10:01.890 --> 10:03.340
Nice and easy to remember.

10:03.360 --> 10:05.390
Then we just close that together is the way.

10:05.580 --> 10:12.030
If you want to open an existing workbook because open begins with 0 you can hit Control.

10:12.180 --> 10:17.750
Oh and that will bring up your open dialogue box and you can instantly click any of your other files

10:17.750 --> 10:23.020
there to open them up so control an O opens a new workbook.

10:23.070 --> 10:28.800
You remember that we already looked at control ampie which open the print dialogue box because print

10:28.830 --> 10:29.630
begins with P.

10:29.640 --> 10:30.060
So.

10:30.280 --> 10:34.030
Sholem brings up your print dialogue box.

10:34.270 --> 10:40.960
And finally one that we didn't look I dont think was control an S which is to save your work now because

10:40.960 --> 10:45.390
I can actually say this yet it will automatically bring up the Save As dialogue box.

10:45.490 --> 10:52.270
But since your work has been saved and you successfully renamed your file control in S will automatically

10:52.270 --> 10:53.820
save your work book.

10:54.030 --> 10:57.620
Okay so they go there's a bunch of keyboard shortcuts.

10:57.670 --> 11:03.760
Please if you have any easier ways to remember these open suggestions please drop me an email and I'll

11:03.760 --> 11:07.690
happily amend this thing if if you come up with a much better way than I did.

11:07.750 --> 11:09.950
So they go you've made it to the end of the course.

11:09.960 --> 11:11.220
Oh you had some fun.

11:11.230 --> 11:15.460
I hope you've learnt an awful lot of stuff and I look forward to seeing you in the next course.

11:15.460 --> 11:16.200
Thanks a lot.
