Excel Edit Defined Name Range: Steps and Common Fixes

How-to basics · Updated November 18, 2026

To edit a defined name’s range in Excel for Microsoft 365 on Windows, go to Formulas > Name Manager, select the name, choose Edit, change the reference in Refers to, and select OK. For example, you can change a name from referring to A2:A20 to A2:A50 without replacing that name in formulas that already use it.

What a defined name does

A defined name gives a cell, range, constant, or formula a readable label. Instead of building a formula around a coordinate such as B2:B100, you might refer to that range as Sales. Names can make formulas easier to understand and can also make it simpler to select a range or maintain a workbook when its layout changes.

Excel has two related ideas that are easy to confuse. A defined name is created and managed through Name Manager or the Define Name command. A table name identifies an Excel table and is managed from the Table Design tab. Editing a defined name does not rename a table, and renaming a table does not edit an unrelated defined name.

A defined name can refer to a cell range, but it can also refer to a formula or a constant. For instance, a name might represent a tax rate or calculate a value. The same Name Manager workflow applies, but be careful not to replace a formula or constant with a range reference unless that change is intended.

Edit a defined name in Name Manager

Use Name Manager when you want to inspect existing names, see what each one refers to, or make a targeted change. In Excel for Microsoft 365 on Windows, follow these steps:

  1. Open the workbook and select Formulas > Name Manager. On Windows, Ctrl+F3 is a shortcut to open Name Manager.
  2. In the list, select the defined name you want to change. Check both its name and its Refers to entry; similar names can point to different ranges.
  3. Select Edit. In the Edit Name dialog, change the expression in Refers to. You can type a reference or select the intended cells on the worksheet.
  4. Select OK to save the change, then select Close in Name Manager. Check a formula or chart that uses the name if the change affects an important report.

For example, assume the worksheet is named Sales and cells A2 through A10 contain the values the name should represent. Enter =Sales!$A$2:$A$10 in Refers to; the name will refer to the ten cells A2:A10 on Sales. The dollar signs make the row and column references absolute, so copying a formula that uses the name will not shift that reference.

If the sheet name contains spaces, enclose it in single quotation marks. Assume the worksheet is named Sales Data and the intended values occupy A2:A10; enter ='Sales Data'!$A$2:$A$10. That reference points to the same ten-cell-sized range on the sheet whose name includes a space.

Choose the right range before changing it

Before editing, decide whether the name should refer to a fixed block of cells or expand as the workbook grows. A fixed reference is suitable when the data has a known end point, such as a closed reporting period. It is easy to audit, but new rows added beneath the range will not automatically be included.

Assume a worksheet named Orders has order amounts in B2:B250, with a header in B1. To make a name cover only those 249 data cells, use =Orders!$B$2:$B$250. The name refers to B2 through B250, not the header or any rows below 250. If you add orders in row 251, you must extend the reference or use a design that expands automatically.

Whole-column references can avoid a fixed last row, but they include every row in that column, including blanks and potentially unrelated content. If formulas using the name perform calculations over a whole column, that broader reference may also be less efficient than limiting the range. Choose a reference that matches the actual data and the formulas that consume it.

For a list that grows regularly, an Excel table is often easier to maintain. Select the data and use Insert > Table (or Home > Format as Table), confirming whether the range has headers. Tables expand when you add rows directly below them, and formulas can use structured references. A defined name can refer to a table column, but a table’s built-in expansion behavior may be more straightforward than repeatedly editing a fixed range.

Understand scope and what the edit changes

Defined names have scope: they can be available throughout the workbook or limited to one worksheet. In Name Manager, inspect the Scope column before changing a name. A worksheet-scoped name can coexist with a workbook-scoped name of the same spelling, so a formula on a particular sheet may use a different name than expected.

Editing Refers to changes the target of the existing name; it does not convert a worksheet-scoped name into a workbook-scoped one. Excel generally does not let you change a name’s scope after it has been created. If the scope is wrong, create a replacement with the intended scope, update formulas as needed, and then remove the old name only after checking its uses. Be cautious: deleting a name can leave formulas with a #NAME? error.

