Naming a range or any cell in excel is a very unique feature. Most of the people, don’t even know this exists. This feature is very uncommon and regarded as pretty pointless.

But in reality, if one if working with large sets of data and uses functions multiple times on a large range, this feature will literally save your time & effort. In this excel tutorial, we’ll see how to use this feature to work with excel range names & already established named range excel.

What is a Named Range in Excel?

Just like you & I have names, excel ranges or even cells can be given different names. If I want to talk to a friend of mine, I would shout his name to get his attention and call him.
Similarly, named ranges or named cells in excel help us to do the same.

Let’s say if I have a data set where I have a range which I frequently use in functions. The range is C2:C11 now every time I want to refer to this range either I have to drag or select first cell, hit SHIFT + DOWN ARROR to select the range.
That’s okay when the range is small, but what if there are multiple ranges & a large data set.

The best way is to use Excel Named Ranges to make the process easier.

You can create a named range in Excel for different data and refer back to this name whenever you want to select this range. Let’s look at the data below:

Now I can name these 2 different ranges as ‘Qty’ & ‘Sales’. I can also name a single cell whose value I have to use again & again like a cell containing tax percentage which I can name ‘Tax’.

How to Create a Named Range in Excel

Let’s look at the different methods to create named ranges in excel:

Method 1: Using Name Box in Excel

1. Select the range which you want to name.
   
   
   
2. Now go to the Name Box on the left of formula bar. Here input the name to create a named range. Then press Enter.
   
   
   

Method 2: Using Excel Define Name Feature

1. First select the range for which you want create a Named Range in Excel
   
   
   
2. Now go to Formulas and select the Excel Define Name
   
   
   
3. A new dialog box will open. In the Name field, type the name for the data range you have selected. In scope, you can define the scope as the entire workbook or the specific worksheet we’re on. If you select the worksheet, then when you switch to different worksheet you’ll not be able to refer to the Named Range in Excel.
   
   
   

Method 3: Using Excel Name Manager

Mostly Name Manager in excel is for working with already defined names. But we can still use it to create new names for ranges too.

1. Select the range, and go to Formulas Tab > under Defined Names Group click Name Manager or you can use Excel Name Manager Shortcut Ctrl+F3
   
   
2. A new dialog box will open, on top left of the Excel Name Manager window, select the New button.
   
   
   
3. A new dialog box will appear, where you can input the new name as you did in the previous method.
   
   
   

Naming Convention Rules for Named Ranges in Excel

Now that we have learned how to name range in excel. Let’s see some golden rules we must follow while creating Named Ranges in Excel.

1. First character of named range should start with either a letter or underscore (_) or a backslash (\). Other characters can be anything. If this is not followed, excel will show an error
2. Excel Named Ranges are case-insensitive. So a range name ‘Product’, ‘PRODUCT’ or ‘product’ will all be treated as the same name
3. Excel Range Name could be as long as 255 characters.
4. Names cannot contain spaces or other punctuation characters.
5. You can’t use names which represents different cell references in excel. Meaning you can’t name a range as A5 as A5 already represents a cell.
   
   

Finding All Named Ranges

Let’s say you have created multiple named ranges and now you don’t remember the different names you assigned to each range.

Worry not. Here’s how you can find the names:

1. Go to the Formula Tab
   
   
2. In Defined Named group, click on Use in Formula option and choose Paste Names
   
   
   
3. Here you can see the entire list of excel range names.
   
   
   

Excel Name Manager- Editing, Filtering & Delete Named Range Excel

Name Manager in Excel is used to manage the assigned names which is to edit, filter and delete names. Let’s see how can use the excel name manager for the same:

How to Edit Named Range Excel

1. Go to the Formulas Tab and select Name Manager
   
   
   
2. A dialog box will open which will have all the named ranges in excel. Double click on the Named Range which you want to edit
   
   
   
3. In the new dialog box, you can choose a new name, scope and reference. Hit OK and you have successfully edited the named range.
   
   
   

How to Filter Range Names in Excel

1. Go to Formulas Tab and select Name Manager
   
   
2. In the opened dialog box, click Filter to only view certain named ranges.
   
   
   
3. You’ll have the option to choose from the following options:
   1. Names scoped to worksheet or workbook
   2. Names with or without errors
   3. Defined names or table names
      
      

How to Delete Named Range in Excel

1. To delete a name range, first go to Formulas Tab and open Name Manager
   
   
2. Select the named range you want to delete and click the Delete button.
   
   
   
3. To delete multiple named ranges, select the first one then press CTRL and hold it while you select other named ranges. Once done, click Delete and all the selected named ranges will be deleted
   
   
   

Shortcuts for Named Ranges in Excel

Let’s see some useful keyboard shortcuts for working with named ranges in excel.

1. To open Excel Name Manager. Use CTRL + F3
2. To create named ranges from selection. Use CTRL + SHIFT + F3
3. For getting a list of all named ranges in excel. Use F3
   
   

Excel Dynamic Named Range

Till now, we have been only working with static Named Ranges. Where the named ranges only refer to the specific range of cells which you have selected.

Say you created a named range ‘Sales’, from a range of cells C2:C11. Then every time you use the excel range name it’ll only refer to cells from C2:C11.
Now you add more data points to cells C12 & C13. For you now the range is C2:C13, but excel doesn’t know this so you have to go back and again redefine the range of cells. So every time you input new data, you have to update named ranges in excel. To deal with this, excel dynamic named ranges are used, which automatically update new data in named ranges in excel.

Let’s see how we can do this, we have to use the Excel INDEX() function. Instead of defining a range of cells we use a formula. This formula automatically gets updated whenever any new data is deleted or added.

1. Let’s say we have a range of data of ‘Sales’ in cells C2:C11
   
   
   
2. First let’s go to Formula Tab and select Define Name
   
   
   
3. In the dialog box update the following details.
   Name: Sales
   Refers to: =$C$2:INDEX($C$2:$C$100,COUNTIF($C$2:$C$100,”<>”&””))
   
   Now click OK

A dynamic named range in excel is now created. As you add more add or delete it’ll automatically update the same.

To understand how the an excel dynamic named range work, you have to understand how the Excel INDEX() Function works.

Conclusion

Excel Range Name is just a name assigned to a group of cells to help us make our working faster. I hope this article was helpful to you and explained you how to name a range in excel and different methods to do so.

If you have any questions or tips, I’d love to hear them in the comment section below.