Add formulas and functions to an Excel worksheet - Microsoft Excel Tutorial

- [Instructor] Well, our brand new workbook here in Excel is well underway. We've added two of the three different types of data we can add to a worksheet. We have our text, we see it in our title and our labels. We have some numbers added now, looks like US dollars in millions. Now it's time to add the third type of data, which are the calculations using formulas or functions, and that's what we're going to explore as we continue working with our Globe Bank Projected Revenues workbook here, GlobeBankPR0105, if you need to get caught up. We left off here in column C, row 9 or cell C9 where we're going to add our very first formula. Maybe we want to total up the numbers for each month. Well, we should probably start with a label over here at the left in cell A9, click there and just type in totals. We'll put in a colon as well. You can hit your tab key a couple of times to get back to cell C9 where we will begin our very first formula. Every formula or function begins with the equal sign, so type that in first, and now Excel knows you're creating a formula. We can continue to work here in the cell or go up to the formula bar to continue working up there. It doesn't really matter at this point. The formula bar for me is more for editing formulas after they're in there. So let's say you wanted to add up all of the values we see in January. Well, in that case you might be tempted to type in the numbers you see. We could do that, but it would cause a lot of problems down the road perhaps. For example, if we type in 4.031, what we see for the USA, add a plus sign, then you might type in 3.115. You wouldn't want to make a typo or your answer would be wrong. Hit the plus sign, 2.51 is the next number we see next to Europe, and then a plus sign for the last one, which is 1.908. Then you'd press enter and you'd have the right answer. Notice we see the results in cell C9, which is 11.564. Click there and you'll see up on the formula bar how we got to that value. We typed in the numbers we saw, and this is totally the wrong way to do it because what if we go back here and realize Europe should be 3.51? Well, we could type it in here, but it wouldn't change the answer down below because it's using 2.51 up here in the formula bar. We'd have to change it twice, in other words, to get the right answer. So instead of using this, let's hit delete on the keyboard to remove what's in there and let's start over with the equal sign. Instead, what we want really is whatever's in cell C4 plus whatever's in C5 plus whatever's in C6 plus whatever's in C7. Now we could start typing that in. Go ahead and type in C4 and then a plus sign. Watch what happens. C4 actually gets highlighted. We know it's now part of the formula, so we could type in C5 or even easier, just click C5. Notice it adds it to the formula for us. Now the plus sign, click C6 plus click C7 and press enter. There we go. We got the same answer, but using a formula that is going to update if we make changes in one of the cells. For example, we go to Europe, this should be 3.51. We click there, type 3.51, and when you press enter, watch the total down below, go up to 12.564. That's the beauty of using cell addresses instead of the actual numbers. So we have our right answer here, but there's an even faster way to do it. So click in cell C9 and hit your delete key and press enter. That removes the formula. If we go back to C9, you can see there's nothing up here in the formula bar. Let's instead use a function. Functions are prebuilt formulas. So we'll start with the equal sign like we always do, but this time start to type in sum, S-U-M. You can see there's a number of different sum type functions that we can choose from. Right at the top with sum highlighted, you can see that little tool tip indicating it adds all the numbers in a range of cells. So the range will go inside round brackets. We start with a round bracket on the left, and now we can actually type in those cell addresses or even easier, look at this. Let's just select the range by clicking and dragging from C4 down to C7 and release. You can see it writes it in for us, C4 colon C7. You could type that in, but it's much easier just to simply select the numbers, knowing you'll always get the right range. We close it up with that right bracket on the right hand side. Shift in the zero gives you that. Press enter. We get the same answer, but we used a function instead much faster than typing in plus signs. Just imagine if you had dozens of rows, you wouldn't want to be clicking cell addresses or typing them in with plus signs in between. A function is much faster. What would you believe? There's an even faster option. Well, hit your delete key and press enter. We've removed the formula now from cell C9 and on the formula bar it's empty. We're going to use something else called an auto sum. So in that case, we actually go up to the formulas tab up here on the ribbon and in the function library section here on the left, you're going to see one that says auto sum. There is a dropdown button that gives you some other auto options, like automatically averaging or counting numbers in a range. Auto sum is going to total up numbers that it finds for you. You don't have to enter the range. It's going to look to the left, to the right. It's going to look down below and above for a range of numbers and use those to, in this case, sum them up. So click the sum function and you can see what it's trying to write here. Sum C4 to C8, and you can see that range is highlighted here. It did include the blank row, which we really don't want, so all we have to do is edit C8 up here in the formula bar. We backspace over the 8 and type in a 7. We get the right range and press enter. Now we could have left the blank one in there. There's nothing in that cell. We'd still get the same answer, but cleaning it up isn't a bad idea. So now we have that same answer. When we click the cell, you can see it's the sum formula or function that we talked about earlier, but we used auto sum to create it. It's a great way if you have a range of numbers that are either to the left, right, above or below your cell, that'll automatically find those and total 'em up for you. It stopped up here at row 3 because it ran into some text labels at the top. So we have our first formula in there. Now you might be tempted to repeat the same process for the next two, but there's a faster way to do that. Thanks to something called relative cell addressing, we can copy and paste formulas and it'll use the right numbers. We're going to explore that next.