XL: How to Force MS Excel to Always Reference the Same Cell

 Last reviewed: February 5, 1998
Article ID: Q51918

The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 5.0
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

To continually reference the same cell in a formula in Microsoft Excel, use the INDIRECT function.

MORE INFORMATION

Absolute referencing references the same cell as long as no cells are added or deleted in the column above the cell you are referencing. This means that when you insert or delete a column so that the cell you are referencing is moved, the formula references the new cell location.

For example, if you type the value 10 in cell A1 and refer to it in cell B1 with the formula "=$A$1" (without the quotation marks), the value 10 appears in cell B1. If you then insert a cell over cell A1 so that the value 10 is moved to cell A2, the formula in cell B1 is "=$A$2."

To anchor the same reference while you insert or delete rows, columns, or cells, use the INDIRECT worksheet function. For example, if you want Excel to always reference cell A1, use the following formula: 

   =INDIRECT("$A$1")
Below is an example of a formula that would use the INDIRECT function to always sum the first 10 rows of column A: 

   =SUM(INDIRECT("$A$1:$A$10"))

REFERENCES

Microsoft Excel 97

For more information about the INDIRECT function click the Index tab in Microsoft Excel Help, type the following text 

   indirect
and then double-click the selected text to go to the "Indirect function" topic.

Earlier Versions

"Online Help," version 5.0

"Function Reference," version 4.0, page 238

"Function Reference," version 3.0, page 129

"Functions and Macros," version 2.x, page 61

Additional query words: sort lock
Keywords : xlformula kbusage
Version : MACINTOSH:3.0,4.0,5.0,98; WINDOWS:3.0,4.0,5.0,7.0,97
Platform : MACINTOSH WINDOWS

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: February 5, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.