🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

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
20.3 KB
Basic Math - Completed.xlsx
20.4 KB

Quick reference

Basic Math

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:04 One of the reasons we use Excel so extensively in the real world is because
  • 00:08 it's one of the world's greatest analytical tools, and
  • 00:11 one of the world's greatest calculators.
  • 00:14 We can do all kinds of mathematical calculations and
  • 00:17 build business intelligence solutions in Excel
  • 00:19 as long as we know how to actually write formulas to make that happen.
  • 00:23 Now, the first thing that we need to be aware of is,
  • 00:25 what symbols does Excel use for calculating mathematics?
  • 00:30 And, as you'd expect, they're gonna look a lot like this,
  • 00:35 addition, we use the + sign, subtraction the- sign, multiplication,
  • 00:40 we use the * instead of the x, division is a /.
  • 00:45 And for exponents, we use this little hat icon which is
  • 00:49 Shift+6 on the North American keyboard and I believe is similar around the world.
  • 00:54 Now if we want to actually write a formula, how would we go about doing that?
  • 00:59 Well let's try and
  • 01:00 work this out right now by looking at the cost of our soccer balls.
  • 01:03 We wanna try and figure out what the total cost is.
  • 01:06 So in order to set this cell equal to something,
  • 01:10 we would type in = and now we can say 56 * 8.99.
  • 01:15 And then we can hit Enter, and Excel will do the calculation for us.
  • 01:20 And if you grab a calculator,
  • 01:21 you can prove it out that this is exactly mathematically correct.
  • 01:25 But there's a challenge, because what happens when the cost or
  • 01:28 the quantity of our soccer balls goes to 57?
  • 01:31 Well, it's still pointing to the 503.44 because we've multiplied 56 times 8.99.
  • 01:38 So I'm gonna press Ctrl+Z to undo this for a second.
  • 01:41 We'll set it back.
  • 01:42 And I'm gonna show you how we would actually do this in Excel.
  • 01:45 We would say, equals and
  • 01:49 we gonna click on cell B9 times cell C9.
  • 01:54 And now we can hit Enter and you'll notice that we still get the same calculation.
  • 01:59 Why is this better?
  • 02:00 This is better because we can now Ctrl+C to copy this and
  • 02:05 we can bring this all the way down and Ctrl+V to paste it.
  • 02:09 And you'll notice that when I go and click on the 669.48, if I go and
  • 02:14 click in the formula bar, it's now multiplying these guys.
  • 02:17 And we'll talk a lot more about how these formulas actually extend as we go through
  • 02:21 different modules.
  • 02:22 But the key thing to recognize is that we've multiplied B13 times C13, so
  • 02:26 we've got the correct price.
  • 02:29 And now if the cell changes to 57, and
  • 02:33 we hit Enter, it automatically recalculates to show us the correct value.
  • 02:38 I'm gonna press Ctrl+Z to undo that again, but you can see how
  • 02:41 working with ranges in our formulas actually allows us to be quite robust.
  • 02:45 And there's no need to reach for a calculator on the corner of your desk and
  • 02:48 put in a hard coded number because this automatically recalculates for you,
  • 02:51 which is great.
  • 02:53 Now, what if I wanted to go and sum up all of these values?
  • 02:57 Well, I could do this.
  • 02:58 I could say =, and we could go and grab the quantity, + the next quantity,
  • 03:03 + the next quantity, plus the next one, plus the next one, and we could hit Enter.
  • 03:09 And this would give us 278.
  • 03:11 And we can check that either by adding on a calculator or
  • 03:14 by selecting the cells here and seeing the sum in our quick information is 278.
  • 03:19 But this has a challenge, while it works, it works now.
  • 03:24 What happens if I insert a new row and put 10 units here?
  • 03:30 Notice it didn't change.
  • 03:32 If I grab all this data, the total should be 288, and
  • 03:35 the reason being is because the formula that we wrote misses that cell.
  • 03:43 So is there a better way, and indeed there is.
  • 03:46 I'm just going to delete this cell.
  • 03:48 We want to use a sum function.
  • 03:50 Which you can actually get from the Autosum button.
  • 03:55 And if you look at the options for Autosum, we have the ability to sum or
  • 03:59 average, count number, max, min or even more functions.
  • 04:03 Because I'm immediately underneath a contiguous column of values here,
  • 04:07 I'm gonna press Sum and you'll notice that it writes the formula I need for
  • 04:12 me, equals sum B9 to B14, and this is actually an entire range.
  • 04:17 And now, when I hit enter, it gives me 288.
  • 04:20 And what's interesting about this, is that I can right click and delete this row, and
  • 04:25 it'll go to 278.
  • 04:26 Or I could even right click insert a new row and
  • 04:29 come back and put 20 units here, and it'll go to 298.
  • 04:34 Now, I'm gonna get rid of that one, cuz you get the idea.
  • 04:37 And I'll show you that we can also do the same thing over here.
  • 04:41 I could grab all this data I can go to the Autosum button and say Sum and
  • 04:45 it will immediately say, well, everything you selected, the last cell was empty.
  • 04:51 So I'm gonna put the sum right in there and
  • 04:53 it's gonna sum up this range of data for you.
  • 04:56 This is where the real magic comes into Excel, this is why it's the world's best
  • 05:00 calculator because you can type values into your ranges and
  • 05:03 Excel can automatically run calculations and update them for you.

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

© 2023 GoSkills Ltd. Skills for career advancement