Formula Anatomy

Quick reference

• 00:04 If you've ever had a conversation with someone about Excel, there's a good chance
• 00:08 that the word formula has come up, maybe in a positive or not so positive light.
• 00:13 But the thing that I want you to know is that formulas are at the heart of making
• 00:17 Excel what it is.
• 00:19 So what's a formula?
• 00:21 Well, simply put, a formula is basically Excel's method to return
• 00:26 data based on the question or equation that you've provided.
• 00:31 It can dynamically react to the changes in the inputs and return you a result.
• 00:36 And this is actually what's made Excel so powerful and so
• 00:39 popular today is because it's become the Swiss Army knife of everything, and
• 00:43 it's all driven by formulas.
• 00:45 That's what makes it dynamically recalculate to give you
• 00:47 the answers you need.
• 00:49 So the question is, well, how do you start these?
• 00:51 because they sound a little bit intimidating.
• 00:53 Well, the first thing you need to know is that every formula must start with
• 00:56 a specific character, and that character is the equals character.
• 01:00 If it starts with equals, it's a formula.
• 01:02 If it doesn't start with equals, it is not a formula.
• 01:06 It is just that simple.
• 01:08 Now, I've had people that have come to me and say that's not true, Ken,
• 01:12 because if I start typing minus and then do things it actually is a formula.
• 01:16 Well, that's true except that when you hit Enter, it actually converts that minus
• 01:21 at the beginning to equals minus, notice that equals is in the front.
• 01:25 In addition,
• 01:26 if you start with plus, it will convert it to just plain old equals, okay?
• 01:30 These are just conversions that actually help to
• 01:32 make the software compatible with Lotus 123 from way back in the day.
• 01:38 So what can you use formulas for?
• 01:40 Well, you can use formulas for all kinds of things.
• 01:42 Let's say that you want to do just basic, basic math.
• 01:44 You can add two numbers using a formula, equals 1 plus 5.
• 01:48 When you hit Enter, the result that gets returned to the cell will,
• 01:51 of course, be 6.
• 01:53 You can also add values and ranges, equals A1 + B1.
• 01:57 Let's say that A1 holds 10 and B1 holds 5, you would add these
• 02:01 two things together A1+B1 and you would get the result of 15 returned to the cell.
• 02:07 The difference between the first formula and the second is,
• 02:09 if you change the value in A1, it will now update automatically for you.
• 02:14 Formulas can be used to provide or to return all kinds of complex operations.
• 02:18 It really comes down to what the formula you've written actually, dictates,
• 02:22 whatever it is, it's going to return the result to the cell.
• 02:26 Let's go hop over to Excel now, and just take a quick look.
• 02:29 Let's start with some basic math using numeric constants and basically what we're
• 02:34 replicating here is, using Excel to act as your calculator instead of reaching for
• 02:38 your phone or your calculator on the side of the desk.
• 02:42 We're going to start our formula here in B7, we're going to add 1 and 5 together.
• 02:46 We're going to say equals 1 plus 5, just like you do on your phone or
• 02:50 your calculator, and we're going to say, Enter, and it gives us the result of 6.
• 02:55 Fantastic.
• 02:57 Let's do 8 minus 6, so equals 8 minus 6.
• 03:00 Boom, we get 2, great.
• 03:04 Let's do this next one here.
• 03:05 So what I'm going to do is, I'm going to to say 1 plus 5, and
• 03:08 we're going to close that in parentheses, okay?
• 03:11 Minus 2, and then the multiplication is not the x,
• 03:14 of course it's the asterix, times 2.
• 03:17 And what you're going to see is when I hit Enter here,
• 03:20 it doesn't convert it to the result, why not?
• 03:24 Well, it's because I didn't start with equals, so it's treating this as text.
• 03:31 So with the equals character in there, and hit Enter,
• 03:34 now you'll see that it actually correctly calculates 2, why 2?
• 03:38 Because Excel follows the order of operations.
• 03:41 We've got 1 plus 5 in the parenthesis is 6 and
• 03:44 then we've got a multiplication over here,
• 03:46 2 times 2 is 4 and then we subtract 4 from 6 to get 2, so that works beautifully.
• 03:51 Now interestingly enough, as I say here we can say minus 8 divided by 2,
• 03:56 I did not start this with equals but because it starts with a negative,
• 04:01 when I hit Enter, it returns the result, negative four.
• 04:05 And if I go back and look at the formula you'll notice that Excel has
• 04:10 converted it to start equals negative so it still starts with equals and
• 04:14 that's important to recognize.
• 04:17 Now your basic mathematical symbols are all here,
• 04:19 you start your formula with equals, as I mentioned, and these are the different
• 04:22 mathematical operators that we end up using for different things.
• 04:26 So let me go through and actually show you now how instead of using constants,
• 04:30 we can use ranges to return the exact same results.
• 04:33 We're going to try again, we're going to say equals 1 but this is C14+D14.
• 04:40 The big difference of course, is now if somebody goes and
• 04:43 updates one of these cells, this will automatically recalculate.
• 04:46 If somebody changes one of the numeric constants, in this case,
• 04:49 I'd have to update the formula manually.
• 04:51 It's very hard to see that I need to do that, so this way is much better.
• 04:56 Let's try again, so equals, I'm going to arrow over to 8, press minus,
• 05:00 arrow over to 6 and hit Enter.
• 05:02 Fantastic.
• 05:04 We know that we need to hit equals, so let's open our equals,
• 05:09 open parenthesis, we'll say 1 plus 5, close the parenthesis,
• 05:14 minus, arrow over 2, asterix, arrow over to the 2 again, and hit Enter.
• 05:20 And again we get 2.
• 05:21 Finally, we could say equals negative 8 divided by arrow 2,
• 05:26 which is in D17, every one of these is now based on a formula and
• 05:31 you can see that whether using hard coded constants or
• 05:36 whether using ranges, we actually get the correct calculation.
• 05:41 The big deal is, it's much easier for me to see what's going on in this formula
• 05:45 than this one because in order to actually see what's happening here,
• 05:49 I have to click on the formula to see what's going on in this result.
• 05:53 So if there's an update that needs to be done, I've now got to look for
• 05:56 hardcoded number, where in this case, I can even label these things and
• 06:00 make them very obvious inputs, makes it very, very easy for
• 06:02 me to dynamically recalculate things when a single input changes, okay?
• 06:06 It's beautiful thing here.
• 06:08 So this is the basics of how to write formulas in Excel.

Lesson notes are only available for subscribers.