How to change from absolute to relative or mixed references in a formula in Excel (820204)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows

SUMMARY

In Excel, you can create absolute, relative, or mixed references in a formula. You can easily change from one reference type to another without having to retype it by pressing F4.

MORE INFORMATION

The following is a description of different reference types.

TypeExampleDescription
Absolute$A$1Both the row and column are fixed
MixedA$1Only the row is fixed
Mixed$A1Only the column is fixed
RelativeA1Neither the row or column is fixed

To toggle from one reference type to another:
  1. Select the cell that contains the reference you want to change.
  2. In the formula bar, select the reference or references that you want to change.

    Select the whole formula if you want to change all the references.
  3. Press F4.
Each time you press F4, Excel cycles through the different types of reference in the order of the table. For example, if you type a relative reference and then press F4, the reference changes to absolute. When you press F4 again, the reference changes to mixed with the row fixed.

REFERENCES

For more information about Referencing, click Microsoft Excel Help on the Help menu, type References in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:9/22/2004
Keywords:kbformula kbinfo KB820204
©2004 Microsoft Corporation. All rights reserved.