Excel 2000
Complex Formulas
Absolute References
Normally, 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 the Delete key. 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 on 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 the Enter key 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).
