Excel Guides

Changing the Reference in a Named Range in Excel

When you change the reference in a named range, you are essentially changing the cells that are included in the named range. This can be useful if you want to expand or contract the named range without having to delete it and create a new one. To change the reference in a named range, follow these steps:

  1. Select the cell(s) that you want to include in the named range.
  2. Click the Formulas tab.
  3. In the Defined Names group, click Name Manager.
  4. Click the named range that you want to change.
  5. Click Edit.
  6. In the Refers to box, do one of the following:

To expand the named range, type the reference of the first cell in the expanded range, followed by a colon (:), and then type the reference of the last cell in the expanded range. For example, if you want to expand a named range that currently refers to cells A1:A5 to include cells A6:A10, you would type A1:A10. If you want to expand a named range by adding additional rows or columns, you can simply extend your selection to include those rows or columns.

To contract the named range, type a new reference that includes only those cells that you want to keep in the named range. For example, if you want to contract a named range that currently refers to cells A1:A10 so that it only refers to cells A1:A5, you would type A1:A5. If you want to contract a named range by removing rows or columns from it, you can simply select those rows or columns and delete them.

To change both the starting and ending references for a named range at once, click Collapse Dialog Box in the Refers to box, select both references in your worksheet, and then click Expand Dialog Box.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.