One of the most important things to understand before you get started with doing things in Excel, is to understand the referencing modes. In Excel, there are three cell addressing modes. There's the relative referencing mode, there's absolute referencing and then there's mixed cell referencing. I'm going to go through examples of all three of those in this screen cast. So I'm over here in Excel, I'm going to go ahead and put in a couple of numbers here. I'm going to put in 4.3 and negative 2.1. First of all, what is a cell reference? Well, this cell, this cell that has 4.3 is known as A2 because it's in column A. This is column A and it's in row two. This entire row is row two. So the crossing of column A with row two is known as cell A2. This other cell here below A2 is known as A3. So those are the cell addresses. We have A2 and A3. We use these various cell addresses in formulas. For example, I can write here in cell B2, I can reference by either directly typing in A2 on the keyboard or you can point and click over here to cell A2. I'm just going to take that to the third power. You'll learn a little bit more about what these different types of things are, how to exponentiate, multiply, divide, subtract and add in some subsequent screen cast but this carrier here just denotes exponentiation. So I'm taking cell A2 and taking it to the third power or cubing it. Then when I press Enter, we make that a permanent number here. That's the result. I can always go back into a cell to see what formulas in that cell by double-clicking. By the way, you can always press escape to get out of there or you can go up here to the Formula Bar and you can click in there to see what formula is in cell B2. Now this reference here, if I double-click in here, this A2 is known as a relative reference. If I take this cell B2 and I copy. So what I'm doing here is I'm going down to the lower right corner here. The big empty plus sigh becomes smaller, solid plus sign in the lower right-hand corner of the cell, the cell that's highlighted. So if I click there, if I left-click and drag, so hold that and drag down, I can copy that formula down. You notice that this is now a different number. I didn't take the value in cell B2. I took the formula and copied it down. So let's go ahead and double-click into cell B3 and now you see that the A2 that was in cell B2 has changed to A3 that's in cell B3. That's because the A2 in cell B2 is known as a relative reference. When I type the formula into cell B2, I reference the cell that was just left of it. So one column over on the same row. So when you drag that down, it's also so now my cell is B3 and I'm referencing the cell that's just one to the left on the same row. So that's known as a relative reference. So I'm going to go ahead and clear the contents here. You can do that using the Delete button, or you can right-click and do clear contents and instead, I'm going to double-click in here and I'm going to put my cursor right in the middle of the A and the two and I'm going to press F4. The F4 key once it puts these dollar signs on to the left of A and to the left of two. Whenever you have a dollar sign in front of either the column letter or the row number, that means it's an absolute reference. I like to think of the dollar signs as thumbtacks. So a thumbtack before it means you're pinning it, making it an absolute reference. Let's also press F4 a couple more. Let's press it once more and you see that now there's only a dollar sign in front of the row and I can press it again and now it's just in front of the column. These are known as mixed cell addresses. Then I can press F4 again and it goes back to a completely relative reference. So you can toggle through using the F4 button. So let's leave it at the fully absolute reference, dollar sign A, dollar sign two. You can also manually type in the dollar signs if you want. Now I'm going to press Enter. Now I'm going to take that formula in cell B2, and I'm going to drag down like we did before. Let's go ahead and double-click in there. Now you notice instead of bringing that cell down and instead of having A3 cubed, we have A2 cubed because A2, when we type that into cell B2, this is an absolute reference we've thumb tacked or made those references absolute. So wherever we take that formula, I can even do Control C and paste it somewhere else over here. We're always going to take cell A2, and we're going to cube it. So that's known as an absolute reference. There's also something known as a mixed address, which I referenced a few minutes ago. So let's talk about what a mixed address is. I've typed in 10,4, -7 and 13 into cells D2, E2, D3 and E3. Now what I'm going to do in cell F2, I'm going to type in the following formula. When I press return or enter, you should see that that equals 14. So that's pretty straightforward. We're adding D2 to E2. However, if we look at the references, these are known as mixed references because only one of either the column or the row is absolute and the other is relative. So we have D dollar sign two that's making the row always going to be row two. In the second one, we have dollar sign E2. That makes it, it's always going to be column E. Now what we're going to do is right-click on this and do copy and then we're going to go down to G3 and I'm going to do right-click, paste here, and we get 17. When we double-click here, you see that the reference is E dollar sign two plus dollar sign E3. So let's take a look back in our F2 formula. This D2, we're going down one row and over one column. So if the D2 or just fully relative, then D2 would become E3. But since we're pinning the two, the D when we go over one column becomes an E because that's a relative reference. But the dollar sign two when we go down one row doesn't become three, it remains dollar sign two. So D dollar sign two when we go down one row over one column becomes E dollar sign two. You can see that here. We have E dollar sign two. The dollar sign E2 when go down one row and over one column, if this were fully relative, that is just an E2 with no dollar sign, it would become F3 but since we've pinned or made the E an absolute reference, the E's never going to change regardless of how far over we go. So the dollar sign E remains a dollar sign E but the two, we go down one row and that becomes a three. So that becomes dollar sign E and three. So that's known as a mixed address. So you'll get a lot more experience with these types of referencing modes as we work through other examples.
