Complex Formulas

In Excel complex formulas can include several operations. Use complex formulas in Excel to calculate more extensive data.

Absolute references

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.

Relative Reference

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).
Absolute Reference