Locked lesson.
About this lesson
Utilize basic mathematics including multiplication and division in Excel.
Exercise files
Download this lesson’s related exercise files.
Basic Math.xlsx8.4 KB Basic Math - Completed.xlsx
8.9 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
- 00:05 >> Now, we've seen how to enter data in Excel, but
- 00:08 now we're gonna start looking at formulas.
- 00:10 These are the real things really that make Excel sing for us.
- 00:14 Some of the key things that we need to understand is that in order to make
- 00:17 a formula, a formula must start with the equals key.
- 00:21 If it starts with equals, it is a formula.
- 00:23 If it does not start with equals, it is not.
- 00:25 It's just that simple.
- 00:27 Now, if we wanted to do some basic math,
- 00:31 we might try something like this, where we say equals eight.
- 00:36 And we might put in plus ten and press enter.
- 00:39 And you'll see that it gives us the answer of 18.
- 00:41 The basic mathematical symbols that you see in Excel
- 00:46 are plus, minus, of course, to subtract.
- 00:51 We could go with the asterisk, which is shift eight, that is multiply.
- 00:57 Divide is the forward slash down by your shift key.
- 01:01 And if you wanted to set something to the power of, you use the little hat symbol
- 01:06 that is on shift six, and that will give you the eight to the power of.
- 01:09 So we'll get a pretty big number by doing that.
- 01:12 But those are the basic mathematical symbols that you end up dealing with.
- 01:16 So plus, minus, the asterisk for multiply, the slash for divide, and
- 01:20 the little hat symbol for powers.
- 01:22 Now, it's neat to see that we can write formulas like this and then calculate.
- 01:27 But where Excel really starts to sing is where we actually start,
- 01:31 instead of using hard coded numbers in our formulas,
- 01:34 we actually start writing formulas that refer to cells.
- 01:37 So if we wanted to get the product of let's say B5 times C5,
- 01:43 what we would do is we would type in equals, and
- 01:45 we could use our mouse if we want to click on a cell, type in multiply.
- 01:51 And we can click on the cell with 899, or we could use our arrow keys as well.
- 01:55 Watch, I'll arrow to the left right now.
- 01:58 You can see that it changes and
- 01:59 moves it around, but its updating the formula as it goes.
- 02:02 So put it back to C5, and I'm gonna press Enter.
- 02:05 Now, the reason why I do this is because these formulas are now dynamic.
- 02:11 If I type in 57 over here, you'll watch,
- 02:14 when I hit Enter it's gonna update the formula.
- 02:16 And that's one of the great things about Excel, is that it recalculates.
- 02:20 Now, how would I go about actually expanding this to multiple cells?
- 02:25 Well, there's a way that we do this.
- 02:27 We can just grab, down on the bottom left here, you see, or
- 02:30 bottom right rather, when I mouse over this it turns into a black crosshairs.
- 02:34 I can left click and drag this down and
- 02:36 it'll put the same formula in different cells.
- 02:38 So that's fantastic.
- 02:40 And we'll talk more about why this works in future modules.
- 02:44 But basically, at this point in time, you can see now if I click in here,
- 02:47 it's multiplying B6 times C6, and the next cell is grabbing B7 times C7.
- 02:55 What if I wanted to sum all of these guys up?
- 02:58 Well, here's how we can do this.
- 02:59 We can go down click in the cell immediately below these guys.
- 03:03 We can go to the home tab, and
- 03:04 over all on the right hand side you see this button here.
- 03:08 It's a sum button.
- 03:09 So if we click on it, it'll actually write the formula for us.
- 03:12 And it looks at it and says, hey, look, the cells that you have above me,
- 03:16 I can see a block of them, goes from D5 to D9.
- 03:18 So it creates the formula for me.
- 03:20 All I need to do now is press Enter, and it will actually sum all of these guys up.
- 03:25 Now, if we can prove this cell by selecting all these cells, and
- 03:29 if we look at down at the bottom we can see some statistics down here
- 03:33 that tells us 1933.07 is the sum of all the selected cells.
- 03:36 And, sure enough, that's what's showing up in our formula.
- 03:39 Again, if we were to go back and change this, 56,
- 03:42 it updates this formula as well as the sum formulas.
- 03:47 So it's really kind of nice.
- 03:49 What if I want to get an average instead?
- 03:51 Say I want an average of the quantity.
- 03:54 Well, I'll come over and instead of actually hitting the auto sum button,
- 03:57 what I'll do is I'll click the little drop down arrow beside it, and
- 04:00 you'll notice that I can actually get a couple of other different pieces as well.
- 04:04 This time I'll choose Average.
- 04:06 And it'll actually, again, put in the formula for me that says, hey,
- 04:09 you need the average formula from B5 to B9.
- 04:12 Hit Enter, and you can see that it actually commits that formula, as well.
- 04:16 So, this is the real basics of working with formulas.
- 04:19 Remember, again,
- 04:20 if it starts with equals when we look inside the formula bar, it's a formula.
- 04:25 If it does not, it's not a formula, it's just a constant,
- 04:27 a hard coded number or value.
- 04:31 We have all kinds of formulas and functions in Excel.
- 04:33 You'll learn about those as we go forward.
- 04:35 But it's a very,
- 04:36 very useful feature to have your Autosum button over here to sum up those columns.
- 04:40 And then the only other thing that we really need to remember is if we start
- 04:43 with equals and we use a range to feed those formulas in, at that point it's
- 04:48 plus, minus, the asterisk for multiply, the slash for divide, and
- 04:53 the little hat on top of shift six there for doing the power of.
Lesson notes are only available for subscribers.