Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Well now with BEDMAS behind us and a look at the operators, let's enter some formulas. The first formula that we have here already totals up the first quarter. The value in that cell is 115, 860 but the formula that created it is seen up here in the Formula bar. Very, very simple. We can do the same thing for the second quarter by entering equals, that's the indicator to Excel that we're entering a formula and I'm going to open up a parenthesis here. This is not necessary for this simple of a formula, but it is good practice, so I encourage you to do that. And we're going enter C2 plus C3 plus C4 plus C5, and we'll close that off with a closing parenthesis. You'll notice for each cell reference that I entered into this simple formula the cell was highlighted and it changed color so that you can match up exactly what cells you're referring to in what part of the formula. It's a very handy intuitive feature in Excel.
When I hit Enter, then I get a total for the second quarter and we're done. Now we can enter the next formula. As you can imagine this is going to take a lot of time to enter them one by one. There must be an easier way and of course their is, you saw it earlier. We did the Copy, Paste from one cell to another. But you're thinking, if I copy this formula to this cell, am I not going to just have the values for the second quarter, over here in this cell? Now Excel is smart enough to move that over. If I grab the rectangle in the bottom corner of my active cell, and I drag that over here to the side, let me complete the third and fourth quarter in one step. I'll release. You notice that the totals are different and if we look here in the third quarter, it's moved the cell references from C2, 3, 4, 5 to D2, 3, 4, 5, and here E2, 3, 4, 5, and that brings us to another point. Cell references can be relative or absolute. A relative cell reference, which is what we have by default, allows Excel a little flexibility to predict your intentions and to update the formulas according to your actions. Since we added up this column here, when we dragged the formula to the right, it simply moved the cell references over to the third and fourth quarter for us assuming that was our intention, and fortunately it was. The difference between a relative cell reference and an absolute cell reference is that an absolute cell reference is fixed and Excel is not allowed to change that. You're the only one that can change an absolute cell reference.
Because the relative cell reference here just saved us some time, it may not seem like absolute cell references are such a good deal, but there are times when you want to always refer to this one cell, or this group of cells, absolutely. No questions. To change a cell reference from relative to absolute, well let's take a look at G2. Here we go, G2. G2 is totaling up the values for the East division. The Eastern division value is 104,740 and all of the cell references here are relative. So if I click and drag that down, it's going to update my formula for the Western division and move the two to a three. Well that's excellent. Let's change those references to absolute by adding the $.
It's a string when you're talking about programming. Here we are, $, and $. Now I needed a $ before the B and I needed a $ before the 3 in order to make both those elements absolute. If I simply added the string symbol to the B, then the reference is going to maintain B at all times regardless of where I move this formula, but the three portion may update. So with the string in front of the B and the 3 now, I'm always referring to that cell and that cell only. So B fixes the column, 3 fixes the row, and I can have any combination thereof. I could fix the row only or the column only. Let's see how that affects our formula as we move it down. So I'm going to click and drag that down. Now it may be hard to do that math in your head, but you can see just by looking at the formula that we're still referring to B3, which is the western sale result for the first quarter. I've got these ones updated because they were relative cell references, so I'm adding this total and this total and this total together, with this total, which of course is incorrect. There's a shortcut key that you can use to change your cell references from relative to absolute. You can highlight any cell reference and tap the F4 key, and that will rotate it to completely absolute, partially absolute, partially absolute, and relative again. You get those four states by just repeatedly tapping the F4 key. If I highlight my entire statement, tap F4, it's going to change that for me and keep rotating through the various states. So that's a fast way you can change your relative statement to an absolute statement, or I can return this formula back to a relative statement, so when I copy it down, let me just hit Enter to fill that in, and that reference is still off by one cell, so let me change that to a 4. There we go. That's the accurate totals for the Northern division and when I click and drag that down, again all the references are relative, so my new formula has the values for row five. As a side note to wrap up this section of relative and absolute formulas, we talked earlier about naming ranges. Whenever you use a named range inside of a formula, it's always absolute.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91192 Viewers
80 Video lessons · 138186 Viewers
59 Video lessons · 57012 Viewers
52 Video lessons · 70651 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.