In Excel, there are mainly two types of references: Relative and Absolute (although there is another reference called mixed reference). In this article, we will discuss the difference between absolute and relative references.
To demonstrate it in a more understandable manner I am using a sample datasheet. There are 4 columns which are Employee Name, Base Salary, Transport Fee, Provident Fund Rate, and Provident Fund.
What Is Relative Reference in Excel?
In Excel, columns are represented Alphabetically, and rows Numerically. To reference the first cell, you can use A1 where A is the column and 1 is the row.
In Excel, a cell reference is a Relative reference. A cell can be referenced just by specifying the row and the column value. Relative references adjust and change themselves automatically when a formula is copied to another cell or while using AutoFill.
What Is Absolute Reference in Excel?
Absolute reference is a fixed reference in a formula. It will only refer to the specific row and columns. It won’t change when the formula is copied to another cell or while using Fill Handle. By using the Dollar Sign ($) you can make any cell an absolute.
To make a cell reference absolute we use the Dollar Sign ($). Depending on which types of absolute we need we use the Dollar Sign ($). When a Dollar Sign ($) is added in front of the row or column it means the cell’s values won’t change while copying it to the other cell. Therefore, it makes a cell reference absolute.
- What Is Absolutely Absolute Reference?
When we want to make both row and column constant then we use the Dollar Sign ($) in front of both row and column, it makes the reference Absolutely Absolute. Here $C$5 means, both the row and column are absolute.
- What Is an Absolute Column?
To make a column reference absolute, put a Dollar Sign ($) before the column. Here $C5 is an absolute column reference as the Dollar Sign ($) is in front of the column so the column will be constant. We also can call it a mixed reference as the column is absolute, but the row is relative.
- What Is an Absolute Row?
To make a row reference absolute, insert a Dollar Sign ($) before the row. Here C$5 means it is an absolute row reference as the Dollar Sign ($) is in front of the row so the row will be constant. It is also a mixed reference.
Relative and Absolute References in Excel: 3 Differences
First, we need to know what Relative and Absolute reference is. Then we can discuss the differences.
1. Application of Relative and Absolute References in Excel
Application of Relative Cell References in Excel:
- When we need to create a formula for a certain range and the formula needs to refer to a relative cell reference then we can use it.
- We can create a single formula for one cell and then copy it to the rest of the cells where needed.
Read More: How to Keep a Cell Fixed in Excel Formula
Application of Absolute Cell References in Excel:
- When we don’t want to change the cell references while copying the formula then we can use absolute cell references.
- When we have fixed values depending on what we want to use formulas.
- When we want to copy the formula keeping the value constant.
- Lastly, we can use absolute references in problems like commission, tax rate, provident fund rate, number of months, etc.
Read More: How to Hard Code in Excel
2. Use of Relative and Absolute References in Excel Formulas
Use of Relative Reference in Formulas:
- In Excel, if you want to use a formula in a particular cell and want to copy it into another cell then you should use a relative reference; it will relatively change the row and column value.
- Here, to find the Total Salary we need to add the Base Salary (Basic Salary) and the Transport Fee. Now, we will sum C4 cell and D4 cell in E5 cell just by selecting those cells.
- The formula is: =C5+D5
- The sum of two cells is $3100.
Read More: How to Use Cell References in Excel Formula
Use of Absolute Reference in Formulas:
- For the below-given datasheet, I want to calculate Provident Fund depending on the Provident Fund rate.
- To calculate the Provident Fund, I will need to multiply the Total Salary and Provident Fund Rate. I used the formula: =E5*F5.
- If we copy the formula to the rest of the cells using the Fill Handle, it won’t show any value like the image below as respective rows have no values.
- If we make the F5 cell (that contains the provident fund rate) Absolutely Absolute by putting Dollar Sign ($) like $F$5 and then drag the Fill Handle, it will calculate the Provident Fund of the rest of the cells.
- In fact, we also can use absolute row F$5. Since the row value is constant 10%.
Read More: How to Make Different Types of Cell Reference in Excel
3. Copying a Formula Using Relative and Absolute References
Copying a Formula Using Relative Reference:
- Now we want to do the same sum for the rest of the cells. We will copy the E5 cell and will drag the Fill Handle to the range (E6:E11). Here the same sum occurred but the cell numbers changed respectively.
- In relative reference, the formula moves with the cell. In E10, the cell reference automatically adjusts to the corresponding row C10 and D10.
Read More: Cell Reference in Excel VBA
Copying a Formula Using Absolute Reference:
When you use absolute reference for a cell, copying formula cannot make any corresponding changes in the copied cell. The cells will supply a fixed value in the new locations too.
What’s the Key Difference Between Absolute Reference and Relative Reference in Excel?
You might have understood the key difference between the two references. Again, just to clarify you, we will mention it here.
- In Relative reference, it is easy to use and copy the formula anywhere in the cell. I just wrote the formula in the first cell of Total Salary then just copied it in the rest of the cells.
- In absolute reference, there is a matter of constant/fixed value. Here I kept the Provident Fund Rate fixed using Dollar Sign ($) and then copied it into the rest of the cells.
Read More: Relative and Absolute Cell Address in the Spreadsheet
How to Switch Between Relative and Absolute Reference in Excel
By using the F4 key on the keyboard we can place a Dollar Sign ($) in front of rows and columns.
- One press of F4 on the keyboard will make the cell absolutely absolute $F$4.
- Two presses of F4 on the keyboard will make the cell absolute row F$4.
- Three presses of F4 on the keyboard will make the cell absolute column $F4.
By removing the Dollar Sign ($) from an absolute reference we can make it a relative reference.
Things to Remember
- The Fill Handle does not affect the absolute reference.
- Pressing F4 will change any relative reference to an absolute reference.
- Lastly, relative references will change while copying the formula according to the row or column variable.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
We discussed the difference between absolute reference and relative reference in Excel in this article. However, if you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.
Thanks for good lesson