How to Edit Named Range in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Existing Named Ranges

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.

Select Name Manager from the Formulas tab

Step 2: Select the named range Brnd from the list you see ⇒ Click on the Edit button.

Select the Edit option from the Name Manager

Step 3: Now, edit the name Brnd and modify this to Brand ⇒ Press OK ⇒ Click Close on the Name Manager window.

Editing name of a named range

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.

Not included names in the named range

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 FormulasName 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.

Editing range of a named range


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.

Named Range containing comment

Step 1: The Edit Name window appears as we click on the Edit button.

Editing comment showing for selected Named Range

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.

Edit comment of named range


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.

The Scope feature is greyed out

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.

Delete a Named Range from Name Manager

Step 2: A warning window will appear. Click OK.

Warning to delete a Named Range

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo