How to use absolute cell reference in excel (Examples and Guide)

When we wish to fix the position of the selected cell in a formula so that its value won't change whenever we move the cell or copy the formula to other cells or sheets, we use absolute references in Excel. The dollar ($) sign should be used both before and after the column name of the chosen cell to do this. Alternatively, we can type the F4 function key, which will immediately cover the alphabet of the column with the dollar. For instance, cell A1 will appear as =$A$1 if it has to be fixed.

In Excel, a cell address with a dollar sign ($) in it is referred to as an absolute cell reference. The column reference, the row reference, or both may come before it. In Excel, we can maintain a constant row, column, or both with an absolute cell reference. When transferred to additional cells, it remains unchanged.

How to use absolute cell reference in excel online, 2016 and 2019

 The names of some employees and the matching number of hours they worked are listed in the data below. Everybody receives the same fixed hourly pay.

Using example 1

1.The gross salary must now be determined based on the number of hours worked.

2.Hourly Wage Rate = $10.00We have a fixed hourly wage of $10.00. We will now figure up the gross compensation for each employee.

3.As shown in the screenshot above, to get the gross pay for employee John, we will multiply the number of hours worked (B20) by the fixed-wage hourly rate (B16) in cell C20. The outcome is:

4.As shown in the screenshot below, if we drag this formula for the remaining employees, the results will be inaccurate.

5.The value of a set hourly wage rate may be seen fluctuating for each employee in the screenshot above. However, all employees receive the same fixed wage rate. Cell B16 will serve as an absolute cell reference going forward. Please view the image below:

6.The column name and the row number will both be preceded by the dollar sign $, as in $B$16.The result is:

7.By doing this, we secure B16's value for all employees. Now, when we drag this formula for the remaining employees, it will produce the right results.By dividing the total number of hours put in by all employees by the hourly rate for a set wage, the gross compensation is calculated. Please look at the image below:

Absolute reference in excel using example 2

1.Ritika goes to a store and purchases some goods and cosmetics. She selects the following around 15 items:

2.The Goods and Services Tax (GST) rate, which is 10%, is applied to each item. We must figure out the tax rate for each item in order to determine the Net Amount.By multiplying the quantity of each item by the appropriate product price, we will first determine the total cost of each item.

The result is:

3.For the remaining goods, drag this formula.

4.We must now multiply the total price by the GST rate in order to determine the tax rate for each product.GST is charged at 10%. Since GST is fixed for each item, we shall utilise an absolute cell reference in this case. Consequently, we will use key F4 to create cell E33 as an absolute cell reference.

The result is:

5.Drag this formula now to the remaining objects.

6.The total bill can be obtained by adding the total cost and GST.

7.So, according to the screenshot below, the result is Rs. 300.60:

Note: This above written article is an attempt to show you how to use absolute cell reference in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.