When you copy a cell containing a formula, you will notice that the cell references in the formula also move the corresponding number of cells across and down. This type of cell reference is called a relative reference.
When we copy (Ctrl + C) the formula in the image above and paste (Ctrl + V) it into another cell, you will notice that the cell references change from column B to column D, so the total is different.
If you want Excel not to change cell references when copying cells, you need to use absolute references. To create an absolute reference, insert a dollar sign ($) in front of both sides of the cell reference in the formula you want to freeze, as shown in the following image.
There are three different types of links: relative, absolute, and mixed. Here are some examples:
- Relative reference: A1 tells Excel to change the cell references to the appropriate columns and rows.
- Mixed reference: $ A1 tells Excel that you always want to refer to column A.
- Mixed reference: B $ 1 tells Excel that you always want to refer to row 1.
- Absolute reference: $ B $ 1 tells Excel that you always want to reference cell B1.
There is a shorthand method for entering dollar signs when selecting or entering cell references in a formula. When you enter a formula and end a cell reference, press F4 to switch between the 4 combinations of reference types. Let’s say you started typing a formula and typed = 100 * B1.
- Press F4 and your formula will change to = 100 * $ B $ 1 (always refer to cell B1)
- Press F4 again and your formula will change to = 100 * B $ 1 (always refer to line 1)
- Press F4 again and your formula will change to = 100 * $ B1 (always refer to column B)
- Press again F4 and your formula will revert back to the original relative reference = 100 * B1 (always change the reference to the corresponding columns and rows)
You can pause entering each cell reference in the formula and press F4 until you get the correct reference type for the current cell reference.
To copy a formula entered using absolute references and keep cell references, select the cell containing the formula and copy (Ctrl + C) and click the target cell where you want to paste the formula.
Make sure the Home tab is active on the Ribbon. Click the arrow on the Insert button in the Insert section of the Home tab. Select “Formulas” from the drop-down menu.
You will notice that the total appears in the target cell from the source cell, and the formula that appears in the formula bar for the target cell contains the same absolute references as the original formula in the source cell.
NOTE. Copying and pasting a formula in the target cell does not mean that the formula will be updated in the target cell when it is updated in the source cell.
There is a way to copy and paste the cell containing the formula into the target cell so that the formula results in the source cell always appear in the target cell as they change. You can provide a reference to the source cell.
To do this, select and copy the original cell with the formula again and click the cell where you want to paste the reference to the original cell. Click the Insert button in the Insert section of the Home tab to display the drop-down menu. From the menu, select “Insert Link”.
You will notice that the sum of the source cell appears again in the target cell. However, this time, the Formula Bar displays an absolute reference to the source cell that contains the formula. Each time the results change in the source cell, the total displayed in the target cell is also updated.
NOTE. If all you have to do is paste the result of the formula into the target cell, select Paste Values ??from the Paste drop-down menu when pasting into the target cell.
Inserting a link is a handy feature when you want to display the grand total from one part of the sheet in a more prominent place with special formatting, perhaps for a presentation, and you want the cell containing the link to be updated. Enjoy!