Microsoft Office Tutorials and References
In Depth Information
Changing the Type of a Cell Reference
Changing the Type of a Cell Reference
In Tips 69 and 70, I discuss absolute, relative, and mixed cell references. This tip describes an
easy way to change the type of reference used when creating a formula.
You can enter nonrelative references (absolute or mixed) manually by simply typing dollar signs
in the appropriate positions of the cell address. Or, you can use a handy shortcut: the F4 key.
After you enter a cell reference when creating a formula, 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 you want.
When you name a cell or range, Excel uses (by default) an absolute reference for the
name. For example, if you give the name SalesForecast to A1:A12, the Refers To field
in the Define Name dialog box lists the reference as $A$1:$A$12, which 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.
Search JabSto ::

Custom Search