GoSkills
Help Sign up Share
Back to course

Formula Anatomy

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Understanding Excel Formula Anatomy

Exercise files

Download this lesson’s related exercise files.

Formula Anatomy - Begin.xlsx
25.7 KB
Formula Anatomy - Complete.xlsx
25.8 KB

Quick reference

Formula Anatomy

Understanding Excel Formula Anatomy

When to use

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:                  /
Login to download
  • 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.

Basic Math
05m:31s
Cell Referencing - Theory
07m:12s
Share this lesson and earn rewards

Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement