The formula B2*C2 gives the sales revenue for product-1. To calculate the sales revenue, we multiply the number of units sold by the unit price. We want to calculate the sales revenue by using the equation Units Sold*Unit Price=Sales Revenue. Let us consider another example of relative references in Excel.
Thus, cell B3 applies summation to the values of cells B1 and B2. After copying cell A3, as we move one cell to the right, A1 becomes B1 and A2 becomes B2. The output of cell A3 depends on cells A1 and A2. This is because cell A3, which is copied, contains a formula, not a value. On copying cell A3 and pasting in cell B3, the answer is not 100. We can either apply the Excel formula of addition to cell B3 or copy and paste the formula of cell A3 to cell B3. Thus, in the above example we see that the address of cell C2 is not changed whereas the address of column A and B changes with the relative position of the row and column, this happened because we used the absolute address of the cell C2.Now, with different values of cells B1 and B2, we want a summation in cell B3. Step 5: You can double-click on any cell to check that the operation is performed in between which cells, and we see that the address of cell C2 does not change. Step 4: Now we can see that the percentage is calculated in column D. In our example, we will drag it till cell D10. Step 3: Drag the Fill handle up to the cells you want to fill. Step 2: Now click on the Fill handle at the corner of cell which contains the formula(D2). In this example, we write the formula(=(A2+B2)*$C$2) in cell D2 and press enter to calculate the formula. Step 1: We write the formula in any cell and press enter so that it is calculated. Here, we will use absolute reference so that the address of cell C2 remains constant and does not change with the relative position of column and rows. $A$1, $B$3 are examples of absolute cell reference.įor example, We want to multiply the sum of marks of two subjects, entered in column A and column B, with the percentage entered in cell C2 and display the result in column D.
You can also press the F4 key to make any cell reference constant. If either the row or the column is made constant then it is known as a mixed reference. The absolute reference does not change as you copy the formula from one cell to other. Absolute ReferenceĪbsolute reference is the cell reference in which the row and column are made constant by adding the dollar ($) sign before the column name and row number. Thus, in the above example, we see that the relative address of cell A2 changes to A3, A4, and so on, similarly the relative address changes for column B, depending on the relative position of the row. Step 5: You can double-click on any cell to check that the operation is performed in between which cells. Step 4: Now we can see that the addition operation is performed between the cell A2 and B2, A3 and B3 and so on. In our example, we will drag it till cell C10. Step 2: Now click on the Fill handle at the corner of cell which contains the formula(C2). In this example, we write the formula(= B2 + A2) in cell C2 and press enter to calculate the formula. Here, we will use relative reference so that the same rows of column’s A and B are added. Relative references are used when we want to perform a similar operation on multiple cells and the formula must change according to the relative address of column and row.įor example, We want to add the marks of two subjects entered in column A and column B and display the result in column C. C1, D2, E4, etc are examples of relative cell references. When you copy the formula from one cell to another the relative cell address changes depending on the relative position of column and row.
It is simply the combination of column name and row number without any dollar ($) sign. Relative reference is the default cell reference in Excel. There are two types of cell references in Excel: Referencing the cell of other programs is known as Remote referencing.Referencing the cell of other worksheets is known as External referencing.We can reference the cell of other worksheets and also of other programs.
#Relative cell reference excel software#
It helps the software to identify the cell from where the data/value is to be used in the formula. It is the combination of column name and row number.