To edit a named range in Excel means editing a defined name or its corresponding cell references or comments.
In this Excel tutorial, I will show you how to edit named ranges in Excel.
Look at the following image. In our sample Excel workbook, we have some named ranges.
You can find them in the Name Box at the upper left corner of the Excel spreadsheet window.
You can see that, there is a spelling mistake in a name (Brnd). So we wish to edit this wrong name. We also want to edit the cell ranges these names refer to.
In this article, the following topics are covered: How to
– Change the names of the named ranges.
– Edit range references of the named ranges.
– Edit comments (if any) associated with each named range.
– Delete named ranges.
What Is a Named Range in Excel?
A named range is a range of cells or an array that is assigned with a user-defined name. A named range can be used in a function or a formula instead of selecting the corresponding range of cells manually.
How to Edit Name of a Named Range?
Here, we shall learn how to edit the name of a named range in Excel using the Edit Name dialog box and editing the Name: field in the box.
Say, we want to change the Named Range Brnd to Brand. To do this, follow the steps below.
Step 1: Go to the Formulas tab ⇒ Name Manager window.
You can also go to Name Manager directly by pressing CTRL+F3.
Step 2: Select the named range Brnd from the list you see ⇒ Click on the Edit button.
Step 3: Now, edit the name Brnd and modify this to Brand ⇒ Press OK ⇒ Click Close on the Name Manager window.
Important Notes on Naming Ranges:
While naming ranges and editing their names, you have to keep the following things in mind.
- A space character is not supported in the names of named ranges.
- 1st character of a name must be a letter/underscore (_)/backslash (/).
- Other characters may be any letter, number, period, or underscore.
- Names are not case-sensitive. Names Brand and brand are the same.
- A name can contain 255 characters at maximum
- You cannot assign a name that is similar to a cell reference of Excel. For example, no names like A1, AB2, XY5 is supported.
- You cannot use names like C, V, Z, etc. that match with keyboard shortcuts.
How to Edit the Range of a Named Range?
Here, we shall learn how to edit the range of a named range in Excel using the same Edit Name dialog box and editing the Refers to: field in the box.
Say, you have added some new brand names in Column B (Brand column). But the previously named range is B6:B14.
This old range does not include the currently added brand names in B15:B17.
To update the range of the named range Brand, follow the following steps.
Step 1: Go to Formulas ⇒ Name Manager ⇒ Select Brand ⇒ Click on Edit.
The Edit Name window appears.
Step 2: Click on the arrow of the Refers to: box ⇒ Select new range ⇒ Again click on the arrow button ⇒ Press OK & Close.
How to Edit Comment in a Named Range?
Here, we shall learn how to edit the comment of a named range in Excel by editing the Comment: field of the Edit Name dialog box.
Say, the named range – Models contains a comment with a spelling mistake. Hence, you have to edit this comment.
In the Name Manager window, we can see the associated comment for Models named range.
Step 1: The Edit Name window appears as we click on the Edit button.
Step 2: Move the cursor into the Comment box ⇒ Edit the comment ⇒ Click on OK.
Finally, click on the Close button of the Name Manager window.
Is it Possible to Edit the Scope of Excel Named Ranges Once They Are Created?
The answer is, no. It is not possible to edit the Scope of Excel Named Ranges after creating them is completed.
Look at the image below. The Scope field is greyed out in the Edit Name window.
To change the Scope of a named range you need to delete the named range and then create the named range with the correct Scope.
How to Delete Named Range in Excel?
You can delete named ranges using the Delete command (the right-most button) of the Name Manager dialog box.
To delete a named range, follow the steps below.
Step 1: Go to the Name Manager window as shown before ⇒ Select the named range you want to delete from the list (We selected Units_Sold) ⇒ Click on the Delete button.
Step 2: A warning window will appear. Click OK.
Read More: How to Delete All Named Ranges in Excel
Things to Keep in Mind while Editing Named Range
- Editing named ranges does not work in cell edit mode.
- We can define a named range from the Name Box. But the the defined names can not be edited in the Name Box.
- The default Scope of any named range is the workbook.
- You can create multiple named ranges with the same name with different scopes based on the workbook and worksheet.
- When you edit a named range (edit name or range), the changes will be reflected in the formulas where the named ranges are being used.
I hope you have found this brief tutorial useful. We have shown how to edit names, ranges, and comments of Excel named ranges in this article. We also have shown how to delete the named ranges. Please place your feedback and share your thoughts in the comment box below.
