Microsoft Office Tutorials and References

In Depth Information

**Using cell references in formulas**

Using cell references in formulas

A cell reference identifies a cell or group of cells in a workbook. When you include cell

references in a formula, the formula is said to be
linked
to the referenced cells. The resulting

value of the formula depends on the values in the referenced cells and changes

automatically when the values in the referenced cells change.

To see cell referencing at work, select cell A1, and type the formula
=10*2
. Now select cell

A2, and type the formula
=A1
. The value in both cells is
20
. If at any time you change the

value in cell A1, the value in cell A2 changes also. Now select cell A3, and type
=A1+A2
.

Excel returns the value
40
. Cell references are especially helpful when you create complex

formulas.

Entering cell references by clicking

When you start a formula by typing an equal sign into a cell, you activate
Enter mode
.
If

you then click another cell, you don’t select it; instead, the cell’s reference is inserted in

the formula. You can save time and increase accuracy when you enter cell references this

way. For example, to enter references to cells A9 and A10 in a formula in cell B10, do the

following:

Select cell B10, and type an equal sign.

1.

Click cell A9, and type a plus sign.

2.

Click cell A10, and press Enter.

3.

When you click each cell, a marquee surrounds the cell, and Excel inserts a reference to

the cell in cell B10. After you finish entering a formula, be sure to press Enter. If you do not

press Enter but select another cell, Excel assumes you want to include that cell reference in

the formula as well.

The active cell does not have to be visible in the current window for you to enter a value

in that cell. You can scroll through the worksheet without changing the active cell and click

cells in remote areas of your worksheet, in other worksheets, or in other workbooks as you

build a formula. The formula bar displays the contents of the active cell no matter which

area of the worksheet is currently visible.

Note

If you scroll through your worksheet and the active cell is no longer visible, you can

display it by pressing Ctrl+Backspace. You can return to the upper-left corner of the

worksheet by pressing Ctrl+Home.