Page 25 - Trackpad_V2.1_Class6
P. 25

There are three types of cell references used in Excel: Relative, Absolute and Mixed.


                 RELATIVE CELL REFERENCING
                 In relative cell reference, you actually refer to the cell that is above

                 or below and left or right to a number of rows or columns. When a             To calculate total of
                                                                                               adjacent cells:
                 formula is copied to a new location in a worksheet, the value in the
                 copied cell also changes.                                                 Short key

                 For example,  a formula (=A2*B2)  placed  in cell C2  multiplies  the             Alt    +   =
                 values of cells A2 and B2. As we press the Enter key, the result of the

                 multiplication of values in A2 and B2 is displayed in cell C2.
                 If we copy the formula (=A2*B2) in cell C3, then the cell references in the formula (=A2*B2) change
                 in relation to the new location of the formula, and the result is displayed according to the changed

                 cell reference.

                                                                       After copying and pasting the formula in cell C3,
                         Result of formula (=A2*B2) in cell C2           cell references are automatically adjusted.






















                 ABSOLUTE CELL REFERENCING

                 In  absolute cell referencing, cell references in the        Typing the formula (=$A$2 * $B$2)
                 formula remain the same even if we copy and paste the        in cell C2 for absolute referencing
                 formula to a new location. It is used when you maintain
                 the original references as they were. For this, we need
                 to use the $ (dollar) sign as a prefix before the column
                 name and row number in the formulas.

                 For example, if we want the value of A1 to be  constant
                 when multiplying it with a cell from column B, absolute

                 referencing will be used.
                 When the formula (=$A$2*$B$2) in cell C2 is copied
                 and pasted to C3, the values in cells A2 and B2 remain        Displaying constant result after copying
                 unchanged.                                                        and pasting formula in cell C3




                                                                                 Formulas and Functions in Excel 2016  23
   20   21   22   23   24   25   26   27   28   29   30