Adjust a Named Range in Excel

To adjust a named range in Microsoft Excel for Microsoft 365 on Windows, go to Formulas > Name Manager, select the name, choose Edit, change the Refers to box, and select OK. For example, to include more rows, change =Sales!$B$2:$B$100 to =Sales!$B$2:$B$150; formulas that use the name will then refer to the expanded range.

What a named range does

A named range gives a cell, group of cells, or formula a recognizable name. Instead of referring to =SUM(B2:B100), a workbook might use =SUM(MonthlySales). The name can make formulas easier to understand and can give you a quick way to navigate to the cells it represents. A named range is not a separate copy of the data: it points to a reference or, in some cases, a formula or constant.

People adjust named ranges when a list grows, a report needs a different set of rows, or a name points to the wrong worksheet or cells. Editing the range does not normally require changing every formula that uses the name. That is one of the main benefits of names: update the definition once, and dependent formulas can use the revised reference.

Steps to edit the range reference

Open the workbook in the Excel desktop app and select the Formulas tab.
In the Defined Names group, select Name Manager. On Windows, Ctrl+F3 also opens Name Manager.
Select the name you want to change. Check the Name and Scope columns so you have the correct entry, particularly if names are repeated on different sheets.
Select Edit. In the Refers to box, replace or revise the cell reference. You can type a new reference or use the range selector button at the right of the box to select cells on the worksheet.
Select OK to save the edited definition, then close Name Manager.

For a concrete example, suppose the worksheet is named Sales and the sales values occupy B2:B100. If new records extend through row 150, set Refers to to =Sales!$B$2:$B$150. The dollar signs make the row and column references absolute, so the reference does not shift if a formula containing it is copied. If the sheet name contains a space, enclose it in single quotation marks; for example, with values on a worksheet named Quarter 1, use ='Quarter 1'!$B$2:$B$50.

Choose the right cells for the reference

Before changing a reference, decide exactly what the name should include. If a named range is intended to cover data rows only, do not accidentally include a heading, a total row, or unrelated notes. If a formula such as SUM uses a range that includes a total cell containing that same formula, the result may become circular. Confirm the first and last cells against the worksheet rather than estimating the row numbers.

When you select a name in Name Manager, inspect its Refers to entry before editing. The range might be on another sheet, might contain more than one area, or might refer to a formula instead of a simple block of cells. If the reference is multi-area or formula-based, replacing it with a plain rectangular range changes what the name means. Keep a note of the original reference if you are unsure whether the change is reversible.

Useful checks before saving include:

Does the reference start at the intended first data cell and end at the final intended cell?
Is the worksheet name correct, including spaces and spelling?
Should the range include blank rows for future entries, or only cells currently containing data?
Does the name serve formulas, charts, data validation, or other workbook features that rely on its existing extent?

Fixed ranges and ranges that grow

A fixed reference, such as =Sales!$B$2:$B$150, stays tied to those cells until you edit it. This is often the clearest choice for a report with a known size. Its limitation is that future rows beyond row 150 are not included automatically. If someone adds records below the endpoint, formulas or charts using the name may omit them until the range is extended.

If the data is a regularly expanding list, consider converting it to an Excel Table instead of repeatedly enlarging a named range. Select a cell in the data and use Insert > Table, making sure the header option matches the layout. Tables expand as rows are added, and their structured references can make formulas and charts easier to maintain. A table is not the same thing as a named range, however; it is a distinct Excel feature with table names and column references.

Another option is a dynamic named formula that calculates its reference as data changes. Such definitions can be useful in workbooks designed around a particular layout, but they are more complex to audit and maintain than a table or a straightforward fixed range. If a workbook already uses a dynamic definition, avoid replacing it with a fixed reference unless you intend to stop the range from adjusting automatically. Check the Refers to box carefully: a name may begin with an equals sign and contain a formula rather than just a sheet-and-cell address.

Names, scope, and duplicate entries

Excel names can have workbook scope or worksheet scope. A workbook-scoped name is available throughout the workbook. A sheet-scoped name is intended for use on its particular worksheet. Name Manager shows the scope for each entry, which matters when two entries have the same spelling. A worksheet-scoped name can take precedence over a workbook-scoped name with the same name when a formula is used on that sheet.

