Locked lesson.
About this lesson
Use to understand the anatomy of Excel functions, and what their components mean.
Exercise files
Download this lesson’s related exercise files.
Function Anatomy.xlsx13.2 KB Function Anatomy - Completed.xlsx
13.4 KB
Quick reference
Topic
Understanding Excel Function Anatomy
Where/when to use the technique
Use to understand the anatomy of Excel functions, and what their components mean.
Instructions
What is a Function?
- A pre-packaged algorithm which accepts parameters to return a result.
Key points to remember
- Functions must be used inside formulas.
- The function name is always followed by ( )
- The parameters for the current function show in Excel’s “Intellisense”.
- The current parameter is always listed in bold.
- Optional parameters are surrounded by [ ]
- Parameters not enclosed in [ ] are required.
- Ranges can be used as parameters.
- Other functions can be used as parameters.
- 00:04 In this video we're gonna talk about Excel functions.
- 00:08 It's important to understand that Excel functions and formulas,
- 00:12 although often referred to as the same thing, are actually very different.
- 00:16 What a function is, is it's a prepackaged algorithm
- 00:20 that allows us to pass certain parameters into it and get a result back out.
- 00:26 Now, the interesting part is that they work very well with formulas.
- 00:29 As a matter of fact, in order to use a function, it has to be inside a formula.
- 00:33 And we can use multiple functions or only one function, or
- 00:37 none at all inside a formula to get the results we need.
- 00:41 Every function has some key parts to it.
- 00:44 It has a function name, it'll have required parameters and
- 00:47 potentially optional parameters as well.
- 00:50 And this is taking from the IntelliSense help that we see that
- 00:53 pops up inside of excel.
- 00:54 You"ll notice that the function name is always followed by an open brackets,
- 00:59 and then the list of any parameters, followed by a closing bracket.
- 01:03 The current parameter is always listed in bold.
- 01:06 So when you look at the sum function here, you can assume that we actually snapped
- 01:09 a picture of this as we were working on number one, which is the first parameter.
- 01:15 Optional parameters are surrounded by the square brackets.
- 01:19 So, in this case, number two and anything after it, the comma dot dot dot,
- 01:24 allows us to know that we don't have to provide a number two if we don't want to.
- 01:29 But we also know by default then, that anything that doesn't have square brackets
- 01:33 around it, it's the case of number1 here, is not optional, it's required.
- 01:39 Something that's also important to understand about functions is that we can
- 01:42 use ranges as these parameters.
- 01:44 We can use regular numbers, we can use text, or
- 01:47 we can use ranges depending on what the function needs.
- 01:50 So let's go take a look in Excel and see how we can actually
- 01:54 use a function in order to build a good formula to work with.
- 01:59 Okay, so here we are in Excel, let's see what we can do here.
- 02:03 If we wanted to leverage a function to sum the numbers above cell B7 here,
- 02:08 what we would do is we would start by pressing the equals key because every
- 02:13 function must exist inside a formula.
- 02:15 Then, what we're gonna do is we're gonna start typing the function name
- 02:18 that we want.
- 02:18 So, I'm gonna type in an S and you'll see that excel comes along and it actually
- 02:23 gives us a list of all of the functions that it believes start with an S and
- 02:27 are ready to go.
- 02:28 And if I type a U now, it'll start nailing down this list and making it smaller.
- 02:33 I could use my arrow keys to arrow down to the one I want.
- 02:37 Or I could keep typing it and I can press tab once I found the one that I want.
- 02:42 You'll notice that as soon as I press tab it puts the opening,
- 02:45 closing parenthesis around and now its got the IntelliSense showing up underneath.
- 02:49 It says this one's bold.
- 02:51 You are currently working with number one.
- 02:53 And it is required.
- 02:54 I can see that number two and so forth are not required.
- 02:58 So, it says what numbers would you like?
- 02:59 Well, what I'm gonna do is I'm gonna grab the mouse and
- 03:01 I'm gonna just select cells B5 and 6.
- 03:04 And you'll notice that it gives me B5 colon B6.
- 03:08 Now, the colon is Excel's shorthand to say from here to here.
- 03:13 So, I like to remember this as B5 up to, there's an up two dots, up to B6.
- 03:18 And it's gonna sum everything in between.
- 03:21 Now, at this point, it's got a closing parenthesis on it.
- 03:25 I can hit Enter, and you'll see that the formula works quite nicely.
- 03:29 Of course, I could type this all manually if I wanted.
- 03:33 I don't need to use the mouse.
- 03:34 I could type equals SUM, open parenthesis.
- 03:38 I could then arrow up to C5.
- 03:41 I could type, or hold down my shift key.
- 03:44 Actually, let's do that first and arrow down.
- 03:46 And that automatically puts in the colon and gives me the C6.
- 03:50 I'll close my parenthesis and
- 03:52 hit enter and you'll see that that formula works as well.
- 03:56 What if I wanted to sum across?
- 03:59 Well, I could say equals SUM, open parentheses.
- 04:01 Lets go over to this guy here and I'm gonna type in a colon now.
- 04:07 That takes me back to cell D5.
- 04:09 So, now I'm gonna arrow one to the left, close my parenthesis and hit Enter.
- 04:16 So you see that there's many, many ways to do this.
- 04:19 Of course, now that I have this,
- 04:21 you already know that we can fill the formula down as well.
- 04:24 So that's gonna work nicely.
- 04:26 I could also fill this formula across if I wanted to do that.
- 04:30 And you'll see that because it's a relative formula,
- 04:33 it's actually picking up quite nicely to sum D5 to D6.
- 04:37 What's interesting here is because these are relative formulas, I can now go and
- 04:41 take this information.
- 04:43 I can right click and copy it.
- 04:46 And I can paste it over the total cost of sales line.
- 04:50 The relative formulas using the sum function you'll notice that I've
- 04:53 selected here, and it's summing up what I have above, which is great.
- 04:58 I could also go and copy these two guys here.
- 05:01 Well, let's use Command, C to copy them this time.
- 05:04 I'll select here, Command, V will paste both of those cells in as well, and
- 05:08 that looks great.
- 05:09 Now, here's the next challenge, though.
- 05:12 What if I want to go in and
- 05:14 add up sums of numbers that are not in a contiguous block?
- 05:18 They're separated.
- 05:19 Because right now I'd like to sum this number positive but
- 05:22 I'd like to subtract this number from it.
- 05:24 But I'd like to use the sum function.
- 05:26 Can I do that?
- 05:26 Well, I absolutely can.
- 05:28 We can type in equals SUM, open parenthesis.
- 05:31 And it says what's number one?
- 05:32 And I say, well I want to use B7 as number one.
- 05:38 To move to the next parameter, what we're gonna do is we're gonna hit comma.
- 05:42 And you'll notice now that number two, even though it's in square brackets, is
- 05:45 now lit up in the IntelliSense in bold and it says all right this is now required.
- 05:49 Because you've put a comma in here, you have to have this.
- 05:52 And it's also providing me with an additional Number3 dot, dot, dot.
- 05:55 So if I were to hit the comma again,
- 05:57 this would now be, Sum Number1, Number2, Number3, Number 4 dot, dot, dot.
- 06:02 So the dot, dot, dot tells us we can do this ad infinitum.
- 06:06 So, the next number I wanna add though is negative so I put in my minus sign, B12.
- 06:14 So this will look at B7 and say, all right, no problem.
- 06:18 And give me negative of B12 and then it'll sum them together.
- 06:21 If I close the parentheses on there and
- 06:24 say enter, you'll notice that it indeed does exactly that.
- 06:27 And again, of course, we could go and copy this across to the right and
- 06:31 everything will work nicely.
- 06:35 You'll notice there's also another practice page in here where you can go and
- 06:38 practice building some different pieces here to use the sum function and whatnot.
- 06:41 And there's a solution on the solutions page.
- 06:44 I also wanna give you just a little bit of a heads up about the test that's
- 06:47 coming up.
- 06:48 You will be tested on different functions that you haven't seen yet.
- 06:51 And this is intentional because the big key piece that we wanna make sure we
- 06:54 understand here is what the IntelliSense means and what the parameters mean when
- 06:59 you're working with different functions that you may not have experienced before.
- 07:03 So just be ready for that as you go through.
- 07:05 All the concepts that we've looked at here are equally applicable but
- 07:08 they are gonna be different functions to really test that knowledge
Lesson notes are only available for subscribers.