Example with Absolute Cell Reference in Excel (4 Apllications)

Get FREE Advanced Excel Exercises with Solutions!

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


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.

Convert One Currency To Another with Absolute Cell Reference in Excel

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.

Convert Currency with Absolute Cell Reference in Excel

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.

Read More: 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.

Absolute Cell Reference To Calculate the Hourly Gross Pay in Excel

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

Absolute Cell Reference To Calculate Hourly Gross Pay in Excel

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.

Calculate the Net Amount with Tax Rate for Product in Excel with Absolute Cell Reference

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.

Calculate the Total Price with Tax Rate for Product in Excel with 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.

Compute Summary Sales for Sales Person of an Organization with Absolute Cell Reference in Excel

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.

Calculate Summary Sales for Sales Person of an Organization with Absolute Cell Reference in Excel

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.


Related Articles


<< Go Back to Absolute Cell Reference | Cell Reference in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo