Microsoft KB Archive/64337

{|
 * width="100%"|

Excel: Changing from Absolute to Relative or Mixed References

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, versions 2.2 and 3.0

-

SUMMARY
In Microsoft Excel, you can create absolute, relative, or mixed references. You can easily change from one reference type to another without having to retype it by doing any of the following:


 * Choose the Reference Command on the Formula menu
 * Press F4 (Windows and OS/2) or COMMAND+T (Macintosh)
 * Use the the FORMULA.CONVERT macro function (versions 3.0 and later)

For information about how to do this in Microsoft Excel 5.0, please see the following article(s) in the Microsoft Knowledge Base:

Q116028 XL5: VB Macro to Change Between Relative/Absolute References

MORE INFORMATION
The following is a description of different reference types:

   Type       Example      Description --

Absolute   $A$1        Both the row and column are fixed Mixed       A$1        Only the row is fixed Mixed       $A1        Only the column is fixed Relative     A1        Neither the row or column is fixed

The following describes the different types of cell references and the methods for changing them.

To Use the Reference Command or Keyboard Shortcut
To toggle from one reference type to another:


 * 1) Select the cell containing the reference you want to change.
 * 2) In the formula bar, select the reference or references you want to change. Select the entire formula if you want to change all of the references.
 * 3) From the Formula menu, choose Reference.

-or-

Press the F4 key (in Windows and OS/2) or COMMAND+T (on the Macintosh).

Each time you choose Reference from the Formula menu or use the keyboard shortcuts, Microsoft Excel cycles through the different types of reference.

Using FORMULA.CONVERT (Versions 3.0 and 4.0)
FORMULA.CONVERT can be used to change references to absolute, relative or mixed. To specify a reference type that you want a reference to change to, specify a value 1-4 for the To_Ref_Type argument.

The following macro converts all formulas in a selected range to absolute references, specified by the number 1 in the FORMULA.CONVERT function in cell A4. The worksheet must be in A1 notation for this macro to work. If your worksheet is in R1C1 style, change the TRUE parameter in FORMULA.CONVERT to FALSE.

Type the following macro beginning in cell A1 of a new macro sheet:

A1: Formula Conversion

A2: =FOR.CELL(&quot;CurrentCell&quot;,,TRUE)

A3: =IF(ISERROR(SEARCH(&quot;=&quot;,GET.CELL(6,CurrentCell))),GOTO(A5))

A4: =FORMULA(FORMULA.CONVERT(GET.CELL(6,CurrentCell),TRUE,FALSE,1,

CurrentCell),CurrentCell)

A5: =NEXT

A6: =RETURN

To run the macro, select all of the formulas that need to be converted. The selection must be contiguous.