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 essentially Excel's way of working through
• 00:17 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:30 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, will actually convert, in
• 01:04 the formula bar, to equals minus five, so it always puts that equal 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 to in your formula bar later.
• 01:20 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, =1+5.
• 01:29 The only difference here between what you would type into your calculator and
• 01:33 what you would to say to Excel is you start in the cell and say equals and
• 01:37 then equation that you want where normally when we work with a calculator
• 01:41 we're more likely to say 1 plus 5 equals, 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, so
• 01:53 that's how it sort of works through it's stuff.
• 01:56 The other thing that we should remember with this is that, and
• 01:58 this is where things start to get really powerful,
• 02:01 is that we can actually use ranges to actually feed our formula.
• 02:05 So if A1 contains the value of 1 and
• 02:08 B1 contains the value of 5 then this particular formula =A1+B1 will
• 02:14 evaluate to =1+5, and would give us the answer of 6.
• 02:19 We can also perform much more complex operations, and
• 02:23 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 it's brackets first, and then it's exponents,
• 02:34 and then it's multiplication, division.
• 02:36 Eventually, addition and subtraction.
• 02:38 And 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 We'll start with the very basics of saying =1+5.
• 02:53 Remembering of course that every formula must start with = to set the sell
• 02:58 equal to the value we're looking for which in this case is 6.
• 03:02 Let's try it with subtraction equals 8-6, and that should return 2, which it does.
• 03:10 And now we'll look at slightly more complex math,
• 03:12 where we're going to say =(1+5).
• 03:17 We'll close our parenthesis, then we'll say -2 and
• 03:21 of course the asterisks from other locations to.
• 03:23 And if this is following the order of operations properly
• 03:27 it should say work out the brackets first, so that will be 1 + 5 = 6.
• 03:31 Then work out the multiplication 2 * 2 = 4, so take 6 and
• 03:35 subtract 4 should give us 2.
• 03:38 Which it does.
• 03:39 So that's kinda nice.
• 03:40 Now remember, we can start by saying equals 8 divided by 2,
• 03:44 but when we hit enter on this, if we go back and we actually look at the formula,
• 03:50 notice in the formula bar that it has converted to equals 8 divided by 2, so
• 03:54 this is good to know is that it's still a formula,
• 03:57 it still has to have an equals there.
• 04:00 So now, let's take a look at how we can do this with ranges.
• 04:04 So in this case, if we were to say = 1 + 5, it actually works out to C10+D10.
• 04:11 When we hit Enter, it still gives us 6, as it should.
• 04:16 But the real benefit here is that we can change this 5 to something else now, and
• 04:20 the formula will recalculate.
• 04:22 Now I'll just set that back using CTRL+Z to undo.
• 04:25 But the nice thing is, and you can see the formula recalculate it again as well.
• 04:29 So let's try this again, we'll say = 8- 6 and hit ENTER and it works.
• 04:38 Let's do this with our more complex math,
• 04:43 1+ 5, close the parenthesis, minus 2 times 2.
• 04:49 So we're rebuilding this entire formula exactly as we did above but
• 04:52 this time we've used cell references throughout the whole thing.
• 04:56 If everything works correctly it should give us a 2,
• 05:00 the same as previously and it does.
• 05:03 Likewise here was can say minus T13 divided by D13, and when
• 05:12 we hit enter we'll notice that the same answer comes back, -4, which makes sense.
• 05:17 And again, the formula has been converted to use the equals key.
• 05:22 So the key things to be aware of here, again,
• 05:23 every formula must start with equals.
• 05:25 If it doesn't, it will convert it properly using a plus or a minus.
• 05:29 And the other side that we want to remember is that Excel does follow
• 05:33 the order or operations when it's actually computing its math.

Lesson notes are only available for subscribers.