Add formulas and functions - Microsoft Excel Tutorial

- [Instructor] Well, we've been building our brand-new workbook here in Excel and so far we've added two different types of data, text, as in our title and the labels we see. We've also now added numbers and it's time to run calculations on those numbers. In other words, get the totals for Jan, Feb, and Mar. And that means adding a formula or function, and that's what we're going to explore in this movie as we continue working with our Globe Bank Projected Revenues workbook, GlobeBankPR0105 from the chapter one folder of your exercise files if you need to get caught up. We're here in cell C9. That's where our first formula is going to go. And we want to total up all of the numbers that we see under January for each of our locations. So there's a number of different ways to do this. One way that is not the right way, but you might be tempted, is to simply type in these numbers with plus signs to add them up, and you would get the right answer. But if you went in here and changed any of these numbers, you'd have to change it in the formula as well. Let's explore that. Every formula, whether it's a function or a manual formula you're going to add yourself, starts with an equal sign. Remember that. Might come in handy. Equals. And then we know that we're starting a formula. So if you wanted to, you could type in 4.031, add a plus sign, 3.115, and continue on. And when you press Enter, you get the right answer. Let's see what happens. Plus 2.62 plus 1.908 and press Enter. There's the answer. Totaled up for us nicely. Click in cell C9 again to see that we're viewing the results here on the sheet in the formula bars where we see the actual formula. But if we went in here and changed one of these numbers, like Europe should be 3.62, we'd still see the same total because it's using 2.62 in the formula. We really don't want this. All you need to do is click the cell C9 and hit your Delete key to remove the contents. Now with a blank cell, we can do something else. Again, we'll start with the equal sign. That's always how we start a formula. But this time, we're going to type in the cell addresses, not the contents of the cell. So what we want is C4. I'll type that in. Plus. You can see as we start adding these cell addresses, they get highlighted. If you wanted to, you could type in C5, or even better, just click it. Notice it shows up down below and in the formula bar. Add a plus sign. Click 2.62, which is C6. Plus, and you could click C7 or type in C7. Either way, you're going to get all of the cell addresses we want to total up. Press Enter and you get that same answer. But when we click there and look at the formula bar, it's just taking the contents of cells and adding them up. That means if we go to Europe now, just click there and type 3.62, what it should have been, 3.62, and press Enter. You can see the total automatically updates for us because it's taking the contents of cells, not the actual values. Very nice. There is a better way though, a faster way. Go back here to cell C9 and hit your Delete key on the keyboard to remove the contents. We'll start with an equal sign. And a function is a pre-made formula that we can use. Imagine if we had hundreds of rows. We wouldn't want to be typing in cell addresses or clicking them and plus signs. Instead, we would use a function. And in this case, we're going to look at the SUM function. When you start to type in SUM, you're going to see a dropdown list of a number of different functions. The top one highlighted is SUM, and you can see a description. Adds all the numbers in a range of cells. So now all we need is the range. A range will go inside round brackets. So we start with a round bracket, the left bracket. The range is actually, if you look up above, C4 to C7. So we could type that in. C4, a colon, and C7. Notice how the range gets selected here up above, and that means we're done. We can close it up with the closing round bracket, the right bracket, and press Enter. There's our total. And when we go back to cell C9 and look at the formula bar, we're using a function this time, not a number of cell addresses and plus signs. That's very handy when you have multiple rows and you don't want to type all that cell addressing in. There is another way. Hit your Delete key to remove that formula. Let's start a new one with an equal sign. We'll do SUM again and the open round bracket, but instead of typing in the range, let's just select the numbers by clicking and dragging from C4 down to C7. Notice it writes it for us down below. We can close it up now with that closing round bracket. And press Enter. There's our answer. That was very quick. But believe it or not, there's a faster way even. Go back up to cell C9 and hit your Delete key on the keyboard to remove that. There's something called AutoSum and it's automatically going to look for numbers and total them up. We access it from the Formulas ribbon. So click the Formulas tab up here, and you'll see over here in the Function Library, to the left, there's something called AutoSum. And when we click the dropdown, you'll see other auto formulas for averages, for counting up numbers, finding maximum or minimum numbers. But AutoSum is the one we want. And all it's going to do is look for a range of numbers. It's going to look to the left, to the right, it's look down, and it's going to look up as well. It's going to look up at the cells above where we are and see if there's a range of numbers that it can total up for us. So let's do that. We'll click it. And sure enough, you can see it writes the formula, SUM C4 to C8. It does include this blank cell because row eight is empty. It's included, but it stops up here at row three because it runs into some text. So if we wanted to, we could change that just by clicking after the eight. We could backspace that and type in a seven and just press Enter. That's a fast way to total up numbers. So AutoSum can be found, remember, on the Formulas ribbon here to the left-hand side. All right, when we go back to cell C9 and look at the formula bar, we have our SUM function that was written from AutoSum using the range of cells above. All we need to do now is do the same thing for February and March. We're not going to recreate the formulas or functions. Instead, wouldn't it be nice if we could just copy what we did already and paste it to those other columns? Well, we can, thanks to something called relative cell addressing, and we're going to explore that next.