Formulas vs Functions

Quick reference

• 00:04 Let's start using Excel to do some practical tasks.
• 00:07 You might have heard about functions and formulas, and
• 00:11 maybe you've even heard them being used interchangeably.
• 00:13 Well, they're not exactly the same.
• 00:16 So what's a formula?
• 00:18 A formula is really a command to do a calculation created using one or more
• 00:23 of the basic math operations, addition, subtraction, multiplication, or division.
• 00:30 In Excel, a formula always starts with an equal sign.
• 00:34 When you create a formula,
• 00:35 it means that you are entering the math operators yourself.
• 00:39 You can create a formula using values, like this.
• 00:43 Let's start with our equal sign, as we mentioned before,
• 00:46 and let's say we want to add two numbers, 25 and 5.
• 00:50 We type =25, the plus sign and the number 5 and hit Enter.
• 00:56 The value is displayed in our cell and the formula is displayed in the formula bar.
• 01:03 What about subtraction?
• 01:04 That's easy too, =25, the dash sign for
• 01:09 our subtraction and our second number.
• 01:14 For multiplication we use an asterisk and for division we use a slash.
• 01:20 We typed our values directly into the cell, but
• 01:22 we could have also used cell references.
• 01:25 Let's say we wanted to add two numbers, the numbers in cell A1 and cell A2.
• 01:31 We could have typed =a1+a2.
• 01:36 So that's a formula.
• 01:38 On the other hand, a function is a prebuilt formula within Excel.
• 01:43 Functions save us from having to build a task ourselves.
• 01:47 So just like formulas, they do begin with an equal sign, but
• 01:50 then each function has a given name that's defined within Excel.
• 01:55 And then it's followed by open parentheses and
• 01:57 the arguments that are needed to do the calculation.
• 02:01 Once we're done entering the arguments within our function,
• 02:04 then we end with close parentheses and hit Enter, and the value is calculated.
• 02:08 Let's say we wanted to find the average of the four numbers that are in column A.
• 02:13 We would start with our equal sign and type the word average,
• 02:16 because that's the name of the function.
• 02:18 We open parentheses.
• 02:19 And notice that Excel helps us by telling us what the arguments are,
• 02:23 what's the syntax of this function?
• 02:25 It says that we need one number, and then a comma, and
• 02:29 then our second and third and so on numbers.
• 02:33 All the numbers after number one are in square brackets because Excel
• 02:36 is telling us that these arguments are optional.
• 02:39 So let's get started.
• 02:41 Our first number is the number in cell A1, we type a comma for
• 02:45 the second number and click cell A2 and so on, comma, A3, comma, A4.
• 02:51 Close parentheses, and we've got our average.
• 02:55 A quicker way of performing this average function
• 02:59 is to use the cells in column A as a range.
• 03:03 They're all next to each other, so we could have simply stated them as a range
• 03:06 instead of typing commas in between each number.
• 03:09 It works like this.
• 03:11 We type the equal sign and the function name, open parentheses,
• 03:16 click the first number in our range, hold all the way down,
• 03:20 we drag to the last number, release, close parentheses, and hit Enter.
• 03:26 We've got our average displayed as expected.
• 03:29 So you can see that having functions in Excel is pretty useful.
• 03:33 The thing is, there are hundreds of Excel functions and it's impossible to know
• 03:37 the name of every one, especially if you're just getting started.
• 03:40 And that's why the Insert Function shortcut is so handy.
• 03:44 Let's say you wanted to add the four numbers in column A, but
• 03:48 you weren't sure of the name of the function.
• 03:51 So here's how we can get Excel to insert the function
• 03:54 even if we don't know the name.
• 03:56 We'll click on the Insert Function icon and
• 03:59 a dialog box pops up saying type a description of what you want to do, and
• 04:04 then Excel will recommend a function to you.
• 04:07 So we know that we want to add the four numbers, so
• 04:10 we type add numbers, and three functions are recommended.
• 04:14 For each function that we select a short description of what the function does
• 04:19 is stated within the dialog box.
• 04:21 This one is the one we want.
• 04:23 It says the SUM function adds all the numbers in a range of cells.
• 04:27 That's what we want to do.
• 04:29 We can hit OK,
• 04:30 and another dialog box comes up with the function arguments that are required.
• 04:36 It says we need the first number.
• 04:38 We can click directly on the cells that we want added, so
• 04:42 the first number, and then for the second number, the third and so on.
• 04:46 While we're clicking these numbers, Excel is performing the calculation and
• 04:50 giving us the result.
• 04:51 It says the formula result will be 180.
• 04:54 So that's pretty neat.
• 04:56 And we hit OK, and the result is displayed here.
• 05:01 We look in the formula bar, and now the function is typed in.
• 05:05 Of course, now that we know the name of that function, we can remember it for
• 05:08 future reference if this is a function we'll be using frequently.
• 05:12 We can use a range the way we learned earlier.
• 05:15 So let's type =sum, open parenthesis, click on the first number,
• 05:23 drag all the way to the last number, close parenthesis, and Enter.
• 05:28 And there we have the SUM function performed as a range.
• 05:32 Let's say that we wanted to multiply this total by the number ten.
• 05:37 This cell already has values, so we really want to edit what's in the cell.
• 05:42 There are a couple of ways we can do that.
• 05:44 We can either click the formula bar directly and type what we want.
• 05:49 Remember, we want to multiply, so we type an asterisk and
• 05:52 the number ten and Enter, and the total is displayed.
• 05:57 Let's say we didn't want to do it that way.
• 05:59 We can Ctrl+Z to undo what we just did.
• 06:03 And there's another way to edit a cell that has values,
• 06:08 we can double-click on that cell.
• 06:11 And then we're inside the cell,
• 06:12 we click on the location that we want to start typing, and
• 06:15 we type our multiplication sign and the number we want to multiply by.
• 06:21 Let's say we didn't want to do it that way, there's a third way.
• 06:25 The easiest way to do this is to click on that cell and hit F2.
• 06:31 Then we're right in the cell and the cursor starts flashing at the end of
• 06:36 the values, we type our asterisk, the number ten, and Enter.
• 06:40 So we just learned the difference between functions and formulas.
• 06:44 We built our first function, our first formula, and
• 06:47 we even added extra arguments to our formula.
• 06:51 In our next lessons, we'll look at some practical applications of how doing all
• 06:55 these tasks can make our work easier.

Lesson notes are only available for subscribers.