Join David Rivers for an in-depth discussion in this video Adding formulas and functions, part of Office 365 for Mac: Learning Excel.
- So we've added some text. We've added some numbers. The next logical step is to input formulas that will perform calculations on those numbers, and that's what we're going to do right now with our Landon Hotels revenue sheet we've been building here. We're going to start in cell C11. If you've skipped to this movie, you need to get caught up, just open up RH_Revenues0104. Now here in cell C11, we can start a formula by typing out the equals sign. That tells Excel we want to create a formula and not just enter text.
So hit the equals sign on your keyboard, and things start to happen. The formula bar is now active. We can come up here to type in our formula if we want to. We can accept it, that's the same as pressing the Return key, or reject it by clicking the red X to cancel it. That's the same as pressing the Escape key. But all we want to do is total up the numbers that we see up above. Now you might be tempted to type in 2.95 plus 3.11 plus 1.89 plus 2.56. That would give you the right answer.
But what happens if you need to go back and change any of these values? Well the answer would not change down below. So instead, what we want to do is add up the cells. That means using the cell addresses such as C6 plus C7, and so on. So that's exactly what we're going to type in. C6 +, you can see what's happening already, it's highlighted in its own color, C7, it gets its own color and it's highlighted, + C8, it gets its own color, and + C9.
There you go, you get all four of those cells showing up. Press Return and it's locked in. 10.51 is the answer that shows up. Now if we go back to that cell, you can see the formula that starts with an equals sign adds up the cell addresses. Let's go to cell C6 and change that value to 3.95. Let's say that was a typo. 3.95 is the real value. Press Return, and you can see the total automatically updates. That's because we're using cell addresses as opposed to those numbers.
All right, there is another option. Let's go back to cell C11. Instead of typing it out like that, there are built in functions here in Excel that will do this for you. For example, there's a SUM function that will sum up the numbers the way we typed them out. So let's press the Delete key on the keyboard to remove our formula, and instead, we'll insert a function. Now if you know the function, all you need to do is start with the equals sign and type it in, sum. And you can see as soon as we do, it shows up as one of the functions.
If you click it, look what happens. There are brackets that are created for you, your cursor's flashing in the middle waiting for you to type in the range, or here's the shortcut, select the range. All we want to do is sum up these four numbers. So we click and drag over those four numbers and release, press Return on keyboard, and there's our answer. Let's click back in cell C11 so you can see on the formula bar the function is =SUM(C6:C9). That's the range that's being totaled up.
Very cool. Could there be a faster way even? Yeah. Let's delete this again, pressing the Delete key on your keyboard, and instead go up to the Formulas tab because there's something called AutoSum. AutoSum appears here in this group. By default, it should be the one you're seeing. Just hover over it and you can it's Sum. But there is a drop down, and if you use averages, or you count numbers, or find the minimum and maximum numbers in a range, that shows up as the button by default, but if you've never used this before, Sum is the one that shows up.
Or you can just click it from the drop down. Watch what happens. It automatically goes looking for numbers either to the left, to the right, above, or below, and in this case, it finds some numbers above. And as you can see, it's going to create that SUM formula using the SUM function, and the range, it's actually including an empty cell so we can leave it like that or we can edit it. All we have to do to edit it is to come up here, take out the 10, and put in a 9, press Return, and there's our answer.
AutoSum's kind of cool. Now wouldn't it be nice if we could take that formula and just copy it around to these other columns instead of having to recreate the SUM function. Believe it or not, we can do that using relative addressing, which we'll talk about next.
- Create new workbooks
- Work with text
- Add formulas and functions
- Use Auto Fill
- Format data
- Manage rows and columns
- Share and print workbooks