Named Ranges

Creating a Named Range in Excel

There are three ways to define a name in Excel:-

1. By Typing a name in the Name Box
   We select a cell or a range of cells that we want to name, then type a name in the Name Box, and after that, press Enter.
    
2. Creating a name by using the Define Name Option
   We select a cell or a range of cells. Then on the Formulas tab under Define Names Group, we click the Define Name button. Then a New Name dialog box pops up where we need to specify three things.
   We need to type the range name in the Name Box. Then we set the name scope in the Scope Dropdown. At last, we check the reference and correct it if needed in the Refers to the box. In the end, we click OK to save all the changes and close the dialog box.
    
3. Making a Named Range using Excel Name Manager
   We go to the Formulas tab > Defined Names group, click Name Manager. Or, press Ctrl + F3. Then in the top left-hand corner of the Name Manager dialog window, click the New button. This will then open the New Name dialog box where you configure a name as done previously.

Creating an Excel Name for a constant

Microsoft allows us to define a name without a cell reference that will work as a named constant. Thus, we use the Excel Define Name feature or Name Manager to create such a name, as explained above.

How to name columns in Excel

1. Select the entire table (including the column and row headers).
2. Go to the Formulas tab > Define Names group, and click the Create from Selection button. Or, press the keyboard shortcut Ctrl + Shift + F3.
3. Either way, the Create Names from Selection dialogue box will open. You select the column or row with headers, or both, and click OK.

Excel Naming Rules

1. An Excel name should be under 255 characters long.
2. The names should not contain spaces and most of the punctuation marks.
3. A name should begin with a letter, underscore (_), or backslash (\).
4. These names are case-sensitive.

Excel Name Scope

There are two types of Excel Name Scope:-

1. Specific Worksheet - the local worksheet level
2. Workbook - the global workbook level

How to Edit Named Range in Excel

First, we open the Name Manager, then select the name and click the Edit button. This will open the Edit Name dialog box where we can change the name and reference. We cannot change the scope of the name. 

We do not need to open the Edit Name dialog box to edit a name reference. We need to select the name of interest in the Excel Name Manager, type a new reference directly in the Refers to box, or click the button at the right and select the desired range on the sheet. After that, we click the Close button. Then Excel will ask if we want to save the changes, and we click Yes.

How to filter names in Excel

The filters available are:-

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

Delete a named range - select it in the Name Manager and click the Delete button at the top.

Delete several names - click the first name, then press the Ctrl key and hold it while clicking other names we want to remove. Then click the Delete button, and all selected names will be deleted.

Delete all defined names in a workbook - select the first name in the list, then press and hold the Shift key, and then click the last name. Release the Shift key and click Delete.

Benefits of using names in Excel

1. Excel names help in making formulas easier to read.
2. It makes formulas easier to re-use.
3. It allows us to create expandable formulas.
4. It helps to simplify navigation.
5. It also allows us to create dynamic dropdown lists.

FAQs

1. What is the shortcut to open the Excel Name Manager?
   Ctrl+F3
    
2. What is the shortcut to getting a list of all Excel names in a workbook?
   F3
    
3. What is the shortcut to creating named ranges from the selection?
   Ctrl+Shift+F3
    
4. What is #REF! error?
   If we delete all cells that make up an Excel named range, the names become invalid and display a #REF! error in the Name Manager.
    
5. What is #NAME? error?
   This error comes up when a formula refers to a non-existing name.

Key Takeaways

In this article, we have extensively discussed the Named Ranges of Excel. We hope that this blog has helped you enhance your knowledge regarding Named Ranges.

If you want to learn more, check out our articles on Days and DATEDIF Function, HLOOKUP Function, Today and Now Function, Dynamic Array Functions.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. 

Enrol in our courses and refer to the mock test and problems available.

Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. 

Happy Coding!