How to force Excel to always reference the same cell

Article translations Article translations
Article ID: 214143 - View products that this article applies to.
This article was previously published under Q214143
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article shows you how to use the INDIRECT function to continually reference the same cell in a formula in Microsoft Excel 2000.

How to Use the Indirect Function

To anchor a 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")
To use the INDIRECT worksheet function, follow these steps:
  1. Start Excel, and then open a new workbook.
  2. Create the following worksheet:
    A1: 2
    A2: 3
    A3: 4
    A4: 5
    A5: 6
    A6: 7
    A7: 8
    A8: 9
    A9: 10
    A10: 11
  3. Type the following formula in cell B1:
    =SUM(INDIRECT("$A$1:$A$10"))
    The formula returns 65.
  4. Select cell A7.
  5. On the Insert menu, click Rows.

    The formula in cell B1 now returns 54, and the cell references are unchanged.

Problems with Absolute References

This section explains why absolute references may be less useful than references that you make with the INDIRECT function.

Absolute referencing in Excel references the same cell or cells as long as no cells are added or deleted in the column above the cell or cells that you are referencing. This means that when you insert or delete a row or column so that the cell that 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
the value 10 appears in cell B1. If you then insert a row over cell A1 so that the value 10 is moved to cell A2, the formula in cell B1 changes to the following:
=$A$2

REFERENCES

For more information about the INDIRECT worksheet function, click Microsoft Excel Help on the Help menu, type indirect in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 214143 - Last Review: January 24, 2007 - Revision: 4.2
APPLIES TO
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowtomaster KB214143

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com