Excel Maintains Cell and Range Names

Once you create a name for a cell or range, excel automatically maintains the name as you edit or modify the worksheet. The following examples assume that Sheet1 contains a workbook-level name(MySheet) that refers to =Sheet1!$C$3:$E$5 (a nine-cell range).

Inserting a Row or Column

When you insert a row above the named range or insert a column to the left of the named range, Excel changes the range reference to reflect its new address. For example, if you insert a new row 1, MySheet then refers to =Sheet1!$C$4:$E$6.

If you insert a new row or column within the named range, the named range expands to include the new row or column. For example, if you insert a new column to the left of column E, MySheet then refers to =Sheet1!$C$3:$F$5.M

Deleting a Row or Column

When you delete a row above the named range or delete a column to the left of the named range, Excel adjusts the range reference to reflect its new address. For example, if you delete row 1, MySheet refers to =Sheet1!$B$3:$D$5.

If you delete a row or column within the named range, the name range adjusts accordingly. For example, if you delete column D, MySheet then refers to =Sheet1!$C$3:$D$5.

If you delete all rows or all columns that make up a named range, the named range continues to exist, but it contains an error reference. For example, if you delete columns C, D, and E, MySheet then refers to =Sheet1!#REF!. Any formulas that use the name also returns errors.

Cutting and Pasting

When you cut and paste an entire named range, Excel changes the reference accordingly. For example, if you move MySheet to a new location beginning at cell A1, Excel MySheet then refers to =Sheet1!$A$1:$C$3. Cutting and pasting only a part of named range does not effect the name's reference.