Scope is established when the name is created; you cannot change a name’s scope by editing its Refers to reference. If you need a different scope, you generally must create a new name with the desired scope and remove the old one after checking its uses. Deleting or replacing a name carelessly can break formulas, so first check formulas, charts, and validation rules that may depend on it. When names are duplicated, confirm both the name and scope before editing rather than selecting the first similar entry.

Names also have restrictions: they cannot contain spaces, and they cannot be written in a way Excel interprets as a cell reference. For example, use an underscore or another permitted character in place of a space. Excel’s Name Manager is the best place to review the exact spelling and scope of an existing name before changing it.

What happens to formulas and other features

When a formula uses a defined name, changing the name’s reference changes the cells that the formula evaluates. For example, if a SUM formula uses a name for a column of sales figures, extending that name can include additional figures without editing the SUM formula itself. This is different from changing a formula’s text: the formula still contains the same name, but the name now points somewhere else.

Named ranges may also be used outside ordinary worksheet formulas. A chart series, a data-validation list, or a formula in another sheet may depend on a name. After editing, check the workbook areas that use it, especially if the new reference has a different number of rows or columns. A chart may display additional points, while a validation drop-down may show extra choices; those may be intended results, but they are worth verifying.

To test the change, select the name in Name Manager and inspect the updated Refers to value. Then review at least one dependent formula or feature and compare its output with the expected result. For a high-impact workbook, save a separate copy before making broad changes. That gives you a way to restore the earlier definition if the revised range affects results unexpectedly.

Common problems and how to fix them

The name does not appear to change. Confirm that you edited the correct name and selected OK in the Edit Name dialog. Reopen Name Manager and check the Refers to box. A duplicate name with a different scope may be the one a formula actually uses.
The reference displays an error. Check for a misspelled worksheet name, a deleted sheet, or an invalid cell address. For sheet names containing spaces, use single quotation marks around the name in the reference.
New records are still excluded. The updated endpoint may not reach the new rows, or the name may use a fixed reference that does not expand automatically. Extend the reference or consider using a Table for an expanding data list.
A formula returns an unexpected result. Confirm that the revised range does not include headings, totals, or unrelated cells. Also check whether the name was a formula or multi-area reference before editing.
You cannot change the scope. Scope is not an editable property in the Edit Name dialog. Create a new name with the required scope, update dependent uses as needed, and remove the old name only after verifying it is no longer needed.

Excel desktop, Mac, and web differences

The steps above describe Excel for Microsoft 365 on Windows. In Excel for Mac, use the Formulas tab and Name Manager to inspect and edit defined names; ribbon placement and keyboard shortcuts can differ by Mac version and keyboard. The menu route is more dependable than assuming a Windows shortcut works on a Mac.

Excel for the web has a more limited interface than the desktop app, and available name-management controls can vary. If you cannot find an option to edit a particular definition, open the workbook in the desktop app and use Formulas > Name Manager. This is especially sensible for complex definitions, duplicate scopes, or names used by charts and other workbook features.

Frequently Asked Questions

How do I change the cells in a named range?

Open Formulas > Name Manager, select the name, choose Edit, and update the Refers to box. Select OK to save. The name then points to the revised cells, and formulas using that name generally use the new reference.

Can I adjust a named range by dragging its border?

The reliable method is to edit the Refers to box in Name Manager or select the desired cells with its range selector. Selecting or dragging cells on the worksheet alone does not necessarily redefine an existing name, so verify the saved reference in Name Manager.

Can I change a named range’s scope?

No. A name’s workbook or worksheet scope is set when the name is created and cannot be changed in the Edit Name dialog. Create a new name with the intended scope, check dependent formulas and features, and then remove the old name only if it is no longer needed.

Will changing a named range update formulas that use it?

Usually, yes: formulas that refer to the name continue using that name, but the name now points to the revised reference. Check dependent charts, validation lists, and formulas after the change, because expanding or shifting the range can change their results.

How do I make a named range expand when I add rows?

For a growing list, an Excel Table is often easier to maintain than repeatedly editing a fixed range. Tables expand as rows are added and offer structured references. A dynamic named formula is another possibility, but it requires more careful setup and maintenance.

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.