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

**Download Workbook**

You can download the free practice Excel workbook from here.

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

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

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 (4 Useful Examples)**

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

**Similar Readings**

**Difference Between Absolute and Relative Reference in Excel****Different Types of Cell References in Excel (With Examples)****Relative and Absolute Cell Address in the Spreadsheet****Mixed Cell Reference in Excel (4 Examples)****[Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)**

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

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