Introducing Functions4:24 with Michael Watson
Functions are built-in calculations supported by most spreadsheet software. They let you calculate averages, sums, round up (or down) numbers, and even calculate values based on IF a certain condition is true. They're powerful spreadsheet features.
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
[MUSIC] 0:00 In this stage, we'll be covering some common functions and keyboard shortcuts. 0:05 Many of the videos in the stage are short and covers specific functions quickly. 0:09 We did that so you can easily come back and reference them if you like. 0:14 I also want to emphasize that we do not cover all the different uses for 0:17 these functions. 0:22 As you get more comfortable with spreadsheets, 0:23 I'm sure you'll find additional creative uses for these functions. 0:25 The functions you use most regularly will depend a lot on the job role you're in 0:30 as well as your particular industry. 0:34 For example, if you're in finance or investing, you probably use financial 0:36 functions like IRR to calculate the internal rate of return, or 0:40 NPV, to calculate the net present value of an investment. 0:45 We won't cover specialized functions like those, but 0:49 just keep in mind, there are many, many functions. 0:52 Some of which may be just right for your job. 0:56 Let's get started. 0:58 Earlier on in this course, we used the sum, average and median functions. 1:00 Let's review the sum function to highlight the key parts of using 1:04 any spreadsheet function. 1:07 I start by typing in the equal sign in a cell. 1:09 Think of the equal sign as saying the value inside the cell is 1:13 equal to something. 1:17 That something in this case is the result of a function. 1:18 To select a function, 1:21 you just start typing the first letters of the function's name. 1:22 I want to use the sum function, so 1:25 I enter SUM and parentheticals. 1:30 You can see right below the cell a box pops up that shows a bunch of other 1:34 functions that are similar to sum. 1:38 It also explains what the sum function does. 1:41 So if I write IF, the list 1:44 shrinks a lot because a bunch of different functions that have sum disappeared. 1:48 Going back to SUM though, when I add parenthesis, you can see the function 1:54 syntax shows up and then descriptions of what value is supposed to be. 1:59 Sometimes you won't see this appear and 2:05 that's because you've deselected this functionality. 2:08 Instead you'll just see a blue question mark there. 2:13 So to turn formula help back on, just click this question mark, and there it is. 2:17 You can turn it on by clicking that question mark, and 2:23 you can hide this formula help by clicking this x or 2:26 using Shift+F1 keyboard shortcuts. 2:31 So I want to calculate the sum of values in other cells. 2:34 We can do this in many different ways, but let's start with two common methods, 2:38 selecting individual cells and selecting a range of cells. 2:42 First, I can individually select each cell like so, choosing the cell, 2:46 then putting a comma, then the next cell, and so forth. 2:52 This is a bit tedious since I need to manually enter every cell I want to sum. 3:00 Or, I can select the entire range of cells. 3:04 Notice what happens here. 3:11 You see the first cell, then a colon, then the last cell. 3:13 That can go across multiple columns and rows. 3:19 So I can type the next row number here, and 3:22 you can see the function is now summing both rows. 3:26 Most good spreadsheet software will use colors and 3:29 highlighting to show you what you have entered into a function or formula. 3:32 In this case, we see E3:H4 and 3:35 those are orange, and then what happens if I select these? 3:40 So you can see these are purple and it's purple in your formula bar as well. 3:45 So if you want to select individual cells, use a comma between each cell reference, 3:49 but a colon lets you select within a range. 3:54 All the cells from the first cell reference to the second cell reference. 3:57 In this case, I am manually entering that range. 4:01 So E3: and I want it to select everything all the way out to H3. 4:04 Did you see that where I had no row number it was selecting everything in the column, 4:13 but if I had the three there it confines it. 4:19
You need to sign up for Treehouse in order to download course files.Sign up