Office 2007 Bible

Using Cell References in Formulas

Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed values. For example, if your formula refers to cell A1 and you change the value contained in A1, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.

Using relative, absolute, and mixed references

When you use a cell (or range) reference in a formula, you can use three types of references:

• Relative: The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.

• Absolute: The row and column references do not change when you copy the formula because the reference is to an actual cell address.

• Mixed: Either the row or column reference is relative, and the other is absolute.

An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5). Excel also allows mixed references in which only one of the address parts is absolute (for example, $A4 or A$4).

By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell.

Figure 15-7 shows a simple worksheet. The formula in cell ...