Adding formulas and functions

- We've added some text. We've added some numbers. The next logical step is to insert formulas or functions that will perform calculations on the numbers we've added. In our Landon Hotels revenue sheet here, that means summing up the different columns. If you need to get caught up, open up RH_Revenues0104 from the chapter 1 folder of your exercise files, and click in cell C11. This is where we want our answer to go, which is the total of these four numbers under the January column. Now, there are different ways to create formulas, but they all start the same way, and that is with the equal sign. Type in the equal sign, and your formula is under way. You'll also notice on the formula bar the equal sign appears. We could build our formula up here. We can use the X to cancel out of what we're typing in, accept, or press the Enter key on your keyboard to accept the formula you've entered. We can continue down here in the cell, building our formula. Eventually though, all we're going to see here is the answer. So you might be tempted to type in, "2.95," then a plus sign, "3.11," and so on, until we get all four numbers entered. Pressing Enter would give you the right answer. But if you went back to any of these cells and changed one of those numbers, your formula would not update automatically. You'd have to edit it, as well. So we're going to Backspace over those numbers. Instead, it would make better sense to add up the cells, so whatever's in the cell is what gets added up. In this case, we want C6, so we'll type in, "C6." As soon as we do, you can see it's highlighted. It knows that's the number we want to use. Now add the plus sign, type in, "C7." Now add another plus sign, C8. Another plus sign, and C9. Notice all four numbers are highlighted in their own colors. We can press Enter, or you could click the checkmark up here next to the formula bar to get the answer, which is 10.51. When we click that cell, we can see the formula up above in the formula bar that gives us that answer. That means if we go to London, for example, under January, where we entered the incorrect value, and change it to 3.95 and press Enter, you can see our total is updated automatically because we added up the cells, not the numbers in those cells. All right, now there might be another way to do this. If we go back to cell C11, you can see that's a lot of typing. If we had multiple locations, you wouldn't want to have to type out all of these cell addresses. Instead, there are built-in functions that are part of Excel that give you a bit of a shortcut. Press the Delete key on your keyboard to remove the contents from cell C11. Instead, we're going to use one of the built-in functions. Hit the equal sign, and now type in the word "sum." You can see, there are some options down below, functions that begin with the word "sum," including the sum function, which adds all the numbers in a range of cells. That's exactly what we want, so double-click it. It's in there now. You can see, there's a round bracket, and all we need is the range. Now, we could type in the range. We could type it in, C6:C9, and we'd get all those numbers. Or even easier, just click and drag across the numbers you want to total up. So we'll click and drag from C6 down to C9. You can see, it's building the formula in the cell and on the formula bar. All we need now is a closing round bracket. Let's add that and press Enter. There's our answer, and that was much faster than typing out all of those cells with plus signs. You can see, when we click in cell C11, the sum function makes it a lot faster to get the answer. Could there be an even faster way? Well, for some functions, there are some further shortcuts. Let's hit Delete one more time. We'll remove that function and instead go to the Formulas tab up here on the ribbon. You'll see something called Autosum. If you click the drop-down arrow, there are other ones too, like average, counting up numbers, finding the maximum or minimum numbers in a range. But this is the one we want, Autosum. We can click the top half of the button, or from the drop-down choose Sum. Watch what happens. Automatically, this function goes looking for numbers. Are there any to the left? No. To the right? No. Down below? No. But there are some up above, and that's why that range is highlighted there. It includes the blank row as well, because it's right above our cell in cell C11. So we could press Enter and get our answer, or we could do a little editing if we wanted to. Instead of C6 to C10, we could change that. Just click right in the cell itself and take out the 10 and type in a 9. You can see the new range is highlighted. That's what we want. Press Enter, and there's our answer, thanks to Autosum. So you can type out your formulas manually. You can use built-in functions. There are many, many functions to help you along. Some of them are automated, like Autosum. Now all we need to do is get that same formula or function underneath February and March. The fastest way to do that is to copy it, using relative addressing. We'll talk about that next.