Microsoft Office Tutorials and References

In Depth Information

**Understanding Cell Referencing**

Did You Know?

You can use Paste Special to copy only formulas.
Select the cells con-

taining the formulas you want to copy, click the Copy button on the

Home tab, click where you want to paste the data, click the Paste but-

ton arrow, click Paste Special, click the Formulas button, and then click

OK.

You can use keyboard commands to recalculate formulas.
Press F9

to recalculate formulas that have changed since the last calculation

in all open workbooks. Press Shift+F9 to recalculate formulas in the

active workbook. Press Ctrl+Alt+F9 to recalculate all formulas in all

open workbooks, regardless of whether formulas have changed. Press

Ctrl+Shift+Alt+F9 to recheck dependent formulas in all open work-

books, regardless of whether formulas have changed.

Understanding Cell Referencing

Each cell, the intersection of a column and row on a worksheet, has a unique address, or

cell reference
, based on its column letter and row number. For example, the cell reference

for the intersection of column D and row 4 is D4.

Cell References in Formulas

The simplest formula refers to a cell. If you want one cell to contain the same value as

another cell, type an equal sign followed by the cell reference, such as =D4. The cell that

contains the formula is known as a
dependent cell
because its value depends on the value

in another cell. Whenever the cell that the formula refers to changes, the cell that contains

the formula also changes.

Depending on your task, you can use
relative cell references
, which are references to

cells relative to the position of the formula,
absolute cell references
, which are cell refer-

ences that always refer to cells in a specific location, or
mixed cell references
, which use

a combination of relative and absolute column and row references. If you use macros, the

R1C1 cell references make it easy to compute row and column positions.