Today we are going to discuss how to edit named range in Excel. Named range is a very interesting feature in Excel. In this article, we will discuss how to define a named range first. Then we’ll explain how to edit the named range in Excel. So, without any delay, let’s start the discussion.
Download Practice Workbook
You can download the practice workbook from here.
What Is Named Range in Excel?
Named range refers to naming several cells in Excel instead of calling them through their range. It can be a whole column or whole row or specific cells. After defining the named range, we can perform any operation of those cells only by calling the name of the named range. For any kind of reference, we can call them by their name.
Additionally, a named range does not change when a formula is copied to other cells. It provides an alternative to using absolute cell references in formulas.
How to Define Named Range in Excel
There are multiple ways to define a named range in Excel. We will show only one way to define a named range for our further discussion. Let’s say, we have a dataset consisting of sales Date, names of some Sales Person, and Sales of the first week of November.
Let’s follow the steps below to define a named range in Excel.
- Firstly, select the cells we want to make a named range.
- Here, we have selected a range from D5 to D8.
- Then, go to the Formulas tab.
- Afterward, from the Define Name group of commands, select the drop-down Define Name.
- After that, we will get a pop-up of New Name window.
- Later on, set a name in the Name section.
- Also, we can see our selected range in the Refers to section.
- Further, we can change the scope where our named range will be applicable from the Scope section.
- In addition, we can change the cell reference for the named range in Refers to section if not selected earlier.
- Consecutively, press OK.
- Finally, we have defined our Named Range.
- Again, select the range containing the sales data in Column D for checking.
- We will see the name marked in the Name Box like in the following image.
- How to Use Named Range in Excel VBA (2 Ways)
- Name a Range in Excel (5 Easy Tricks)
- How to Delete Named Range in Excel (3 Methods)
- 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
- How to Edit Cell with Single Click in Excel (3 Easy Methods)
Step-by-Step Procedures to Edit Named Range in Excel
In the last section, we’ve discussed the named range and how to define it. Now we are going to explain how to edit the named range in Excel. Editing a named range can be required sometimes because we may need to change the name or the range as our data expands.
We can edit the named range with the Name Manager command in the Formulas tab. The stepwise procedure is described below.
STEP 1: Utilize Formulas Tab to Open Name Manager
- Firstly, go to the Formulas tab.
- Now, select Name Manager from the Define Name group of commands.
STEP 2: Apply Name Manager to Edit Named Range
- When we click Name Manager, we will get a pop-up.
- The Name Manager dialog box includes the options like New, Edit or Delete options like in the following image.
- Also, our selected range is marked here.
- Let’s say, we want to edit the named range called Date.
- So, we have to select Date from the Name section and click on Edit option.
STEP 3: Insert New Name for Named Range
- When we click on the Edit option, a new dialog box called Edit Name will appear.
- Now, we can change the Named Range from the Name section.
- Also, we can change the range from Refers to section.
- After that, click on OK.
STEP 4: Check New Name and Close Name Manager Window
- Consecutively, the Name Manager window will show the preview.
- Later on, press Close on that window.
- Finally, we will get the result.
- Here, we can see that the named range changed from Date to Date_N.
Rules and Restrictions for Defining Named Range in Excel
- We can’t add space in the given name.
- The first character of the name can be a letter, underscore or backslash.
- The maximum name length can be up to 255 characters.
- Cell reference can’t be defined as a range name like A5 can’t be a name for named range.
- Upper case and lower case are undistinguishable in Excel. So, names like sales and SALES mean the same.
Here, we discussed named range, how to define named range and how to edit named range. We can define named range in multiple ways, but we can edit named range in Excel only by using Name Manager. Here, we explained all the steps in detail so that the users can create a named range rather than only edit it. If you have any queries or suggestions, please let us know in the comment section. Visit our ExcelDemy Website for more articles regarding Excel.
- How to Enable Editing in Excel Protected View (5 Methods)
- Dynamic Named in Range Excel (Both One and Two Dimensional)
- How to Paste Range Names in Excel (7 Ways)
- [Fixed!] Name Manager Delete Option Greyed out in Excel (3 Reasons)
- How to Set Value to a Named Range with Excel VBA (3 Methods)
- [Solved!] Names Not in Name Manager in Excel (2 Solutions)
- How to Change Scope of Named Range in Excel (3 Methods)