In this video, learn how to perform calculations on numbers by inserting a formula by typing it in or by inserting a built-in function using a variety of methods.
- [Instructor] Well so far we've added our text data for our title and labels. We've added numerical data for the revenues for the first quarter here at Red30. Now it's time to use formulas and functions to calculate the answers we're looking for. That's what we're going to do in this movie with our Red30 Revenues 2019 sheet. You can open up R30Revenues0104 if you're catching up. You'll find that in the Chapter One folder of your exercise files. We're going straight to Cell C11. That's where our first answer needs to show up.
It's going to be the sum total of the numbers we see for January up above for each of our locations. All you have to remember to start a formula or a function is to use the equals sign. When you press the equal sign on your keyboard, you are starting a formula. Now you might be tempted to type in the numbers that you see here like 2.95. There it is up there for Canada. Add a plus sign and then 3.11 add another plus sign 1.89.
And then one more plus sign and the last value's 2.56. When you press Enter, you will get the right answer. But what happens if we realize we entered an incorrect value for Canada, should be 3.95? We go up there to Cell C6. Go up to our Formula Bar here and take out the two, add in a three, and press Enter. Notice the total doesn't update to 11.51. It should have, but it's not because it's using actual numbers and not the cells.
What we really want is a formula that says take the contents of Cell C6, whatever it is, and add it to C7 plus C8 plus C9. So, let's go up here to the Formula Bar and take everything out except the equal sign. We need that to start the formula. And instead of typing the number 3.95, we'll type the cell address which is C6. Then you can type in a plus sign, C7. Notice what's happening? Things are getting highlighted here. Type in a plus sign, you could even click the cell.
Let's click Cell C8 plus click C9. You can see it being written up here in the Formula Bar for us. Now all we have to do is press Enter and check out the answer. It's 11.51; that's exactly what it should be. Now that's one option, and it works for just a few numbers. But it would be a lot of clicking and adding plus signs if we had hundreds of rows of data we wanted total. In that case we wanna use a function, the SUM Function. And for it we start with the equal sign as well.
First let's delete what we've done. Just with your Cell C11 selected, hit your Delete key on the keyboard to remove the contents. You can see up here in the Formula Bar it's empty. This time type in the equal sign and then the word sum. This is just one of many, many functions built in to Excel. The SUM Function adds numbers in a range. So next we want a range of cells, and they'll show up in round brackets. So, let's add the left round bracket. Down below you're getting a hint.
You could type in number 1, a comma, number 2. So we could do, for example, C6 comma C7 comma C8. You can see how that would take a while. So instead here's the fastest way to select a range. Just click and drag. Notice it gets written for you, C6 colon C9. You can see it up here on the Formula Bar as well. Now we can click after C9 and just close it up with a closing round bracket. There's our range. And now when we press Enter, we get that same answer.
But it's much faster using a range. Just imagine dozens or even hundreds of rows of data, how much faster that would be. Well as fast as that is there's one other faster action, and that is something called AutoSum. There are different Auto functions, one of them is the SUM function where automatically it will go looking for numbers in the vicinity if it sees a group of numbers, left, right, above, like we see here at the AutoSum will automatically calculate them and create the formula for you. So, one last time, here in C11, let's delete what's there.
And instead, go up to the Formulas Tab here on the ribbon, you'll see AutoSum. That's the button on the top half. At the bottom half, click that you'll see other options like Average, Counting of Numbers, finding the maximum or minimum numbers, and there are other functions down below. But with SUM, under AutoSum, when we click this, it's gonna go looking for groups of numbers. There's nothing to the left, right, or below, but there is a group of numbers above. And it will automatically use those in a formula that it will write for us.
Go ahead and click. You can see what it's doing here. It's creating the at SUM function, and the range is C6 to C10. It is including a blank cell which will still give us the right answer. Go ahead and press Enter. But if you wanna clean that up, just go back to C11 and up here in the Formula Bar, we can take out the 10 with our Backspace key, type in a nine, and press Enter to get it exactly right. So, there's a few ways to get formulas and functions into a cell.
The next step now, of course, will be to get that same formula or function showing up at the bottom of Column D and E for February and March. But the good news is, we don't have to repeat the process. Once we have it in there once, we can actually do some copying and pasting using something called relative cell addressing and that's what we're going to talk about next.
- Explore efficient methods for adding functions and formulas.
- Format text, numbers, dates, and times.
- Save, share, and print your workbooks.
- Recognize how to freeze panes to control your view.
- Recall methods for protecting documents.