Graduation Sale - 20% off! Get the skills they don’t teach in college - Graduation Sale - 20% off

GoSkills
Help Sign up Share
Back to course

Basic Math

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Utilize basic mathematics including multiplication and division in Excel.

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.

Basic Math.xlsx
8.2 KB
Basic Math - Completed.xlsx
8.7 KB

Quick reference

Topic

Basic Math in Excel.

When to use

Working with basic mathematics in Excel.

Instructions

Excel’s Mathematical symbols

Addition +
Subtraction: -
Multiplication:  *
Division:  /
Exponents/Powers:  ^

Formula Basics

  • To write a formula in Excel you must start with the = character
  • A basic multiplication formula might be =8*10

Using Ranges

  • Instead of using numbers in our formulas, we can link to cell addresses

Basic formula examples

Multiply A5 by B5:  =A5*B5
Add A5 and B5: =A5+B5
Divide A5 by B5: =A5/B5

AutoSum

  • A feature to write formulas using basic Excel functions for you
  • To SUM a column:
    • Select the cell immediately below the last row of data
    • Go to the Home tab » Editing group » AutoSum
    • The formula will automatically show up in the cell
    • Press Enter
  • To AVERAGE a column:
    • Select the cell immediately below the last row of data
    • Go to the Home tab » Editing group » click the down arrow just to the right of the AutoSum button
    • Choose Average
    • The formula will automatically show up in the cell
    • Press Enter
Login to download
  • 00:05 One of the reasons we use Excel so extensively in the real world is
  • 00:09 because it's one of the world's greatest analytical tools.
  • 00:13 And allows us to do a lot of mathematical calculations and
  • 00:15 build business intelligence solutions out of it.
  • 00:19 The challenge is of course that we've got to figure out how to work with formulas in
  • 00:22 order to make that happen.
  • 00:24 Since Excel has a great calculation engine in it,
  • 00:27 we need to understand what mathematical symbols we can use.
  • 00:30 And those symbols are, as you probably expect, going to be + for
  • 00:34 addition,- for subtraction.
  • 00:36 They're going to be the * for multiplication,
  • 00:41 the / for division, and if you need to raise a number to the power,
  • 00:46 you need the little hat icon here that is Shift+6 on the North American keyboard.
  • 00:51 And I believe that's similar in the rest of the world,
  • 00:53 but Shift+6 will give you that little hat.
  • 00:56 That's how we raise a number to the power, one number on one side to the power of and
  • 00:59 then the next number.
  • 01:01 So these are the symbols that we use for our math.
  • 01:04 Now, if we wanted to use those to say, take the numbers in our table and
  • 01:09 calculate the worth for our individual products.
  • 01:14 What we'd really like to do is we'd like to set cell D6 =,
  • 01:20 whoops, hang on, = 56 x 8.99.
  • 01:25 Any time we do a calculation, we always start with equals and
  • 01:29 this is gonna set the cell equal to that amount, which is 503.44.
  • 01:34 But there's a challenge with this because we'd really like to take this, copy it,
  • 01:38 paste it into the cell and have it work for the numbers on the left.
  • 01:42 And right now obviously it won't because, it's hard coded to these values.
  • 01:47 So wouldn't it be nice if we could actually say equals this cell times
  • 01:51 this cell.
  • 01:52 So let's give that a shot.
  • 01:57 Let's say equals 56 in B5 times the 8.99 in C5.
  • 02:03 And when we hit Enter,
  • 02:05 you'll notice that the calculation wrings true with what we calculated before.
  • 02:09 That's kinda cool.
  • 02:09 But this has a benefit because now we can copy this and
  • 02:14 we can select our cells here and right click and paste.
  • 02:19 And you'll see that the formulas have actually run all the way down the table
  • 02:23 and are working nicely for us.
  • 02:24 This one in fact is pointed to these two cells.
  • 02:27 And we can prove it out by saying, = 84 x 7.97,
  • 02:32 I'll just move that cursor out of the way there, and say Enter.
  • 02:38 And indeed, it rings true at 669.48.
  • 02:43 We're gonna talk in future videos about why formulas extend this way,
  • 02:47 the difference between absolute and relative referencing, but
  • 02:49 for right now, you know that when you copy your formula it will extend and
  • 02:53 everything works nicely, which is great.
  • 02:56 So we've been able to do multiplication across our table.
  • 02:58 Wouldn't it be nice if we could also add up our quantities?
  • 03:02 So let's try this.
  • 03:03 We know that plus is used for addition.
  • 03:05 We'll say, = 56 + 84 + 21
  • 03:10 + 65 + 52, and hit Enter.
  • 03:15 And it gives us 278.
  • 03:17 And we can prove this out as well by selecting these cells and
  • 03:22 looking in the quick access toolbar, where it says sum is 278.
  • 03:28 But working with formulas in this way, while useful, can cause us some problems.
  • 03:33 Here's why.
  • 03:33 Let's right click and insert a new row.
  • 03:37 And we'll put in a quantity here of 10.
  • 03:39 And you'll notice that it still returns 278 because
  • 03:44 the cell that we needed isn't pulled into the formula anymore.
  • 03:49 So rather than do that, why don't we delete this and
  • 03:52 come up with a different way.
  • 03:53 The way we're gonna do this,
  • 03:55 we're gonna select the cell immediately below our data.
  • 03:58 We're gonna go to the AutoSum button on the Home tab, and
  • 04:03 noticed that there's several different types that we can get.
  • 04:05 Sums and Averages, Counts of Numbers.
  • 04:08 I'm gonna go with the Sum because I want to add these up.
  • 04:10 And you'll see that when I do that, its sums the entire range from B5 to B10.
  • 04:17 So now, when I hit Enter, it gives me 288.
  • 04:20 And if I select this, I can see that, indeed, it is 288.
  • 04:24 And if I were to right click and delete this row.
  • 04:29 It will recalculate to 278.
  • 04:31 The reason being is I'm not picking up specific cells anymore,
  • 04:35 I'm picking up a range.
  • 04:36 So that works much better.
  • 04:38 Let's try this again.
  • 04:39 We'll go over to the Worth column and say AutoSum, give me the sum.
  • 04:44 Gives me D5 to D9 and I can hit Enter and
  • 04:48 it will automatically put the sum in there as well.
  • 04:50 So this is really really useful.
  • 04:52 We can write formulas that are picking off individual cells like we've done here, or
  • 04:57 we can actually write formulas using some functions, which we'll explain in a lot
  • 05:01 more detail later to pick up entire ranges of cells as well.
  • 05:06 So I say Excel is the world's best calculator, and
  • 05:10 this is part of the reason why.

Lesson notes are only available for subscribers.

Formatting
05m:55s
Formula Anatomy
06m:15s
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

© 2022 GoSkills Ltd. Skills for career advancement