1 00:00:03,000 --> 00:00:08,090 Let's take a look at some of the most useful Excel shortcuts for navigation. 2 00:00:08,119 --> 00:00:10,020 I'm on the start file for this section. 3 00:00:10,110 --> 00:00:13,220 So let's jump to the shortcuts tab. Here I have added 4 00:00:13,220 --> 00:00:15,240 the most common shortcuts I use. 5 00:00:15,240 --> 00:00:17,050 So let's quickly go through them. 6 00:00:17,100 --> 00:00:20,160 First one is how to move to the next worksheet 7 00:00:20,190 --> 00:00:26,550 and the previous worksheet. Here you can use control and then page up and the page down arrow. 8 00:00:27,000 --> 00:00:33,810 So if you could control and Page Down you go to the next sheet. Hold down control and click on page up 9 00:00:34,200 --> 00:00:37,210 and you go to the previous sheet. Control tab 10 00:00:37,230 --> 00:00:40,350 allows you to switch between Excel workbooks. 11 00:00:40,350 --> 00:00:44,360 So these are Excel workbooks that you've opened from the same Excel session. 12 00:00:44,400 --> 00:00:49,510 Let's open one so I'm gonna press control+n to open a brand new workbook. 13 00:00:49,530 --> 00:00:55,520 Now let's switch between them using control and tabs. Hold control and press the tab key. 14 00:00:55,640 --> 00:01:03,360 And we just jump directly from one workbook to the next. Control down allows you to move to the bottom 15 00:01:03,450 --> 00:01:04,709 of the data region. 16 00:01:04,709 --> 00:01:08,700 Check this out. If I'm in this cell I'm going to hold down control and press the down 17 00:01:08,700 --> 00:01:13,070 arrow key, I jump to the end of this data set. 18 00:01:13,110 --> 00:01:15,330 Notice after this there's a blank. 19 00:01:15,330 --> 00:01:21,080 So if I'm here in cell A21 and I use control down, I jump to a A29 20 00:01:21,090 --> 00:01:23,630 Because after this there is an empty cell. 21 00:01:23,640 --> 00:01:26,360 Let's do it right here in A37 22 00:01:26,370 --> 00:01:30,750 Where do you think I'm going to jump to? To the bottom of the data sheet. 23 00:01:31,230 --> 00:01:34,410 I have no text, no values in between. 24 00:01:34,410 --> 00:01:35,950 Now how do I jump back up? 25 00:01:36,060 --> 00:01:38,790 I can use control and the up arrow 26 00:01:38,790 --> 00:01:43,580 and that takes me directly to the first cell that has a value in it. 27 00:01:43,620 --> 00:01:46,770 If I click control+up again that's the next cell. 28 00:01:46,770 --> 00:01:52,510 If I want to jump back to A1, do you remember what the shortcut key for that was? 29 00:01:52,530 --> 00:01:54,450 That's control+home. 30 00:01:54,570 --> 00:02:00,870 Now you can do the same with the other arrow keys so control and the right arrow jumps to the end of 31 00:02:00,870 --> 00:02:01,620 your data set. 32 00:02:01,650 --> 00:02:06,280 So here if I had a value it would jump to C8. 33 00:02:06,480 --> 00:02:11,130 It's jumping to B7 because that's the cell with the last value. 34 00:02:11,130 --> 00:02:18,510 If I click it again I jump to the last column in Excel and control and left arrow to come back. 35 00:02:18,510 --> 00:02:24,030 If you want it to move to the bottom of the data region and highlight the range in between you have to hold 36 00:02:24,030 --> 00:02:30,690 down the shift key as well. So it's control+shift+down you highlight everything in between. 37 00:02:30,690 --> 00:02:37,740 control+shift+right, you highlight everything on the right side as well. If you want to select data in this data 38 00:02:37,740 --> 00:02:46,420 region you can use the shortcut key control+a. If I press control+a in this region here, what do I get? 39 00:02:47,020 --> 00:02:50,250 I get A21 to B29. 40 00:02:50,800 --> 00:02:52,960 So it checks where there is spaces 41 00:02:52,960 --> 00:03:01,370 and it doesn't highlight those. Control+end comes in really handy if you want to know the number of cells 42 00:03:01,390 --> 00:03:06,280 Excel keeps in memory. That also can have an impact on performance. 43 00:03:06,310 --> 00:03:07,380 So check this out. 44 00:03:07,480 --> 00:03:09,550 If I press control+end here. 45 00:03:09,550 --> 00:03:14,280 This is the last used cell but control+end can get a bit confusing. 46 00:03:14,290 --> 00:03:15,750 So let me show you how. 47 00:03:16,050 --> 00:03:23,680 If I input a number here so let's just put a 2 in D3. I'm gonna press control+end now. What cell do 48 00:03:23,680 --> 00:03:27,500 you think is going to be the last cell? 49 00:03:27,760 --> 00:03:37,190 D35, so it doesn't pick D3 it picks the last bottom cell based on this matrix here. 50 00:03:37,300 --> 00:03:39,330 Here's the other confusing thing. 51 00:03:39,430 --> 00:03:43,400 Let me just delete this by just removing the contents here. 52 00:03:43,430 --> 00:03:50,590 I'm just gonna press backspace on it, now I'm gonna press control+end again, it still has it in memory 53 00:03:50,980 --> 00:03:55,740 even though I remove the information in here. It hasn't reset yet. 54 00:03:55,750 --> 00:03:56,620 Now let's do a test. 55 00:03:56,620 --> 00:04:00,170 What if I save this, so the shortcut for save is control+s. 56 00:04:00,220 --> 00:04:04,540 I'm gonna close the workbook, the shortcut is right here. 57 00:04:04,540 --> 00:04:08,320 Control+w, open that workbook again. 58 00:04:08,500 --> 00:04:17,860 Now I'm gonna do control+end again, now it's reset. If you've applied formatting to this cell and you 59 00:04:17,860 --> 00:04:23,980 just remove the content but the formatting is there that cell is still going to be in the memory of 60 00:04:23,980 --> 00:04:30,590 Excel. It's sometimes safer to delete entire rows and columns. And to delete 61 00:04:30,640 --> 00:04:33,890 you can just highlight the column, right mouse click and delete. 62 00:04:34,060 --> 00:04:40,540 But since we're in our shortcut lecture, let's do the shortcut way of deleting stuff. The delete shortcut 63 00:04:40,630 --> 00:04:42,870 is control+- 64 00:04:43,030 --> 00:04:49,120 Now if you're just in a cell excel needs to know if you want to just delete that cell and if you do 65 00:04:49,120 --> 00:04:53,270 how it should shift the cell. Should it shift them left or shift them upwards 66 00:04:53,410 --> 00:04:57,360 or should it delete the entire row or the entire column. 67 00:04:57,360 --> 00:04:57,680 Now, 68 00:04:57,700 --> 00:05:03,190 since you're working with your keyboard you can use your down arrow keys to make your selection and 69 00:05:03,190 --> 00:05:04,180 then press enter. 70 00:05:04,180 --> 00:05:10,180 So if I want an entire column, let's just come down and press enter and it would delete my column. But if 71 00:05:10,180 --> 00:05:16,630 I want to avoid that dialog box and I just directly want to delete my entire column I need to highlight 72 00:05:16,630 --> 00:05:17,710 that column first. 73 00:05:17,710 --> 00:05:22,410 Normally I would go here and then use control+- 74 00:05:22,540 --> 00:05:25,960 Now that required the mouse click. To avoid the mouse 75 00:05:25,960 --> 00:05:33,310 you can use the shortcut key control+space. So, control+space highlights your entire column and then you 76 00:05:33,310 --> 00:05:36,520 can do control+- and that deletes the column. 77 00:05:36,640 --> 00:05:39,340 So let's just put a number here that we actually see it removed. 78 00:05:39,340 --> 00:05:45,610 So it was control+space, control+-. For the row, to highlight an entire row 79 00:05:45,610 --> 00:05:52,250 we need to use shift+space and then control+- to delete the full row. 80 00:05:52,420 --> 00:05:59,030 Next one is control+backspace. That shows the active cell. To demonstrate this 81 00:05:59,200 --> 00:06:01,260 let's just scroll all the way down. 82 00:06:01,330 --> 00:06:01,630 Right. 83 00:06:01,630 --> 00:06:03,480 So where is my active cell? 84 00:06:03,490 --> 00:06:06,400 It's somewhere on top. If I want to get to it 85 00:06:06,430 --> 00:06:08,770 I don't have to scroll up and look for it. 86 00:06:09,190 --> 00:06:14,570 I can use control+backspace that jumps directly to the active cell. 87 00:06:14,650 --> 00:06:19,510 If the active cell is somewhere down here and I'm somewhere up here and I want to know where is that 88 00:06:19,510 --> 00:06:24,120 active cell control+backspace jumps to that active cell. 89 00:06:24,130 --> 00:06:30,280 Some of these ones we've already seen in the previous lecture. We saw that the tab key moves to the 90 00:06:30,280 --> 00:06:30,980 right. 91 00:06:31,000 --> 00:06:34,610 If you want it to move to the left you can use shift+tab. 92 00:06:34,660 --> 00:06:39,970 I personally prefer to use the arrow keys to move backwards. 93 00:06:39,970 --> 00:06:42,940 Enter also moves down. If you want to move backwards 94 00:06:43,030 --> 00:06:45,310 you can also use shift+enter 95 00:06:45,310 --> 00:06:47,520 or just use the up arrow key. 96 00:06:47,530 --> 00:06:53,440 Now we also saw that if you input something in Excel and you press Enter you automatically go to the 97 00:06:53,440 --> 00:06:54,390 next cell. 98 00:06:54,460 --> 00:06:58,570 If you want to stay in the same cell you need to press control+enter. 99 00:06:58,570 --> 00:07:03,280 Now I personally like to press enter and stay in the same cell. 100 00:07:03,310 --> 00:07:06,850 I don't always want to remember to press control+enter. 101 00:07:06,850 --> 00:07:13,300 There is a setting in options that you can adjust to make Excel behave this way. 102 00:07:13,360 --> 00:07:21,470 If we go to File > Options. Under advanced, the first option here after pressing enter, 103 00:07:21,490 --> 00:07:23,520 how do you want to move your selection. 104 00:07:23,650 --> 00:07:29,860 You can adjust it if you want but if you don't want it to move at all which is in my case you just have 105 00:07:29,860 --> 00:07:32,700 to take away this checkmark and click on OK. 106 00:07:32,830 --> 00:07:40,210 So now every time you press enter you actually stay on the same cell which is personally my preferred version. 107 00:07:41,360 --> 00:07:42,410 F2 we saw, 108 00:07:42,430 --> 00:07:45,180 you can use that to edit the cell contents. 109 00:07:45,220 --> 00:07:51,560 It's the same as doing a double click with Alt+enter you can add a new paragraph in your cell. 110 00:07:51,590 --> 00:07:59,060 So if I want to put this in the next line I can press Alt+enter here and it will put it in the next line. 111 00:07:59,060 --> 00:08:02,260 Control+z if you want to go back. 112 00:08:02,630 --> 00:08:08,420 So some of these we already saw. Shift+F11 is to insert a new worksheet. 113 00:08:08,450 --> 00:08:13,480 So remember we said that if you click on the plus sign you can insert a new sheet. 114 00:08:13,550 --> 00:08:15,870 Let's use the shortcut key Shift+F11 115 00:08:15,890 --> 00:08:20,990 that inserts a new sheet. 116 00:08:20,990 --> 00:08:26,570 We already saw the control+- that was to delete a cell row or column. 117 00:08:26,590 --> 00:08:34,100 Control++ does the same thing you just insert a new row or column. If you wanted to insert an entire column 118 00:08:34,190 --> 00:08:35,830 or an entire row 119 00:08:35,929 --> 00:08:42,559 you can use shift+space first or control+space and then use control++. 120 00:08:42,559 --> 00:08:50,480 Down here we have some formatting shortcuts. Control+B to change the format to bold and control+B again 121 00:08:50,480 --> 00:08:56,610 to take it away so it's a toggle. Control+1 gets you to the format cells dialog box, so remember 122 00:08:56,680 --> 00:09:02,660 this dialog box when you right mouse click you have format cells here. You can get there using the shortcut 123 00:09:02,660 --> 00:09:11,690 key control+1 and this is one of my most use shortcuts in Excel. F4 allows you to repeat the last 124 00:09:11,780 --> 00:09:12,480 edit. 125 00:09:12,620 --> 00:09:14,000 That's a good one to know. 126 00:09:14,120 --> 00:09:15,050 So check this out. 127 00:09:15,110 --> 00:09:23,090 If I change this cell to bold and I change the color to yellow now I'm going to go to this cell press F4 128 00:09:23,140 --> 00:09:25,420 I get the yellow color. 129 00:09:25,610 --> 00:09:27,790 Do I also get the bold. 130 00:09:27,790 --> 00:09:28,600 I don't. 131 00:09:28,700 --> 00:09:29,830 So it applies 132 00:09:29,830 --> 00:09:34,550 the last action I did in the last cell to this cell. 133 00:09:34,550 --> 00:09:41,390 If the last action was to make something bold so let's say I change this to yellow, I make it bold, now 134 00:09:41,420 --> 00:09:44,030 I'm going to go here do F4 135 00:09:44,060 --> 00:09:51,110 It doesn't do the yellow but it does the bold. Let's just press control+z a few times to go back. 136 00:09:51,150 --> 00:09:56,140 Okay so these are some of the most useful shortcuts that I personally use in Excel. 137 00:09:56,150 --> 00:10:02,210 You can also use the alt keys to access quick access toolbar and your menu item. 138 00:10:02,210 --> 00:10:08,480 So when you expand your quick access toolbar to add the most common features that you use. If you use 139 00:10:08,480 --> 00:10:09,830 the alt key here 140 00:10:09,830 --> 00:10:11,630 notice I get 1,2,3. 141 00:10:11,660 --> 00:10:15,430 So if I press 2 now it's going to save my workbook. 142 00:10:15,590 --> 00:10:22,850 If I press alt again and use 3 it's going to undo my action and I can use the arrow keys to decide 143 00:10:22,970 --> 00:10:24,700 until how much I want to undo. 144 00:10:24,710 --> 00:10:27,410 So let's undo both of these. 145 00:10:27,410 --> 00:10:35,420 This way with the alt key you can also access the menu item. If I do H now I access the home items 146 00:10:35,810 --> 00:10:39,850 and you can see all the shortcut keys present and you can apply them. 147 00:10:39,920 --> 00:10:49,150 So if I press E now I get to the clear items box and if I press A I get to clear everything. 148 00:10:49,160 --> 00:10:52,000 So you can use the alt keys to go through the menu. 149 00:10:52,050 --> 00:10:54,610 Let's just use control+Z to go back. 150 00:10:54,920 --> 00:11:00,170 Okay so what I suggest you do is take some time and practice some of these shortcuts.