Function of Absolute Cell Reference in Excel 2013 Formula

The function of Absolute in excel formula is used to lock the position of columns and rows. This function is characterized by a $ sign that was in front of the column and in front of the row. There are two functions that can be used, namely Absolute cell reference and Semi Absolute cell reference.

Absolute Cell Reference
There is a Data Items consisting of Item Name, Unit Price and Total Price. Every item purchased by 2 items, so each of item Unit Price multiplied by 2 to get the total price. In order to simplify the calculation process, we type a number 2 as the master of multiplication. So each unit price multiplied by the number 2, located in column B11. In order not to change, we are required to add the dollar symbol ($) before the letter B ($B = function $ to lock column B) and before item 11 ($11 = function $ to lock row 11). So we get the result of the multiplication.

Semi Absolute Cell reference

Do you remember the multiplication table??? First, we create a multiplication table with a composition as shown at above table. For starters, try to find the answer from the cross product 1 x 1, it will be known function formula B1*A2. Then try again with the cross product between 2 x 2, it will be known function formula C1*A3. After that, look again in detail, what is the difference of the two function formulas?.

The difference in the formula is located at one of the reference of the cell, but the cell reference also have in common. Of the differences and similarities of that, we can use the function formula Semi Absolute reference to speed up the calculation of the multiplication table.

The formula of Semi Absolute reference used to lock one column or row at a cell reference. As an example :
• $C9: Locking column C, in this way when the cell is copied to right cell reference will remain readable as $C9 not D9, but when copied down the cell address will change to $C10.
• C$9: Locking the line 9, in this way when the cell is copied down the cell reference will remain read as C$9 instead of C10, however when copied to the right of the cell reference will change to D$9.

For its application, the first function formula B1*A2 add $ in front of the number 1, and in front of  the letter A to B$1*$A2. Then we can try to pan (drag) the mouse down and then laterally as shown below picture.

The function of absolute cell reference in Excel formula have a function to lock columns and rows used in the formula. By locking the columns and rows, it will facilitate the process drag the mouse to the right or down cell reference.

Post a Comment for "Function of Absolute Cell Reference in Excel 2013 Formula"