Locked lesson.
About this lesson
Understanding Excel Formula Anatomy.
Exercise files
Download this lesson’s related exercise files.
Formula Anatomy.xlsx14.6 KB Formula Anatomy - Completed.xlsx
14.7 KB
Quick reference
Topic
Understanding Excel Formula Anatomy
Where/when to use the technique
Use to understand the anatomy of Excel formulas, and how to build them.
Instructions
What is a Formula?
- Excel’s method to return data based on a question or equation you have provided.
Key points to remember
- Every formula starts with =
- Equations started with - will convert to =-
- Equations started with + will convert to =
- Formula inputs can be:
- Numbers or text
- Ranges
- Excel Functions
- Formulas can be used to:
- Add two numbers: =1+5
- Add values in ranges: =A1+B1
- Perform more complex operations
- Mathematical formulas are always evaluated based on the order of operations:
- Brackets (Parenthesis)
- Exponents
- Multiplication and Division
- Addition and Subtraction
- Mathematical Operators
- Brackets: ( )
- Exponents: ^
- Add: +
- Subtract: -
- Multiply: *
- Divide: /
- 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.