Difference Between Absolute and Relative Reference in Excel

In Excel, there are mainly two types of references: Relative and Absolute (although there is another reference called mixed reference). In this article, I am going to explain the difference between an absolute and relative reference.

To demonstrate it in a more understandable manner I am using a sample datasheet. There are 4 columns which are Employe Name, Base Salary, Transport Fee, Provident Fund Rate, and Provident Fund.

Workbook to Practice

Difference of Relative and Absolute References

Relative Cell References 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 in 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.

I. Relative References in Excel 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, I will sum C4 cell and D4 cell in E4 cell just by selecting those cells.

Here the formula is =C4+D4

The sum of two cells is $3100

II. Copy a Formula Using Relative References

Now I want to do the same sum for the rest of the cells. I will copy the E4 cell and will drag the right icon to the range (E5:E6). 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

 III.When to Use 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 then copy it to the rest of the cells where needed.

Absolute Cell References in Excel

An 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 AutoFill. By using the Dollar Sign ($) you can make any cell an absolute.

I. Use of Dollar ($) Sign

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. That makes a cell reference absolute.

Absolutely Absolute

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$4, both the row and column are absolute.

Absolute Columns

To make a column reference absolute, put a Dollar Sign ($) before the column. Here $C4 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.

Absolute Rows

To make a row reference absolute, insert a Dollar Sign ($) before the row. Here C$4 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.

II. Absolute Reference in Excel 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 =E4*F4

If I copy the formula to the rest of the cells it won’t show any value as respective rows have no values.

If I make the F4 cell (that contains the provident fund rate) Absolutely Absolute putting Dollar Sign ($) in front of $F$4, it will calculate the Provident Fund of the rest of the cells.

You also can use absolute row F$4. It will give the same result as absolutely absolute because here the row value needs to be constant.

III. When to Use 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.
• We can use absolute references in problems like commission, tax rate, provident fund rate, number of months, etc.

Key Difference Between Absolute and Relative Reference

You might have understood the key difference between the two references, just to clarify you I’m mentioning 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.

Relative Reference

In absolute reference, there is a matter of constant/fixed value. Here I kept the Provident Fund Rate fixed using Dollar Sign ($) then copied it in the rest of the cells.

Absolute Reference

Switch Between Relative and Absolute Reference

By using the F4 key on the keyboard we can place a Dollar Sign ($) in front of rows and columns.

• One press of F4 will make the cell absolutely absolute $F$4
• Two presses of F4 will make the cell absolute row F$4
• Three presses of F4 will make the cell absolute column $F4

By removing the Dollar Sign ($) from an absolute reference we can make it a relative reference.

Conclusion

In this article, I explained the difference between relative and absolute reference in Excel. Here you will find the reason why these two references are different and how you can use them while copying any formula. You are most welcome to give any kinds of suggestions, feedback, ideas. Feel free to comment down below.