1 00:00:00,000 --> 00:00:05,067 [MUSIC] 2 00:00:05,067 --> 00:00:09,860 In this stage, we'll be covering some common functions and keyboard shortcuts. 3 00:00:09,860 --> 00:00:14,570 Many of the videos in the stage are short and covers specific functions quickly. 4 00:00:14,570 --> 00:00:17,420 We did that so you can easily come back and reference them if you like. 5 00:00:17,420 --> 00:00:22,410 I also want to emphasize that we do not cover all the different uses for 6 00:00:22,410 --> 00:00:23,720 these functions. 7 00:00:23,720 --> 00:00:25,680 As you get more comfortable with spreadsheets, 8 00:00:25,680 --> 00:00:30,070 I'm sure you'll find additional creative uses for these functions. 9 00:00:30,070 --> 00:00:34,160 The functions you use most regularly will depend a lot on the job role you're in 10 00:00:34,160 --> 00:00:36,150 as well as your particular industry. 11 00:00:36,150 --> 00:00:40,470 For example, if you're in finance or investing, you probably use financial 12 00:00:40,470 --> 00:00:45,530 functions like IRR to calculate the internal rate of return, or 13 00:00:45,530 --> 00:00:49,990 NPV, to calculate the net present value of an investment. 14 00:00:49,990 --> 00:00:52,680 We won't cover specialized functions like those, but 15 00:00:52,680 --> 00:00:56,020 just keep in mind, there are many, many functions. 16 00:00:56,020 --> 00:00:58,750 Some of which may be just right for your job. 17 00:00:58,750 --> 00:00:59,410 Let's get started. 18 00:01:00,630 --> 00:01:04,780 Earlier on in this course, we used the sum, average and median functions. 19 00:01:04,780 --> 00:01:07,940 Let's review the sum function to highlight the key parts of using 20 00:01:07,940 --> 00:01:09,640 any spreadsheet function. 21 00:01:09,640 --> 00:01:11,850 I start by typing in the equal sign in a cell. 22 00:01:13,630 --> 00:01:17,040 Think of the equal sign as saying the value inside the cell is 23 00:01:17,040 --> 00:01:18,220 equal to something. 24 00:01:18,220 --> 00:01:21,390 That something in this case is the result of a function. 25 00:01:21,390 --> 00:01:22,514 To select a function, 26 00:01:22,514 --> 00:01:25,779 you just start typing the first letters of the function's name. 27 00:01:25,779 --> 00:01:30,036 I want to use the sum function, so 28 00:01:30,036 --> 00:01:34,303 I enter SUM and parentheticals. 29 00:01:34,303 --> 00:01:38,363 You can see right below the cell a box pops up that shows a bunch of other 30 00:01:38,363 --> 00:01:41,220 functions that are similar to sum. 31 00:01:41,220 --> 00:01:44,200 It also explains what the sum function does. 32 00:01:44,200 --> 00:01:48,930 So if I write IF, the list 33 00:01:48,930 --> 00:01:54,720 shrinks a lot because a bunch of different functions that have sum disappeared. 34 00:01:54,720 --> 00:01:59,630 Going back to SUM though, when I add parenthesis, you can see the function 35 00:01:59,630 --> 00:02:05,550 syntax shows up and then descriptions of what value is supposed to be. 36 00:02:05,550 --> 00:02:08,590 Sometimes you won't see this appear and 37 00:02:08,590 --> 00:02:13,120 that's because you've deselected this functionality. 38 00:02:13,120 --> 00:02:16,830 Instead you'll just see a blue question mark there. 39 00:02:17,910 --> 00:02:23,930 So to turn formula help back on, just click this question mark, and there it is. 40 00:02:23,930 --> 00:02:26,940 You can turn it on by clicking that question mark, and 41 00:02:26,940 --> 00:02:31,360 you can hide this formula help by clicking this x or 42 00:02:31,360 --> 00:02:34,760 using Shift+F1 keyboard shortcuts. 43 00:02:34,760 --> 00:02:38,330 So I want to calculate the sum of values in other cells. 44 00:02:38,330 --> 00:02:42,340 We can do this in many different ways, but let's start with two common methods, 45 00:02:42,340 --> 00:02:46,080 selecting individual cells and selecting a range of cells. 46 00:02:46,080 --> 00:02:52,592 First, I can individually select each cell like so, choosing the cell, 47 00:02:52,592 --> 00:02:57,646 then putting a comma, then the next cell, and so forth. 48 00:03:00,057 --> 00:03:04,730 This is a bit tedious since I need to manually enter every cell I want to sum. 49 00:03:04,730 --> 00:03:07,454 Or, I can select the entire range of cells. 50 00:03:11,980 --> 00:03:13,587 Notice what happens here. 51 00:03:13,587 --> 00:03:19,510 You see the first cell, then a colon, then the last cell. 52 00:03:19,510 --> 00:03:22,510 That can go across multiple columns and rows. 53 00:03:22,510 --> 00:03:26,634 So I can type the next row number here, and 54 00:03:26,634 --> 00:03:29,460 you can see the function is now summing both rows. 55 00:03:29,460 --> 00:03:32,040 Most good spreadsheet software will use colors and 56 00:03:32,040 --> 00:03:35,790 highlighting to show you what you have entered into a function or formula. 57 00:03:35,790 --> 00:03:40,910 In this case, we see E3:H4 and 58 00:03:40,910 --> 00:03:45,980 those are orange, and then what happens if I select these? 59 00:03:45,980 --> 00:03:49,990 So you can see these are purple and it's purple in your formula bar as well. 60 00:03:49,990 --> 00:03:54,620 So if you want to select individual cells, use a comma between each cell reference, 61 00:03:54,620 --> 00:03:57,360 but a colon lets you select within a range. 62 00:03:57,360 --> 00:04:01,290 All the cells from the first cell reference to the second cell reference. 63 00:04:01,290 --> 00:04:04,035 In this case, I am manually entering that range. 64 00:04:04,035 --> 00:04:11,980 So E3: and I want it to select everything all the way out to H3. 65 00:04:13,840 --> 00:04:19,540 Did you see that where I had no row number it was selecting everything in the column, 66 00:04:19,540 --> 00:04:22,482 but if I had the three there it confines it.