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.

**Table of Contents**hide

## 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