Excel: Changing from Absolute to Relative or Mixed References

ID: Q64337

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:

Select the cell containing the reference you want to change.

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.

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("CurrentCell",,TRUE)
A3: =IF(ISERROR(SEARCH("=",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.

REFERENCES

"User's Guide 1," version 4.0, page 142
"User's Guide," version 3.0, page 124
"Function Reference," version 4.0, page 170

Additional query words: 2.2 2.20 4.00a

Keywords :
Version :
Platform :
Issue type :