Editing the reference also does not rewrite every formula that uses the name. A formula such as one using Sales continues to use the same name, but the cells represented by Sales change. That is useful when extending a report range, yet it can affect totals, charts, validation lists, or other workbook features that depend on the name. Review the impact before changing a shared name in a complex file.

Names that refer to formulas or dynamic data

The Refers to box is not limited to a simple rectangular range. A defined name can refer to a calculation, a constant, or another name. If the entry begins with an equals sign and contains a calculation, preserve that expression unless you intend to change what the name calculates. Replacing it with a cell address can alter the meaning of formulas that use the name.

For data that grows, a table-based reference can reduce maintenance. Assume an Excel table is named Table1 and its column header is Amount. A defined name can use =Table1[Amount] to refer to the Amount column. The name then follows the table column as table rows are added, rather than requiring a manually updated last row. The table and column names must match those in the workbook.

Dynamic array formulas can also be used in defined names in supported Excel versions, but they are more specialized and can behave differently depending on how the name is consumed. For a workbook shared with people using older Excel versions, a straightforward range or table reference is usually easier to understand and maintain. If a name already contains a more complex expression, copy its current definition somewhere safe before editing it.

Common problems and how to fix them

  • The name is missing from the list. Check the Scope column and the worksheet-level names. A name scoped to another sheet may not be the one you expect. Also confirm that you opened the correct workbook.
  • The reference shows an error or points to the wrong sheet. Confirm the sheet spelling, quotation marks around sheet names with spaces, and the row and column endpoints. Use the range selector in the Edit Name dialog to pick the target cells rather than typing if the address is uncertain.
  • Formulas show unexpected results after editing. The name may be used in more places than the visible formula. Search the workbook for the name and inspect charts, data validation, and other dependent features. Undo immediately with Ctrl+Z if the edit was just made and the results are wrong; otherwise, restore the previous reference in Name Manager.
  • A name appears to be valid but formulas return an error. Check whether the name refers to a range of the expected size and type. A formula designed for a single value may not work as intended when its name points to multiple cells, and a name that was deleted or misspelled can result in #NAME?.
  • The name cannot be edited as expected. Check whether the workbook is protected or opened with restricted permissions. If a shared workbook or organizational policy limits editing, ask for the necessary access or edit a permitted copy.

One common misconception is that changing a name’s range changes the worksheet data itself. It does not move, delete, or rewrite the cells. It changes what Excel resolves that name to, which can then change the outputs of formulas or features that depend on it.

Excel for Mac and Excel for the web

In Excel for Mac, use Formulas > Name Manager to review and edit names; the ribbon path is the clearest option because keyboard shortcuts can differ by keyboard and system settings. The dialog labels may vary slightly by release, but look for the name’s reference field, edit the target, and confirm the change.

Excel for the web has name-management capabilities, but the available commands and editing experience can differ from desktop Excel. If you cannot find the name or make the required change in the browser, open the workbook in the desktop app and use Name Manager. After editing in desktop Excel, save the workbook so the change is available to other users.

Frequently Asked Questions

Can I edit a defined name without changing formulas that use it?
Yes. Keep the name itself and change its Refers to entry. Formulas that use the name will continue to use that name, but they will now refer to the new target.

Can I change a defined name’s scope?
Not by editing the existing name. Create a new name with the desired workbook or worksheet scope, update formulas and other references, and remove the old name only when you have confirmed it is no longer needed.

How do I see what cells a name refers to?
Open Formulas > Name Manager and select the name. Read its Refers to entry; if necessary, use the range selector to navigate to or select the referenced cells.

Why does a formula return #NAME? after I edit a name?
Check that the name still exists, is spelled correctly in the formula, and has not been deleted or replaced by an invalid reference. Also inspect scope: a sheet-level name may not resolve the way a workbook-level name does.

Will a defined range include new rows automatically?
Not if it points to a fixed address such as A2:A50. Extend that reference when the data grows, or consider using an Excel table and a structured reference so the column follows added table rows.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.