After 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 (MyRange) that refers to the following nine-cell range:
=Sheet1!$C$3:$E$5
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, MyRange 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, MyRange then refers to =Sheet1!$C$3:$F$5.
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, MyRange 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, MyRange 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, MyRange then refers to =Sheet1!#REF!. Any formulas that use the name also return errors.
When you cut and paste an entire named range, Excel changes the reference accordingly. For example, if you move MyRange to a new location beginning at cell A1, MyRange then refers to =Sheet1!$A$1:$C$3. Cutting and pasting only a part of a named range does not affect the name's reference.