Difference Between Absolute and Relative Reference in Excel

In Excel, there are two main types of references: Relative and Absolute (although there is another reference type called a mixed reference). In this article, we will discuss the difference between absolute and relative references. To demonstrate these concepts, we’ll use the following dataset, containing columns for Employee Name, Base Salary, Transport Fee, Provident Fund Rate, and Provident Fund.

What Is a 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 an Absolute Reference in Excel?

• What Is an 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. For example, $C$5 means both the row and column are absolute.

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.

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

Difference 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.

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.

Difference 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 change the row and column value automatically.

• To find the Total Salary we need to add the Base Salary (Basic Salary) and the TransportFee. We will sum C4 cell and D4 cell in E5 cell just by selecting those cells.
• The formula is: =C5+D5

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 Signs ($) like $F$5 and drag the Fill Handle, it will calculate the Provident Fund for the rest of the cells.

• We also can use absolute row F$5, since the column value is constant.

Difference 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 selected the E5 cell and dragged the Fill Handle to the range (E6:E11). Here the same sum occurred but the cell numbers changed respectively.

• Due to the relative reference, the formula moves with the cell. In E11, the cell reference automatically adjusts to the corresponding row C11 and D11.

Copying a Formula Using Absolute Reference:

When you use absolute reference for a cell, copying the formula doesn’t change the absolute references. 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?

• For a Relative reference, it is easy to use and copy the formula anywhere in the cell. We wrote the formula in the first cell of Total Salary then copied it in the rest of the cells.

• With an absolute reference, there is a matter of constant/fixed value. Here we kept the Provident Fund Rate fixed using Dollar Sign ($) and then copied it into the rest of the cells.

How to Switch Between Relative and Absolute Reference in Excel

The F4 key on the keyboard places 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 into an absolute row F$4.
• Three presses of F4 on the keyboard will make the cell into an 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.
• Relative references will change while copying the formula according to the row or column variable.

Download the Practice Workbook

Related article