OK so what is the difference between absolute cell references and relative cell in Excel? Also, why would you need to know the difference between absolute and relative cell references when writing formulas in Excel. To help you understand take a look at the following spreadsheet:
To calculate the amout of tax that I need to pay I will want to multiply the amounts in the Total column with the 20% in column H. The 20% is the amount that I want to fix.
When you have entered in formula in Excel, it is entered by default using relative references. What does "Relative References" mean? To help you understand let's have a look at the formula in D2.
You can see that the formula in Cell D2 is multiplying cells B2 and C2. However, now I want to copy the formula down to the cell below, to cell D3. Now I could enter the formula in again, adjusting for the next row down so that it reads =B2*C2. The problem with typing this is that it takes an awfully long time and it might not be the best use of my time. So what I usually use is a technique called AutoFill this technique involves using a small square in the bottom right corner of the formula to drag down to the other cells. An example of this can be seen below:
Now, if you double click on cell D3 you will see the formula to calculate the total for row 3.
Go ahead and check the formulas all the way down the sheet. You will see that each formula has adapted to take into account the rows adjacent to it. For example, D4 reads =B4*C4 and so on. As a result you don't have to type what is basically the same formula for each row in the Excel spreadsheet. In other words the formula is relatively referencing the cells referenced in the formula. Hence the term relative reference.
Alright! I think that you know should have some idea of what relative reference are. So if what we talked about above are relative references what are absolute references?
Absolute references fixes a reference in a formula. That is when you AutoFill down it doesn't refer to the adjacent cells. An absolute reference will only refer to a particular row or a particular column depending on how you fix it.
To make a cell in a formula absolute you would add the dollar sign "$" in front of the particular row or column that you want to make absolute. You would want to do this in the case of the Tax to Pay column:
To calculate the Tax to Pay we would multiply the total in D2 with the Tax rate in cell H1, which is 20%. The formula would look like:
The above formula would work fine for cell E2. However, when you come to AutoFill the column down we'll get a problem.
As you can see, when we drag the formula down the calculation fails to calculate the tax for the other ice cream flavours. Why is that?
To find the answer, double click on cell E3 and have a look at the result.
As you can see the formula is trying to get the tax rate from Cell H2. When it should be getting the tax rate from cell H1. The formula has slipped down or "Relatively Referenced" down a cell. We learnt from the previous example that relative referencing is fantastic should we what to relatively reference both of the cells in our calculation. But here we want to have the D2 part of the calculation relatively reference, while the H1 part of the calculation will be at absolute reference.
To rectify this problem we can add a dollar sign in front of the row number in the H1 part of the formula.
Now that we've added a dollar sign before the 1 in the formula that part of the formula is set as an absolute reference. In fact, because the dollar sign is only in front of the dollar sign, this convention is referred to as a Mixed Reference. This is because although the formula is fixed on the row, it is not fixed on the column. This means should we AutoFill the formula to the left or right, this formula would not work.
But as we have no intention of AutoFilling the formula left and right, we're only interested in AutoFilling down. We only need place a dollar sign in front of the row number.
Once you have added a dollar sign before the 1 in the formula, you can now AutoFill the formula down.
If you want to can use the F4 key on the keyboard to automatically place the dollar signs. With each press of the F4 key you will cycle through the different places within the formula you can place dollar signs. One press places dollars before the column letter and the row number. A second press, places the dollar sign in front of the row number. A subsequent press places a dollar sign before the column letter. The final press removes the dollars signs altogether.
OK then, what is the difference between absolute cell references and relative cell references? In a nutshell, it's the dollar sign. You would place dollar signs within your formula to convert the formula into an absolute cell reference when refering to other cells.
Now that you know the difference between absolute and relative cell references, can you think of where you can apply them in your everyday life?
If you want to know a little more about using absolute references you can view the support page on the Microsoft web site.
You may have to click on enable editing in Excel to be able to complete the exercise.