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

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Formula Anatomy.xlsx
22 KB
Formula Anatomy - Completed.xlsx
22 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 One of the most important features in Excel are formulas.
  • 00:09 Well, what the heck is a formula anyway?
  • 00:12 What a formula is, it's essentially Excel's way of working through
  • 00:16 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:29 which is really, really neat.
  • 00:31 One thing that we have to remember about formulas is that
  • 00:34 every single formula in Excel always starts with the = character.
  • 00:38 And this is the way that we can tell whether or not something is a formula.
  • 00:42 If it starts with =, it's a formula.
  • 00:44 If it doesn't, it's not, it's just that simple.
  • 00:46 One of the interesting pieces, though, is that we can actually build formulas.
  • 00:50 Either by starting with the = key, or with a couple of special characters as well.
  • 00:55 One of them is the- key.
  • 00:57 But the- key, once you type in -5 and Enter, will actually convert,
  • 01:02 in the formula bar, to =-5.
  • 01:04 So it always puts that = sign in front.
  • 01:07 The same is true if you start a formula using the + sign,
  • 01:12 +5+2 would return =5+2 in your formula bar later.
  • 01:18 Now formulas, as I mentioned, can be used to do all kinds of cool things.
  • 01:21 We can start with simple things, like adding two numbers together, =1+5.
  • 01:27 The only different here between what you would type in your calculator and
  • 01:32 what you would say to Excel is you start in a cell and say =.
  • 01:35 And then the equation that you want.
  • 01:37 Where, normally, when we work with a calculator, we're more like to say 1+5=.
  • 01:42 So it works a little bit backwards.
  • 01:44 But the reason is because Excel already has the cell to hold the answer.
  • 01:48 And it wants to know what that cell should be = to.
  • 01:51 So that's how it sort of works through its stuff.
  • 01:54 The other thing that we should remember with this is that, and
  • 01:56 this is where things start to get really powerful.
  • 01:59 Is that we can actually use ranges to actually feed our formulas.
  • 02:03 So if A1 contains the value of 1, and B1 contains the value of 5.
  • 02:08 Then this particular formula =A1+B1 will evaluate to =1+5,
  • 02:14 and would give us the answer of 6.
  • 02:18 We could also perform much more complex operations.
  • 02:21 And this is also very good, because Excel actually follows the order of operations.
  • 02:26 Whether you refer to it as BODMAS or PEMDAS or anything else.
  • 02:30 It works through its brackets first, and then it's exponents, and
  • 02:33 then it's multiplication, division, eventually addition and subtraction.
  • 02:36 And we'll look at that in more advanced videos.
  • 02:38 But for right now why don't we go and
  • 02:40 look at the very basics inside Excel as to how formulas actually work.
  • 02:46 So let's start with the very basics here.
  • 02:48 And I want to set cell B7 =1+5.
  • 02:54 Remember, we always want to start every formula with an =.
  • 02:58 And when we look at this, 1+5, we add those two together, we should get 6, and
  • 03:02 indeed we do.
  • 03:04 Let's do the next one, =8-6, which we hope would return 2.
  • 03:09 And naturally it does.
  • 03:11 Now for more complicated mathematics,
  • 03:13 we're gonna write the same kind of a formula.
  • 03:15 We'll say =(1+5), and then -2.
  • 03:22 And remember the multiplication is an asterisk, Shift+8, times 2.
  • 03:28 Now, again this will follow the order of operations.
  • 03:31 So it should go within the parenthesis first, 1+5 is 6.
  • 03:35 And then we'll have -, but the 2*2 needs to be evaluated before we can subtract it.
  • 03:40 So that'll give us 4.
  • 03:41 So we should have 6-4=2, and Enter, and that is exactly what we get.
  • 03:46 Now for the final value set here, I'm gonna go straight with -8/2.
  • 03:53 Notice I have not put an = sign in front of this.
  • 03:56 And when I say Enter, it still returned -4.
  • 04:00 And if I go back and look at a formula,
  • 04:02 notice that it actually does have an = prepended to it.
  • 04:06 So if you put in a -, Excel is gonna automatically try and
  • 04:10 convert it to be a formula.
  • 04:13 Now let's amp this up a bit, let's go and start using ranges.
  • 04:17 Now we've got the same formulas here, but we've got inputs in cells.
  • 04:21 Because of course the challenge is if anything changes over here
  • 04:24 these formulas will stay static.
  • 04:26 Because they're using hard coded numbers.
  • 04:29 So in this case, we're gonna come down to B14.
  • 04:32 And what we're gonna do is we're gonna say =.
  • 04:35 And we'll choose C14+D14.
  • 04:41 Again, this is still 1+5.
  • 04:43 And when we hit Enter, it still returns 6.
  • 04:47 The beauty is through, if I were to change C14 to 2 and
  • 04:51 hit Enter, it now calculates to 7.
  • 04:54 I'm gonna press Ctrl + Z to undo it.
  • 04:56 And you'll see that it goes back to 6.
  • 05:00 Let's do B15.
  • 05:01 We'll say =, we'll take 8-, and we'll choose the 6 that's
  • 05:07 living in D15, and we'll hit Enter, and we still get 2.
  • 05:12 Now, let's test the mathematics here for a little more complicated things.
  • 05:16 Open our parenthesis,
  • 05:20 we'll say (C16+D16) -E16*F16.
  • 05:29 Now we've used the same values that we used when we were actually calculating
  • 05:33 B9 here.
  • 05:34 So when I hit Enter we get the same result.
  • 05:39 And finally, we'll say -, we'll choose to subtract C17.
  • 05:44 And we'll divide it by D17, and we'll hit Enter.
  • 05:50 And it again returns -4.
  • 05:52 And once again, you'll notice that up in the top here it's gone and
  • 05:56 it's actually put an = in front.
  • 05:57 To turn it into a formula that will calculate automatically.
  • 06:00 And at this point,
  • 06:01 any change to any of these values that are in those cells we referred
  • 06:04 to will automatically flow through these formulas to return the right result.

Lesson notes are only available for subscribers.

Basic Math
05m:22s
Cell Referencing
06m:03s
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