Excel: Changing from Absolute to Relative or Mixed References

Last reviewed: May 1, 1995
Article 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:

   ARTICLE-ID: XL5: VB Macro to Change Between Relative/Absolute References
   TITLE     : Q116028

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

KBCategory: kbusage
KBSubcategory:
Additional reference words: 2.2 2.20 3.00 4.00 4.00a

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 1, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.