Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We're going to learn about relative and absolute referencing by using the StoreA worksheet. So you can open that now. Before we learn about referencing, I want to review the concept of a cell addresses. And what a cell address is, is the identifier that distinguishes this particular cell here, G2, then any other cell in the whole spreadsheet. The cell address denotes the column that it's in and the row that it's sitting in. So, it's G2 is this cell here. The name of that cell is over here in the name box. So that's that cell address that I'm looking at.
Now when you use cell addresses and formulas, it gives you a lot of power. If I use my arrow keys and move over to cell E2, you'll see that this particular formula was created using cell addresses. I'm seeing D2*C2. Now as a little review, you'll know that this is a formula because it starts with an equals sign and it's using a operator. In this case it's the multiplication operator that is the asterix. So this formula is telling Excel to take the value that's sitting in cell D2, which is 15, and multiply it by the value that's sitting in cells C2, which is 525.
So if I take 525, multiply it by 15, I get 78.75, which is the answer that you get when you do this calculation. Now, the reason I put in these cell addresses rather than the numbers to do the calculation, is because gives me the ability to use relative referencing. What relative referencing does, is it says, "When you copy this particular formula into other areas of your spreadsheet, use the same relative addresses." Which means the very first cell that you're going to get is one to the left, because that's where D2 is sitting in respect to where I'm putting the answer.
So move one cell to the left, and multiply it by the information that's in two cells to the left. And put the answer in the cell that you're working in. How does this look in real life? Well if I moved down the road to row 3, the relative referencing is one cell to the left, D3, times two cells to the left, D3. If I go down one more row, still keeps that same relative referencing. One row to the left times two cells to the left and so on and so on and so on down my column.
So that's what relative referencing does. If I copy this by selecting the cell E2, right-clicking and selecting Copy, go to cell F2, right-click and say Paste. You'll see that the relative answers are placed in here. So it's going once cell to the left times two cells to the left. Notice now I'm looking at a whole lot bigger numbers in this case because I'm multiplying my final costs times a larger inventory. But that that's how easy it is to take the information that's in that cell and copy it.
I'm going to delete that. Now the difference between relative referencing and absolute referencing, means that absolute referencing always goes back to the same cell. It kind of pins that cell in the calculation that you're going to do. You identify absolute referencing by the use of dollar signs. So let's use the same formula here, but use C2 has an absolute reference. So if I type in the formula D2*--an absolute reference is indicated by putting in a dollar sign-- c$2, and I click the Accept sign, it places that particular formula in cell F2. if I move down one cell, noticed now that this is different than the other type of referencing I had.
If I compare the formula in cell F3 to the formula that's in cell F2, you'll notice that the first section has moved and uses relative referencing. So I've gone to D3 in both cases, but in the second one I've still gone back using the value that if find in cell C2. And if you scroll down through the column, you'll notice that cell C2 stays as a absolute reference throughout every single row. Even though the first reference is using the relative referencing and it moves with each row. So it's always going back to the value that it finds in cell C2 to do the calculation.
This is the difference between a relative and absolute referencing. We'll be reviewing this concept again as we go to take a look at working with formulas in other movies.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86768 Viewers
80 Video lessons · 136033 Viewers
59 Video lessons · 54758 Viewers
52 Video lessons · 68651 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.