Formula Anatomy

Quick reference

• 00:05 >> One of the most important features in Excel are formulas.
• 00:10 Well, what the heck is a formula anyway?
• 00:12 What a formula is, is it's essentially Excel's way of
• 00:16 working through 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:24 some of them numbers, some of them text, and Excel can react to it in different
• 00:27 ways to give us different outcomes, which is really, really, neat.
• 00:32 One thing that we have to remember about formulas is that
• 00:35 every single formula in Excel always starts with the equals character.
• 00:40 And this is the way that we can tell whether or not something is a formula.
• 00:43 If it starts with equals, it's a formula.
• 00:45 If it doesn't, it's not.
• 00:46 It's just that simple.
• 00:48 One of the interesting pieces, though, is that we can actually build formulas,
• 00:52 either by starting with the equals key, or
• 00:54 with a couple of special characters as well.
• 00:57 One of them is the minus key.
• 00:59 But the minus key, once you type in minus five and enter,
• 01:02 will actually convert in the formula bar to equals minus five.
• 01:06 So it always puts the equals sign in front.
• 01:09 The same is true if you start a formula using the plus sign.
• 01:13 Plus five, plus two, would return equals five plus two in your formula bar later.
• 01:19 Now formulas, as I mentioned, can be used to do all kinds of cool things.
• 01:23 We can start with simple things like adding two numbers together,
• 01:28 equals one plus five.
• 01:29 The only difference here between what you would type into your calculator and
• 01:33 what you were to say to Excel is you start in a cell and say equals, and
• 01:38 then the equation you want.
• 01:39 Where normally when we work with a calculator we're more likely to say one
• 01:42 plus five equals.
• 01:43 So it works a little bit backwards.
• 01:46 But the reason is because Excel already has the cell to hold the answer, and
• 01:50 it wants to know what that cell should be equal to.
• 01:53 So that's how it sort of works through it's stuff.
• 01:55 The other thing that we should remember with this is that, and
• 01:58 this is where things start to get really powerful,
• 02:00 is that we can actually use ranges to actually feed our formulas.
• 02:05 So if A1 contains the value of one, and B1 contains the value of five,
• 02:10 then this particular formula equals A1 plus B1 will evaluate to
• 02:14 equals one plus five and would give us the answer of six.
• 02:19 We can also perform much more complex operations.
• 02:23 And this is also very good because Excel actually follows the order of operations,
• 02:28 whether you refer to it as BODMAS or PEMDAS, or anything else,
• 02:31 it works through its brackets first and then it's exponents and
• 02:35 then it's multiplication, division eventually, addition, subtraction.
• 02:38 We'll look at that in more advanced videos.
• 02:40 But for right now, why don't we go and
• 02:42 look at the very basics inside Excel as to how formulas actually work?
• 02:48 So, here we are in Excel, and
• 02:50 we can actually start to put some basic formulas in place.
• 02:53 So, what we're gonna do here is, we're gonna fill in the light blue areas, and
• 02:58 we're gonna put formulas in place to actually practice and
• 03:01 try this a little bit here.
• 03:02 So, the first one we're gonna start with is we're gonna put in one plus five.
• 03:05 And to do that, we start with the equals key.
• 03:07 Remember, every formula must start with equals.
• 03:10 We type in one plus five.
• 03:12 And then we can hit Enter.
• 03:15 And you'll notice that it gives us six, so that works nicely.
• 03:18 To do some subtraction we can say equals eight minus six.
• 03:23 And to prove out that we followed the order of operations here, what we're gonna
• 03:28 do is we're gonna type in equals and we're gonna open our parenthesis.
• 03:31 We can say one plus five and close our parenthesis.
• 03:35 That, of course, will be evaluated before we subtract two.
• 03:38 Accept that.
• 03:40 But we want to multiply by two first.
• 03:42 So when I say times two what we should see here if we're following the order
• 03:46 of operations is that the parentheses are evaluated first.
• 03:50 So it should say one plus five equals six.
• 03:52 But then the multiplication comes before subtraction.
• 03:55 So we'll get two times two is four.
• 03:57 That'll be subtracted from six.
• 03:58 Should yield two.
• 03:59 And, indeed it does.
• 04:02 Now this gets interesting cuz notice at this point in time,
• 04:06 we could type in minus eight divided by two.
• 04:09 And if you look in the formula bar,
• 04:11 you can see that's exactly what we've typed here.
• 04:13 And if I hit Enter, it will still return the correct result.
• 04:18 But, remember I said before that every formula must start with equals.
• 04:22 Well, here's what's interesting with the minus sign.
• 04:24 If we go back and we look at it, check out the formula bar.
• 04:28 Excel has decided in this particular case, it says, well, a minus sign,
• 04:31 that's probably gonna be a mathematical equation, and so I'm gonna put the equals
• 04:35 in front of it to make sure that it turns it into a proper formula.
• 04:37 So, if we took that out, it would put it back in for us as well.
• 04:41 So, we have to have that in place.
• 04:45 If we ever wanted to use a dash, for example,
• 04:47 in something, what we do is we actually put a little apostrophe in front of it.
• 04:52 Then we could put in minus eight slash two.
• 04:54 That would not convert it to a formula.
• 04:56 That's what the apostrophe will do, is it'll actually treat it as text.
• 05:00 Okay? But we're not gonna do that for right now.
• 05:03 Now, to really unlock the power of Excel,
• 05:05 we actually wanna start using our formulas with ranges because then we can change
• 05:10 the value inside the cell later and the formula will automatically update.
• 05:15 To do that, we'll say equals.
• 05:18 And now we have a couple options.
• 05:19 We could use our mouse to go and click over here and say C10.
• 05:25 And we could say plus.
• 05:28 We could use our mouse to go and grab D10, or we could even arrow over to it as well.
• 05:32 So I'm just using my right arrow key twice,
• 05:34 and again you'd notice that the formula updates to work as well.
• 05:38 So, we say, Enter.
• 05:41 And you can see that it works.
• 05:43 The nice piece here, of course, is that now,
• 05:45 if I were to change this to two, the formula will update.
• 05:48 So I can change that back to one, and it goes back to six, which is great.
• 05:52 So let's try the same ones.
• 05:53 If we went to C11 minus D11,
• 05:59 we'll get the answer we're looking for.
• 06:03 If we used our parenthesis,
• 06:07 we can say equals one plus D12 minus,
• 06:12 we'll go with E12 times F12.
• 06:17 Same numbers as we used up above here.
• 06:20 We'll see what ends up happening.
• 06:21 Same results.
• 06:23 Even though, if we look back in the formula bar,
• 06:24 we can see now that it's all cell references.
• 06:28 Let's go try the last one.
• 06:29 We'll say minus C13 divided by D13.
• 06:36 And, again, you'll notice that when we hit Enter, the formula will commit,
• 06:40 it returns the right value.
• 06:42 But Excel has again gone and put an equal sign in front.
• 06:45 So this is how we create formulas in Excel.
• 06:48 Basic formulas to do basic mathematical operations.
• 06:51 If you wanna get some more practice with this, then by all means, you can.
• 06:55 There's another little practice worksheet that we have over here.
• 06:57 You can go through, and you can try and
• 06:59 build the solutions that you're looking for, or you're directed to use here.
• 07:03 And then there's actually a solutions page as well that will show you the completed
• 07:07 versions of those formulas as well.
• 07:08 So opportunity to practice and play around with it, too.

Lesson notes are only available for subscribers.