Example with Absolute Cell Reference in Excel (4 Apllications)

In this article, we will let you know what Absolute Cell Reference in Excel is and describe it with 4 useful examples.


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.

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.

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.

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


Similar Readings


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.

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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo