Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:02,050 --> 00:00:07,450 Here's my favorite list of shortcuts when designing reports or dashboards in Excel. 2 00:00:07,720 --> 00:00:12,200 I use this often when I work in Excel especially when I'm designing reports. 3 00:00:12,310 --> 00:00:14,350 Let me quickly take you through them. 4 00:00:14,350 --> 00:00:16,480 CTRL+SHIFT+C and CTRL+SHIFT+V 5 00:00:16,480 --> 00:00:24,340 are great shortcuts not just for Excel but I use them often in Powerpoint as well and they copy the object 6 00:00:24,490 --> 00:00:25,960 attributes. 7 00:00:25,960 --> 00:00:33,150 For example, if you have an object inserted here... Let's put in a rectangle. 8 00:00:33,360 --> 00:00:40,030 Just going to copy and paste it two times and change the formatting of the last one. 9 00:00:40,270 --> 00:00:41,880 Let's add a text. 10 00:00:45,710 --> 00:00:59,180 Make it red and change the color of this to gray and no shape outline. The text of these is white 11 00:00:59,180 --> 00:01:01,850 so one of them has text, one of them doesn't have text. 12 00:01:01,930 --> 00:01:07,870 If I would copy and paste this, I'm going to copy that whole object and paste it. But I just want to copy 13 00:01:07,870 --> 00:01:09,150 the attributes of this. 14 00:01:09,160 --> 00:01:16,750 I'm going to click on it and do CTRL+SHIFT+C and click on this and then press CTRL if you want 15 00:01:16,750 --> 00:01:21,110 to select more objects and highlight the other object and do CTRL+SHIFT+V. 16 00:01:21,120 --> 00:01:25,560 That works great in Powerpoint as well. 17 00:01:26,510 --> 00:01:29,590 To select all objects, you can press CTRL+A 18 00:01:29,910 --> 00:01:32,100 but you need to click on an object first. 19 00:01:32,100 --> 00:01:35,020 If you're just in a cell and you do CTRL+A, 20 00:01:35,160 --> 00:01:36,570 it highlights everything. 21 00:01:36,720 --> 00:01:41,970 If you want to highlight all your objects, click on an object and then press CTRL+A and it selects 22 00:01:42,000 --> 00:01:47,210 all your objects. One way that you can also get a list of all the objects that's used, 23 00:01:47,250 --> 00:01:51,410 you can go to Find & Select > Selection pane 24 00:01:51,600 --> 00:01:54,140 and here, you have the full list. 25 00:01:54,240 --> 00:01:56,790 These must be the rectangles that I just added. 26 00:01:56,820 --> 00:01:59,440 If I click on Hide, 27 00:01:59,850 --> 00:02:01,620 You can see they disappear. 28 00:02:01,620 --> 00:02:03,200 I can unhide them here. 29 00:02:03,300 --> 00:02:08,880 If you have a lot of objects and it's difficult to click on them, you go to here and then highlight them 30 00:02:08,880 --> 00:02:11,290 here, press CTRL and highlight more 31 00:02:11,350 --> 00:02:16,950 from here. It's sometimes easier to work here than directly on the objects in the spreadsheet especially 32 00:02:16,950 --> 00:02:19,530 if you have a few on top of each other. 33 00:02:19,590 --> 00:02:24,020 I can also highlight them from here and press Delete. This one 34 00:02:24,020 --> 00:02:26,690 I use a lot. It's to format cells. 35 00:02:26,760 --> 00:02:33,630 You can get to it either by right mouse clicking and going to Format Cells or by just pressing CTRL+1 36 00:02:33,630 --> 00:02:41,430 and going to the Format Cells. F4 does a lot of things. One of the things it does is to repeat 37 00:02:41,520 --> 00:02:43,590 the last formatting action. 38 00:02:43,770 --> 00:02:49,080 Let's say you have 333 here. One formatting I'm going to do with this with CTRL+1 39 00:02:49,080 --> 00:02:52,070 is to add decimal places to this 40 00:02:52,110 --> 00:02:56,410 and then what I'm going to do is to make it this color. 41 00:02:56,640 --> 00:03:02,940 If I go to these cells and press F4 and F4 and F4, it puts the color. 42 00:03:02,960 --> 00:03:06,230 Let's take a look if it puts the formatting of the number. 43 00:03:06,360 --> 00:03:07,310 It doesn't do that. 44 00:03:07,320 --> 00:03:14,040 It just repeats the last formatting action and I use that quite often when I finished a table design 45 00:03:14,100 --> 00:03:18,270 and then I add borders to that table with specific colors. 46 00:03:18,330 --> 00:03:24,090 I just go to the other cells and press F4 and it just copies the borders but leaves the rest of the 47 00:03:24,090 --> 00:03:25,400 formatting alone. 48 00:03:25,720 --> 00:03:31,880 The other useful one is to copy formulas without formatting using F2 and CTRL+ENTER. 49 00:03:32,340 --> 00:03:36,140 Let's say here, I have a formula. 50 00:03:36,210 --> 00:03:40,070 It equals this number. 51 00:03:40,190 --> 00:03:42,020 I'm going to fix it with F4. 52 00:03:42,020 --> 00:03:48,860 This is another great shortcut. The F4 key fully fixes your cell references. For instance, if you click 53 00:03:48,860 --> 00:03:49,840 it again, 54 00:03:50,010 --> 00:03:51,670 it semi-fixes just a row 55 00:03:51,680 --> 00:03:57,290 and if you click it again, it fixes just the column and then it leaves everything relative. 56 00:03:57,290 --> 00:04:02,020 and then once again, you click it, it has the double dollar signs in there. 57 00:04:02,300 --> 00:04:09,230 I'm going to say equals this plus row E1 which is going to return 1 58 00:04:09,350 --> 00:04:12,660 but when I drag it down, it's going to return 2 and 3. 59 00:04:12,680 --> 00:04:17,050 This is a formula that we're going to cover later on in the Formulas toolbox. 60 00:04:17,060 --> 00:04:19,300 Let's say I would drag this down. 61 00:04:19,300 --> 00:04:19,490 . 62 00:04:19,580 --> 00:04:23,550 It copies the formatting of the first cell along with it. 63 00:04:23,660 --> 00:04:29,420 What if I didn't want that? What I have to do is to highlight the area that I want the formula to be 64 00:04:29,420 --> 00:04:30,360 copied to. 65 00:04:30,650 --> 00:04:38,510 Then, click F2. You see, it includes the first cell that has my formula and then press CTRL+ENTER. 66 00:04:39,620 --> 00:04:42,440 That copies the formula without the formatting. 67 00:04:43,070 --> 00:04:49,380 Another thing that bothered me for a while is drawing a straight line from shapes. If you try that, 68 00:04:49,440 --> 00:04:55,420 you can see that it can be a challenge sometimes when you go like this to get it really straight. 69 00:04:55,620 --> 00:04:59,240 And it's not just in Excel but in Word and PowerPoint as well. 70 00:04:59,250 --> 00:05:07,780 So one way around this is to go to Insert, to go to your line, click on it, but before you start drawing it, hold 71 00:05:07,780 --> 00:05:10,540 down the SHIFT key and then draw it. 72 00:05:10,640 --> 00:05:13,990 And that forces the line to be straight. 73 00:05:14,000 --> 00:05:18,120 Another one is to insert bullet points in text. 74 00:05:18,300 --> 00:05:25,230 You can insert bullet points by going to Insert > Symbol or finding the bullet points symbol and inserting 75 00:05:25,230 --> 00:05:28,700 it in your formula or in your text. 76 00:05:28,940 --> 00:05:32,320 But you can also use the ALT+7 shortcut key. 77 00:05:32,350 --> 00:05:37,550 I put a note here that it only works on a larger keyboard that has a numeric keypad. 78 00:05:37,550 --> 00:05:42,290 If you're using a laptop, you need to enable Number Lock first. 79 00:05:42,500 --> 00:05:48,980 But some laptops just don't have that functionality. The laptop I'm using right now to film this doesn't 80 00:05:48,980 --> 00:05:54,560 have this functionality so I can't show it to you. But try it on your desktop computer or if you have a 81 00:05:54,560 --> 00:05:59,620 keyboard, try that. It will insert bullet points in text. 82 00:05:59,640 --> 00:06:06,330 One other one that can be a bit frustrating is trying to move charts with the arrow keys. 83 00:06:06,540 --> 00:06:10,820 Let's go here and let's just insert a mini chart. 84 00:06:12,700 --> 00:06:17,610 If you just try to move this with the arrow keys, this is what happens. 85 00:06:18,380 --> 00:06:24,050 It moves through the different chart elements but it doesn't actually move your chart. The way to move 86 00:06:24,050 --> 00:06:24,620 it is 87 00:06:24,650 --> 00:06:31,730 you have to press down CTRL and then click on the edges of your chart to get these circles and now, 88 00:06:31,730 --> 00:06:34,380 you can move it with the arrow keys. 89 00:06:35,380 --> 00:06:41,200 Another way that you can make sure that you're moving it in a straight fashion is when you're moving this with 90 00:06:41,200 --> 00:06:42,230 the mouse keys, 91 00:06:42,340 --> 00:06:49,480 hold down the SHIFT key and then it locks it into the rows and the column cell. You can see, it moves it 92 00:06:49,480 --> 00:06:50,810 straight over 93 00:06:50,940 --> 00:06:59,180 then it moves this straight down over. These two can come in handy when you work with charts. 94 00:06:59,180 --> 00:07:05,060 Another one is the ALT+ENTER key that inserts a paragraph inside a cell. 95 00:07:05,090 --> 00:07:09,620 Let's say here, I wanted to insert a paragraph. When I press enter, 96 00:07:09,620 --> 00:07:14,670 it leaves the formula bar. To make it insert a paragraph inside the cell, 97 00:07:14,720 --> 00:07:21,380 I need to press the ALT and the ENTER key. Here are some basic ones for navigation. 98 00:07:21,500 --> 00:07:22,980 Let's go back to here. 99 00:07:23,120 --> 00:07:30,050 If you want to jump to the end to the last cell in your table, press CTRL and then the down arrow 100 00:07:30,050 --> 00:07:31,850 key. Press CTRL then 101 00:07:31,910 --> 00:07:39,800 up arrow key to jump up. If you want to highlight everything on the way, press CTRL+SHIFT+↓ and 102 00:07:39,800 --> 00:07:42,590 then press CTRL+SHIFT and the right arrow key. 103 00:07:42,590 --> 00:07:45,920 and then you highlight your whole table. CTRL+SHIFT+↑, 104 00:07:46,040 --> 00:07:56,300 you go back up. If you want to move through your tabs, you can press CTRL and then PAGE UP and CTRL, 105 00:07:56,300 --> 00:07:57,350 PAGE DOWN. 106 00:07:58,050 --> 00:08:04,500 F2 is a great one when you want to edit your formula. If you want to edit your formula right now, 107 00:08:04,590 --> 00:08:11,310 you press the F2 key and it edits it in the cell. If you don't want to make changes to your formula, 108 00:08:11,310 --> 00:08:14,980 you can press ESC to leave your formula. 109 00:08:14,980 --> 00:08:21,480 I already covered the F4 key that allows you to change the cell references between absolute and relative 110 00:08:21,480 --> 00:08:22,130 cell references. 111 00:08:22,140 --> 00:08:27,800 That's the one that puts the dollar sign (this one) on your cell references. 112 00:08:29,120 --> 00:08:34,340 Another good one that can come in handy when you write larger formulas and you want to debug and 113 00:08:34,340 --> 00:08:37,600 track your formula is the F9 key. 114 00:08:37,740 --> 00:08:45,010 If I go to one of these where I have a formula, you can highlight parts of your formula and then press 115 00:08:45,010 --> 00:08:52,220 the F9 key. It actually translates it to what is in those references. 116 00:08:53,330 --> 00:08:57,860 Press CTRL+Z to go back or ESC to leave. 117 00:08:57,860 --> 00:09:04,190 In this case, it says saying this sumifs plus this sumifs, I can highlight this and then press 118 00:09:04,190 --> 00:09:05,940 the F9 key and I know 119 00:09:06,030 --> 00:09:12,460 this is the number is the result of the second sumifs and then press ESC to leave or CTRL+Z 120 00:09:12,460 --> 00:09:13,610 to go back 121 00:09:13,610 --> 00:09:17,770 because if you press enter, it keeps that number in your formula. 122 00:09:19,120 --> 00:09:22,210 CTRL+Z I just covered is to go back. 123 00:09:22,420 --> 00:09:30,880 One that I missed out on for quite a while was the double click Format Painter. You probably know 124 00:09:30,880 --> 00:09:36,970 the Format Painter that if you click on a cell, click on the formatting, it copies that formatting to 125 00:09:37,000 --> 00:09:38,020 another cell. 126 00:09:38,230 --> 00:09:45,760 But, if you want to reuse that formatting on different cells, all you have to do is double click really 127 00:09:45,760 --> 00:09:53,680 quickly on the Format Painter icon and then you can reuse that on other cells. When you're done, you can 128 00:09:53,680 --> 00:09:59,470 just press ESC to leave. The other one that can come in really handy is the Clipboard. 129 00:09:59,470 --> 00:10:05,620 I have it in CTRL+C+C so if you press C twice but it might not work for you for the first time, 130 00:10:05,710 --> 00:10:08,360 you just have to activate an option. 131 00:10:08,530 --> 00:10:11,980 This is the Clipboard. I'm just going to open my Clipboard. 132 00:10:12,210 --> 00:10:19,150 Anything you copy gets added to the clipboard. If I go in this cell and press CTRL+C, it's added 133 00:10:19,270 --> 00:10:20,610 to my Clipboard. 134 00:10:20,740 --> 00:10:22,540 Let's say I also want this number. 135 00:10:22,540 --> 00:10:25,980 I'm going to do CTRL+C and I might even want a formula. 136 00:10:26,000 --> 00:10:33,240 I'm going to do CTRL+C. They are all added to my Clipboard and I can reuse them when I'm writing a formula. 137 00:10:33,610 --> 00:10:42,400 I could say it equals this plus this. 138 00:10:42,430 --> 00:10:47,080 There is an option to make CTRL+C+C work and that's this option. 139 00:10:47,080 --> 00:10:53,260 Make sure you have a tick mark where it says "Show Office Clipboard when Ctrl+C Pressed Twice" 140 00:10:53,620 --> 00:10:56,440 You can see you can toggle it on and off. 141 00:10:56,450 --> 00:11:05,200 If I close this and clear it all, whenever I press CTRL+C+C, it opens my Clipboard. 142 00:11:05,230 --> 00:11:08,830 That's it! These are the list of my favorite shortcuts in Excel. 143 00:11:08,890 --> 00:11:14,680 I suggest you pick a few of these shortcut keys and use them immediately in your files. You can also 144 00:11:14,680 --> 00:11:18,170 print the list out from the demo workbook and keep it handy. 145 00:11:18,430 --> 00:11:23,680 On the one side, we have the shortcut keys to help us be faster and in the next lecture, I'm going to 146 00:11:23,680 --> 00:11:27,380 show you some additional tools that you should be using 147 00:11:27,490 --> 00:11:29,820 if you use Excel on a frequent basis. 15542