What Tool Is Used To Edit Pre-existing Range Names?

Name manager in excel are used to create a named ranges or edit it or delete, while working with formulas in excel we sometimes used names instead of giving cell references, if we want to add a new reference or edit any reference or delete it we can do it by the name manager, this is available in the Formulas tab under the defined names section.

Excel Name Manager

Named Ranges in excel Name range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window. read more formulas can be used as a substitute for cell references Cell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1. read more . Excel Name Manager is used to create, edit, delete & find other names in the excel workbook.

Excel Name Manager can be found in the "Formulas" tab.

Tip: "Ctrl + F3" is the shortcut used to access it.

Usually, it is used to work with existing names. However, it also allows you to create a new name too.

How to Use Name Manager in Excel?

You can download this Name Manager Excel Template here – Name Manager Excel Template

Please follow the below steps for using the Excel Name Manager.

Go to Formulas tab > Defined Names group, then click the Name Manager. Alternatively, we can just press Ctrl + F3 (the shortcut for Name Manager)
For a new named range, click on the "New" button.
On clicking the "New" button, you will see the below window.

Type in the name that you want to give to your range, as well as the cells it will refer to in the "Refers to" section.

Examples of Name Manager in Excel

Name Manager can be used to create, edit, delete, and filtering excel names. Below, we will see one example of each, along with their explanations.

Example #1 – Creating, Editing & Deleting Named Range in Excel

Let's suppose we want to refer to the cells in the range B2:E2 by the name "near." To do that, follow the below steps.

Go to Formulas tab > Defined Names group, then click the Name Manager. Alternatively, we can just press Ctrl + F3 (the excel shortcut An Excel shortcut is a technique for performing a manual task quickly, and these shortcuts help speed up the work, saving time for the job's secondary tasks. read more for Name Manager)

For a new named range, click on the "New" button.

Then in Name, write "Near" and in Refer to select B2: E2 and click ok.

After this, you can see the name "Near" created when you click on the "Excel Name Manager."

You can see the other options like edit & delete. Let's suppose you want to edit the cell reference. Then just select the relevant named range (here "near"), click on "Edit," and change the configuration.

Similarly, for deleting, select the relevant named range & click on "Delete."

In case you want to delete multiple named ranges at once, all you need is to select the relevant ones by pressing the "Ctrl" button. All the relevant ones will be selected, and then you just need to click on "Delete." To delete all the names, select the first one, press the Shift button, and then click on the last "named range." In this way, all will be selected, then just click "Delete."

Example #2 – Create an Excel Name for a Constant

Not only named ranges, but excel also allows us to define a name without any cell reference. This can be used to create a named constant.

Suppose you want to use a conversion factor in your calculations. Instead of referring every time to that value, we can assign that value to a name and use that name in our formulas.

For example: 1 km = 0.621371 mile

Let's first create the named range, which will then be used in a formula. In the Formula tab, click on Name Manager.

Once you click on Name Manager, a window will open in that click on New.

In "Name Box," write "Km_In_Miles," and in "Refer to Box," specify the value as 0.621371,

After this, you can see the name "Km_In_Miles" created when you click on the "Excel Name Manager."

Whenever you want to use a name in a formula, type, and you will see it in the list of suggestions to select.

So the answer will be,

Then drag the plus sign to get the answer for all.

Example #3 – Defining Name for a formula

Similar to the above, we can give a name to an excel formula.

Let's suppose column A contains the names of people who participated in the run, and I want to know the number of people who participated. Let's just create a named formula for it.

Create a named Range "Run_Number" following the above-given steps. So, in the New Name window, write the following Attributes, and click Ok.

Then use this named range as follows; it will give you the correct number of participants.

Note: If the cells being referred to are in the current sheet, then we don't need to mention the Sheet number in the excel formula. However, add the sheet's name followed by the exclamation point before the cell/range reference if you are referring to cells on a different worksheet.

Rules for Name Manager in Excel

Under 255 characters
Cannot contain spaces and most punctuation characters
Must begin with a letter, underscore ("_"), or backslash ("")
Can't have names like cell references. For example, B1 is not a valid name
Names are case-insensitive
A single letter name can be used to name a range. However, they can't be "c," "C," "r," or "R."

Scope Precedence

The worksheet level takes precedence over the workbook level.

The scope of excel's name can be either at the worksheet level or at the workbook level.

Worksheet level name is recognized only within that worksheet. In order to use it in another worksheet, we will need to prefix the worksheet name followed by an exclamation to the named range.

The workbook level name is recognized in any of the worksheets inside a workbook. To use another workbook's name range in another workbook, we need to prefix the workbook name, followed by an exclamation mark to the named range.

Example #4 – Filters in Excel Name Manager

Excel name Manager also has the filter functionality to filter out the relevantly named ranges. Please see the screenshot below.

Here, you can see the relevant criteria for filtering the relevantly named ranges. Select the one you want to restrict to and then do whatever you want.

Things to Remember

Open Excel Manager: "Ctrl + F3"
To get a list of all excel named ranges, use F3
Named Ranges are case-insensitive