Formula Anatomy

Quick reference

• 00:04 One of the most important features in Excel are formulas.
• 00:09 Well, what the heck is a formula anyway?
• 00:12 What a formula is, it's essentially Excel's way of working through
• 00:16 an equation to come back with a result.
• 00:20 The nice thing about this is that we can feed it all kinds of different pieces.
• 00:23 Some of them numbers, some of them text.
• 00:25 And Excel can react to it in different ways to give us different outcomes,
• 00:29 which is really, really neat.
• 00:31 One thing that we have to remember about formulas is that
• 00:34 every single formula in Excel always starts with the = character.
• 00:38 And this is the way that we can tell whether or not something is a formula.
• 00:42 If it starts with =, it's a formula.
• 00:44 If it doesn't, it's not, it's just that simple.
• 00:46 One of the interesting pieces, though, is that we can actually build formulas.
• 00:50 Either by starting with the = key, or with a couple of special characters as well.
• 00:55 One of them is the- key.
• 00:57 But the- key, once you type in -5 and Enter, will actually convert,
• 01:02 in the formula bar, to =-5.
• 01:04 So it always puts that = sign in front.
• 01:07 The same is true if you start a formula using the + sign,
• 01:12 +5+2 would return =5+2 in your formula bar later.
• 01:18 Now formulas, as I mentioned, can be used to do all kinds of cool things.
• 01:21 We can start with simple things, like adding two numbers together, =1+5.
• 01:27 The only different here between what you would type in your calculator and
• 01:32 what you would say to Excel is you start in a cell and say =.
• 01:35 And then the equation that you want.
• 01:37 Where, normally, when we work with a calculator, we're more like to say 1+5=.
• 01:42 So it works a little bit backwards.
• 01:44 But the reason is because Excel already has the cell to hold the answer.
• 01:48 And it wants to know what that cell should be = to.
• 01:51 So that's how it sort of works through its stuff.
• 01:54 The other thing that we should remember with this is that, and
• 01:56 this is where things start to get really powerful.
• 01:59 Is that we can actually use ranges to actually feed our formulas.
• 02:03 So if A1 contains the value of 1, and B1 contains the value of 5.
• 02:08 Then this particular formula =A1+B1 will evaluate to =1+5,
• 02:14 and would give us the answer of 6.
• 02:18 We could also perform much more complex operations.
• 02:21 And this is also very good, because Excel actually follows the order of operations.
• 02:26 Whether you refer to it as BODMAS or PEMDAS or anything else.
• 02:30 It works through its brackets first, and then it's exponents, and
• 02:33 then it's multiplication, division, eventually addition and subtraction.
• 02:36 And we'll look at that in more advanced videos.
• 02:38 But for right now why don't we go and
• 02:40 look at the very basics inside Excel as to how formulas actually work.
• 02:46 So let's start with the very basics here.
• 02:48 And I want to set cell B7 =1+5.
• 02:54 Remember, we always want to start every formula with an =.
• 02:58 And when we look at this, 1+5, we add those two together, we should get 6, and
• 03:02 indeed we do.
• 03:04 Let's do the next one, =8-6, which we hope would return 2.
• 03:09 And naturally it does.
• 03:11 Now for more complicated mathematics,
• 03:13 we're gonna write the same kind of a formula.
• 03:15 We'll say =(1+5), and then -2.
• 03:22 And remember the multiplication is an asterisk, Shift+8, times 2.
• 03:28 Now, again this will follow the order of operations.
• 03:31 So it should go within the parenthesis first, 1+5 is 6.
• 03:35 And then we'll have -, but the 2*2 needs to be evaluated before we can subtract it.
• 03:40 So that'll give us 4.
• 03:41 So we should have 6-4=2, and Enter, and that is exactly what we get.
• 03:46 Now for the final value set here, I'm gonna go straight with -8/2.
• 03:53 Notice I have not put an = sign in front of this.
• 03:56 And when I say Enter, it still returned -4.
• 04:00 And if I go back and look at a formula,
• 04:02 notice that it actually does have an = prepended to it.
• 04:06 So if you put in a -, Excel is gonna automatically try and
• 04:10 convert it to be a formula.
• 04:13 Now let's amp this up a bit, let's go and start using ranges.
• 04:17 Now we've got the same formulas here, but we've got inputs in cells.
• 04:21 Because of course the challenge is if anything changes over here
• 04:24 these formulas will stay static.
• 04:26 Because they're using hard coded numbers.
• 04:29 So in this case, we're gonna come down to B14.
• 04:32 And what we're gonna do is we're gonna say =.
• 04:35 And we'll choose C14+D14.
• 04:41 Again, this is still 1+5.
• 04:43 And when we hit Enter, it still returns 6.
• 04:47 The beauty is through, if I were to change C14 to 2 and
• 04:51 hit Enter, it now calculates to 7.
• 04:54 I'm gonna press Ctrl + Z to undo it.
• 04:56 And you'll see that it goes back to 6.
• 05:00 Let's do B15.
• 05:01 We'll say =, we'll take 8-, and we'll choose the 6 that's
• 05:07 living in D15, and we'll hit Enter, and we still get 2.
• 05:12 Now, let's test the mathematics here for a little more complicated things.
• 05:16 Open our parenthesis,
• 05:20 we'll say (C16+D16) -E16*F16.
• 05:29 Now we've used the same values that we used when we were actually calculating
• 05:33 B9 here.
• 05:34 So when I hit Enter we get the same result.
• 05:39 And finally, we'll say -, we'll choose to subtract C17.
• 05:44 And we'll divide it by D17, and we'll hit Enter.
• 05:50 And it again returns -4.
• 05:52 And once again, you'll notice that up in the top here it's gone and
• 05:56 it's actually put an = in front.
• 05:57 To turn it into a formula that will calculate automatically.
• 06:00 And at this point,
• 06:01 any change to any of these values that are in those cells we referred
• 06:04 to will automatically flow through these formulas to return the right result.

Lesson notes are only available for subscribers.