Microsoft Office Tutorials and References
In Depth Information
Changing the types of your references
($2). As a result, this formula can be copied down and across, and the calculations will be
correct. For example, the formula in cell F7 is:
=$B7*F$2
If C3 used either absolute or relative references, copying the formula would produce
incorrect results.
When you cut and paste a formula (move it to another location), the cell references in the formula aren’t adjusted.
Again, this is usually what you want to happen. When you move a formula, you generally want it to continue to refer to
the original cells.
Changing the types of your references
You can enter nonrelative references (that is, absolute or mixed) manually by inserting
dollar signs in the appropriate positions of the cell address. Or you can use a handy shortcut:
the F4 key. When you’ve entered a cell reference (by typing it or by pointing), you can press
F4 repeatedly to have Excel cycle through all four reference types.
For example, if you enter =A1 to start a formula, pressing F4 converts the cell reference to
=$A$1 . Pressing F4 again converts it to =A$1 . Pressing it again displays =$A1 . Pressing it
one more time returns to the original =A1 . Keep pressing F4 until Excel displays the type of
reference that you want.
When you name a cell or range, Excel (by default) uses an absolute reference for the name. For example, if you
give the name SalesForecast to B1:B12, the Refers To box in the New Name dialog box lists the reference as
$B$1:$B$12 . This is almost always what you want. If you copy a cell that has a named reference in its formula, the
copied formula contains a reference to the original name.
Referencing cells outside the worksheet
Formulas can also refer to cells in other worksheets — and the worksheets don’t even have
to be in the same workbook. Excel uses a special type of notation to handle these types of
references.
Referencing cells in other worksheets
To use a reference to a cell in another worksheet in the same workbook, use this format:
SheetName!CellAddress
Search JabSto ::




Custom Search