Locked lesson.
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:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Basic Math.xlsx20.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
- 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.