Microsoft Office Tutorials and References
In Depth Information
Using cell references in formulas
Understanding relative, absolute, and mixed references
Relative references —the type we’ve used so far in the sample formulas—refer to cells by
their position in relation to the cell that contains the formula, such as “the cell two rows
above this cell.” A relative reference to cell A1, for example, looks like this: =A1.
Absolute references refer to cells by their fixed position in the worksheet, such as “the cell
located at the intersection of column A and row 2.” An absolute reference to cell A1 looks
like this: =$A$1.
Mixed references contain a relative reference and an absolute reference, such as “the cell
located in column A and two rows above this cell.” A mixed reference to cell A1 looks like
this: =$A1 or =A$1.
Dollar signs in a cell reference indicate its absoluteness . If the dollar sign precedes only the
letter (A, for example), the column coordinate is absolute and the row is relative. If the
dollar sign precedes only the number (1, for example), the column coordinate is relative and
the row is absolute.
Absolute and mixed references are important when you begin copying formulas from one
location to another in your worksheet. When you copy and paste, relative references adjust
automatically, but absolute references do not. For information about copying cell
references, see “How copying affects cell references” later in this chapter.
While you are entering or editing a formula, press F4 to change reference types quickly. The
following steps show how:
Select cell A1, and type =B1+B2 (but do not press Enter).
2. Press F4 to change the reference nearest the lashing pointer to absolute. The
formula becomes =B1+$B$2.
3. Press F4 again to change the reference to mixed (relative column coordinate and
absolute row coordinate). The formula becomes =B1+B$2.
4. Press F4 again to reverse the mixed reference (absolute column coordinate and
relative row coordinate). The formula becomes =B1+$B2.
Press F4 again to return to the original relative reference.
When you use this technique to change reference types, click the formula bar to activate
it. Then, before pressing F4, click in the cell reference you want to change or drag to select
one or more cell references in the formula to change all the selected references at the same