• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Part 3. Using Excel > Task 17 How to Use Absolute and Relative Cell Addresses

Task 17 How to Use Absolute and Relative Cell Addresses

When you copy a formula to a new location, the formula is automatically adjusted using relative cell addresses. For example, if you copy the formula =C2+C3 from cell C4 to cell D4, the formula is changed to =D2+D3. The addresses are adjusted by one column because you copied the formula one column over. If you don't want a cell address to change (which is true when the address points to a value that the formula is required to use), use absolute cell addresses. To make an address absolute, precede it with a dollar sign ($), as in =$C$2+C3. If you copy this formula from cell C4 to cell D4, the formula changes to =$C$2+D3. The absolute address, $C$2, does not change.

  1. Enter a Relative Formula

    To demonstrate the difference between relative and absolute references, we'll enter a formula that uses only relative addresses: =SUM(C7:F7).



  2. Copy the Formula

    Copy the formula down the column using the AutoFill feature. The addresses used in the formula are adjusted to reflect the row number to which the formula was copied.



  3. Enter an Absolute Formula

    Now we'll enter a formula that uses absolute addressing: =G7-$C$3. The absolute address $C$3 points to the sales goal value that all the formulas, regardless of which row they are in, must use. To enter the formula, type the first part of the formula (=G7-), click cell C3, and then press F4. Pressing F4 makes the cell address to the left of the cursor absolute.



  4. Copy the Formula

    Use the AutoFill feature to copy the formula down the column again. This time, notice that the first address, G7, changes based on the row the formula is in. The second address, $C$3, does not change because it is absolute.



  5. Enter a Mixed Formula

    A mixed reference is one in which only the column or row number is absolute. This example uses a formula with a mixed reference. Type the first part of the formula (=C7-), click cell C4, and press F4 twice to make just the row number absolute: C$4.



  6. AutoFill the Formula



    Use the AutoFill feature to copy the formula down to row 18 and then across to column F. Notice that the mixed address changes to reflect the column to which it is copied, but not the row.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint