Excel Named Range

Excel Named Range (Table of Contents)

• Named Range in Excel
• Define Names from a Selected Range
• How to Create Named Range in Excel?

Named Range in Excel

Named Range just makes the process of working with formulas very interesting, especially to keep track of things. It is just very convenient to assign a name to a range. Now if there is any change in that range, it becomes infinitely easier to update the range through the Name Manager. It totally allows us to bypass the process of manually updating every formula. Also, in cases of nested formulas, where one formula has to be used in another formula or maybe just used in a different location, all that needs to be done is to refer the formula by its name.

• The importance of named ranges is that one can use any names in the formula without worrying about the cell addresses/references. Any name can be assigned to a range.
• Any named constant or any data can be given a named range and these names can be used instead of the actual formula. In this way, it becomes easier to understand the formulas. So, what this does is that it gives a range of cells in Excel a human understandable name.
• By making use of named ranges one can make the usage of formulas very easy in Excel. One can give a name to a range in a formula or function, a constant or a table. After starting to use names in a sheet, one can easily comprehend these names.

Define Names from a Selected Range

Firstly, one has to select the range that you want to give a name. Thereafter navigate to Formulas and then select Create from Selection.

From “Create Names from Selection” box select either Bottom Row, Top Row, Left Column or Right Column and then click on OK.

The cells are named based on labels in the designated range

• First, use names in formulas and then focus on a cell and then enter a formula.
• The cursor has to be placed at the location where we want to use the named range in a formula.
• The name can be selected from the list that opens up after typing the first letter of the name.
• Alternatively, navigate to Formulas and then Use in Formula followed by selecting the name that you intend to use.
• Press Enter.

It is also possible to update the named ranges from the Name Manager. The shortcut to update the named ranges from the Name Manager is Ctrl+F3. We just need to select the name that we wish to change and then proceed to change the reference directly.

How to Create Named Ranges in Excel?

Let us understand the concept of Named Range through some examples.

Named Ranges in Excel – Example #1

Create a Named Range by using the Define Name option –

• First, focus on the cells by selecting the range of cells.

• Now, navigate to the Formulas tab. Next, go to the Defined Name group. Click to select Define Name.

• Now once the New Name dialog box opens, mention three things -Name, Scope, Comment.

• Now in the “Name:” category, provide a name for the range.

• Now in the “Scope:” category, from the dropdown, the name scope has to be set (by default it is Workbook)

• Now moving on to the ‘Refers to:’ section, verify the reference which is available and modify it if required. After that click on OK in order to save the changes that you have made and to close the dialog box.

Named Ranges in Excel – Example #2

Create a Named Range by utilizing the Name Manager –

• Navigate to the Formulas tab and then proceed towards Define Name Group and then click on Name Manager.

• Alternatively, you can also press Ctrl+F3 which is the shortcut for the same.

• Now, on the top, towards the left corner of the Name Manager, we have the ‘New’ button. Click on it.

Then New Name dialog box will open.

Named Ranges in Excel – Example #3

Create a Named Range using VBA –

We can also create a Named Range in VBA.  Here is an example to explain how this works.

So, this is what we did in the code snippet above:

• We created three variables – myWorksheet of Worksheet datatype, which will store the name of the Excel Worksheet, myNamedRange of Range datatype which will hold the name of the range of cells that we wish to name, and myRangeName, of String datatype which will hold the name of the Named Range.
• We defined our Worksheet (in the variable myWorksheet).
• We then went on to define our range of cells that we wanted to name (in the variable myNamedRange).
• After that, we defined the name of our Named Range in the variable myRangeName.
• After that, we proceed to create the Named Range and provide the reference to which it will point to.

This concludes the Named Range creation process through VBA.

Things to Remember

The following things must always be kept in mind while creating or dealing with Named Ranges in Excel.

• Named Ranges can have names that start with either a backslash (\), letter or underscore (_). Numbers or other special symbols are invalid as the starting character. If any other character other than letters, backslash or an underscore is given as the starting character, then Excel will throw an error.
• There is also a limitation on how long the name of a Named Range can be. The maximum length of a name can be 255 characters. Anything above 255 characters will cause Excel to throw an error.
• Every name has to be a continuous sequence of characters. There is no scope to have spaces, tabs, or any punctuation marks in the name. This is done so as to maintain uniformity and make the names easier.
• Another important instruction while working with Named Ranges is that the name of a Named Range must not have any conflict with cell references.
• As we saw, there is a limit on the maximum length for a name. Conversely, a name must also be of at least one-character length at minimum. Names are allowed to be of a single character length, however, there are few characters such as ‘r’ and ‘c’ which are reserved by Excel and can not be used as Names.
• As a bonus, Names aren’t case sensitive, which means that names such as “Jon”, “JON”, “jon” are all the same to Excel.

Recommended Articles

This has been a guide to Named Range in Excel. Here we discussed How to create Named Range in Excel along with practical examples and downloadable excel template.  You can also go through our other suggested articles –

1. Quick Access Toolbar in Excel
2. Guide to Excel Auto Numbering
3. Guide to Excel MAX IF Function
4. How to use AutoFill in Excel