Typically, when you copy a formula that uses references, Excel automatically adjusts the reference in the pasted formula to refer to different cells relative to the position of the formula.
Sometimes when you copy a formula, you don't want one of the cell references to change. Creating an absolute reference—a reference to a cell that does not change when copying a formula—solves this problem. An absolute reference always refers to the same cell or range. It is designated in the formula by the dollar sign ($).
For this exercise, delete the original formulas and start again.
To delete a formula:
- Select the cells in which the original formulas exist. The cells are now selected.
- Press Delete. The information in these cells is deleted.
To create an absolute reference:
- Enter the numbers you want to calculate. Then, create a simple formula (=A1+A2).
- Click the cell in which you want to place the formula answer.
- To create an absolute reference, add a dollar sign ($) in front of the column reference and the row reference (=$A$1+A2).
- Press Enter or click the Enter button. The answer displays in the cell.
- Copy and paste the formula to another adjacent cell. The formula now includes an absolute reference (=$A$1+C2).