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

**(Basic Salary) and the**

*Base Salary***. Now, I will sum**

*Transport Fee***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

**and**

*Total Salary***Â I used the formula**

*Provident Fund Rate*.

` =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.