Microsoft Office Tutorials and References

In Depth Information

**Making a cell reference unchangeable**

Making a cell reference unchangeable

Another use for the INDIRECT function is to create a reference to a cell that never changes. For

example, consider this formula, which sums the values in the first 12 rows of column A:

=SUM(A1:A12)

If you insert a new row 1, Excel changes the formula to

=SUM(A2:A13)

In other words, the formula adjusts so that it continues to refer to the original data (and it no longer

sums the first 12 rows of column A). To prevent Excel from changing the cell references, use the

INDIRECT function:

=SUM(INDIRECT(“A1:A12”))

This formula
always
returns the sum of the first 12 rows in column A.