When you re-create a cell containing a formula, you volition discover that the prison cell references in the formula also move a corresponding number of cells beyond and downwardly. This blazon of cell reference is called a relative reference.

Relative formula

When nosotros copy (Ctrl + C) the formula in the image above, and paste (Ctrl + 5) it into another jail cell, yous'll find that the cell references change from the B column to the D cavalcade, so the total is different.

Relative formula copied

If you want to preclude Excel from changing the cell references when copying cells, you need to utilise absolute references. To create an absolute reference, insert a dollar sign ($) before both parts of the cell reference in the formula y'all want to freeze, every bit illustrated in the following epitome.

Creating a formula with absolute references

There are three unlike types of references, relative, accented, and mixed. The following are some examples:

  1. Relative Reference: A1 tells Excel to alter the cell references to the corresponding columns and rows.
  2. Mixed Reference: $A1 tells Excel you always desire to refer to cavalcade A.
  3. Mixed Reference: B$one tells Excel y'all always desire to refer to row 1.
  4. Absolute Reference: $B$1 tells Excel you always want to refer to cell B1.

There is a autograph method for inbound the dollar signs as y'all select or enter cell references in a formula. Every bit you are typing a formula and finish a prison cell reference, press F4 to toggle between the 4 combinations of reference types. Allow'due south say y'all started typing a formula and y'all typed =100*B1.

  • Printing F4 and your formula changes to =100*$B$1 (always refer to jail cell B1)
  • Press F4 again and your formula changes to =100*B$i (e'er refer to row 1)
  • Press F4 over again and your formula changes to =100*$B1 (always refer to cavalcade B)
  • Press F4 once more and your formula returns to the original relative reference =100*B1 (always change the reference to the respective columns and rows)

You lot can interruption while entering each prison cell reference in the formula to press F4 until you get the right reference blazon for the current prison cell reference.

To copy the formula entered using absolute references and preserve the cell references, select the cell containing the formula and re-create it (Ctrl + C) and click the destination cell into which you want to paste the formula.

Cell copied with relative references

Brand sure the Domicile tab is the active tab on the ribbon. Click the arrow on the Paste button in the Paste section of the Domicile tab. Select Formulas from the drop-down menu.

Pasting a formula

You will detect that the total displays in the destination jail cell from the original prison cell, and the formula that displays in the Formula Bar for the destination cell contains the aforementioned absolute references as the original formula in the original cell.

06_formula_pasted

NOTE: Copying and pasting a formula into a destination cell does not mean that the formula will be updated in the destination jail cell when it is updated in the original prison cell.

There is a manner you tin can copy and paste a cell containing a formula to a destination cell such that the results of the formula in the original cell e'er display in the destination cell equally they alter. Yous can link to the original cell.

To do this, select and re-create the original jail cell with the formula again and click the cell into which you lot desire to paste the link to the original jail cell. Click the Paste button in the Paste section of the Home tab to display the drop-down menu. Select Paste Link from the menu.

Pasting a link

Y'all will find that, again, the total from the original cell displays in the destination jail cell. Nonetheless, this time, the Formula Bar displays an absolute reference to the original cell containing the formula. Every fourth dimension the results change in the original cell, the total displayed in the destination cell updates as well.

Link pasted

Annotation: If all y'all desire to do is paste the result of the formula into the destination cell, select Paste Values from the Paste drop-downward menu when pasting into the destination cell.

Pasting a link is a handy feature if y'all want to display a total from ane part of a worksheet in a more prominent location with special formatting, possibly for a presentation, and yous desire to keep the jail cell containing the link updated. Enjoy!