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

Basic Math
05m:05s
Cell Referencing
05m:34s
Share this lesson and earn rewards

Facebook 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