Join David Rivers for an in-depth discussion in this video Adding formulas and functions, part of Learning Excel for Mac 2016.
- 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 Hotel's revenue sheet we've been building here. We're going to start in cell C11. If you 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 equal 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 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 got 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 equal 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 equal 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 is 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 the 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 see it's Sum. But there is a dropdown 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 dropdown. 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 is 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 nine. Press Return and there's our answer.
AutoSum is 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