In this article, we will let you know what absolute cell reference in Excel is and describe it with 4 useful examples.

**Table of Contents**hide

**What is Absolute Cell Reference in Excel?**

Absolute cell reference in Excel is used when we want to lock the position of selected cells in any formula so that its value will be fixed and wonâ€™t be changed whenever the cells are being copied to other cells or sheets.

This can be done by simply putting the dollar** ($)** sign before the cell references. For example, if you want to lock the value of cell** B2**, then it will look like **$B$2** with absolute cell reference.

**Absolute Cell Reference in Excel: ****4 Examples**

This section describes 4 different examples of how to use the absolute cell reference in Excel.

**Example 1: Convert One Currency To Another with Absolute Cell Reference in Excel**

Suppose you have a dataset consisting of travel costs written in US dollar amounts. Now you want to convert the cost into Euro.

Steps to convert **Dollar currency to Euro** with absolute cell reference are given below.

**Steps:**

**1 USD = 0.88 Euro**, by the time we are writing this article. So we stored the conversion value**0.88**in a separate cell, cell**C11**, which is the**Absolute Cell Reference**number for our example.- Pick a cell to store the updated currency value (e.g. cell
**D5**). - In that cell simply run a generic multiplication formula with the cell containing the currency (
**C5**) and the absolute cell reference (**C11**). Put the dollar**($)**sign before the column and row reference number of the absolute cell reference. So the formula will be,

`=C5*$C$11`

- Press
**Enter**.

You will get the converted currency value in the result cell** D5**.

- Now, drag the row down with
**Fill Handle**to convert every other USD currency into Euro by multiplying them with the absolute cell reference.

As we have made cell** C11** our absolute cell reference so when we are dragging the row down, it will keep multiplying the newly added currency with the fixed value in cell** C11**.

**Related Content:** Absolute Cell Reference Shortcut in ExcelÂ

**Example 2: Absolute Cell Reference To Calculate the Hourly Gross Pay in Excel**

Here we will see how to calculate the hourly gross pay for workers of a company with a fixed hourly wage rate.

**Steps:**

- Same as before, first store the hourly wage rate in a separate cell (
**$10.00**in cell**C11**in our case). cell**C11**is our absolute cell reference. - Now pick a cell to store the updated currency value (e.g. cell
**D5**). - In that cell simply run a generic multiplication formula with the cell containing the currency (
**C5**) and the absolute cell reference (**C11**). This time donâ€™t put the dollar**($)**sign before the column and row reference number of the absolute cell reference. We will see what is going to happen if we write the formula like this,

`=C5*C11`

- Press
**Enter**.

For the first cell, we are getting the correct result. Letâ€™s see what happens if we drag the row down with **Fill Handle** to apply the same pattern of multiplying each cell with the absolute reference.

As shown in the picture above, we are getting error values. Because when we want to calculate a range with a fixed value, Excel only calculates the fixed value with the first input data and then keep updating the result with the cells which come after the absolute cell reference.

Look at the picture above to understand more. In cell** D7** we can see that it calculated cell** C7** with cell** C13**, not with the absolute cell reference **C11**. That is because we didnâ€™t put the dollar** ($) **sign before cell** C11**, so it didnâ€™t lock the value to calculate it with the rest of the cells.

- Now we will calculate the hourly gross pay with the absolute cell reference by putting the
**dollar ($)**sign before cell**C11**.

To calculate the hourly gross pay for John, the formula will be,

`=C5*$C$11`

- Press
**Enter**.

If you drag the row down then you will see it is generating the correct results now.

**Read More:** What Is and How to Do Absolute Cell Reference in Excel?

**Example 3: Calculate the Net Amount with Tax Rate for Product in Excel with Absolute Cell Reference**

In the picture shown below, you can see that we have some Products along with their Price and Quantity. We will calculate the Total Price of those products with a fixed Tax Rate in Excel.

**Steps:**

- In cell
**E5**, calculate the Total Price of the product Television by multiplying its Price by the Quantity.

`=C5*D5`

- Press
**Enter**.

You will get the total net amount of Television.

- Now apply the multiplication to the rest of the cells by dragging the row down with
**Fill Handle**.

As we got the Total Price so now we will calculate the Price with the fixed Tax Rate of 10% with each and every total amount.

- Similarly, the way we are showing you till now, first store the Tax Rate in a separate cell (
**10%**in cell**C11**in our case). cell**C11**is our absolute reference cell. - Pick a cell to store the Price with tax (e.g. cell
**F5**). - In that cell simply run a generic multiplication formula between the Total Price and the Tax Rate with the
**dollar ($)**sign before the absolute cell reference. So the formula will be,

`=E5*$C$11`

- Press
**Enter**.

You will get the Price of the Television with Tax.

- Now drag the row down with
**Fill Handle**to get the Price with Tax for each product by multiplying them with the absolute cell reference.

**Example 4: Compute Summary Sales for Sales Person of an Organization with Absolute Cell Reference in Excel**

Look at the following example. We have some salespeople with their sales amount for 3 months. We will extract each of their sales value per month with the help of absolute cell referencing.

- To get the sales amount of individual sales-person, we need to utilize the
**SUMIFS function**. The formula is,

`=SUMIFS($D$5:$D$13,$B$5:$B$13,$F5,$C$5:$C$13,G$4)`

- Press
**Enter**.

You will get the sales value of only John in cell** G5**.

- Now drag around the rows and columns with
**Fill Handle**to extract the sales amount of each of the workers for different months.

Letâ€™s see the different types of uses of absolute cell referencing with this example.

- We put the dollar sign
**($)**before the column and row reference number of every range**$D$5:$D$13, $B$5:$B$13, $C$5:$C$13**that we search our values because the value we are searching must be in fixed ranges. - But we fixed only the column by writing
**$F5**because we want to fix only column**F**, not the rows. So that it can take the next name from the next row and keep updating it. - And we fix only the row by writing
**G$4,**so only row 4, month names will be fixed, not the columns. So that it can move between months and calculate the results.

**Download Workbook**

You can download the free practice Excel workbook from here.

**Conclusion**

This article showed you 4 useful examples of Absolute Cell Reference in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.