Name range in excel are the ranges which have been given a name for the future reference, to make a range as named range first select the range of data and then insert a table to the range, then we put a name to the range from the name box in the left-hand side of the window, after this we can refer to the range by its name in any formula.
Name Range in Excel (Table of Contents)
What is Name Range in Excel?
Name Range in excel makes it much cooler to keep track of things, especially when you’re using formulas. You can assign a name to a range. And, if there is any change in that range, no problem you need to update the range from the Name Manager. You don’t need to update every formula manually. Similarly, you can create a name for a formula. And, if you want to use that formula in another formula or another location, just refer to it by name.
- The significance of named ranges is that you can put any names in your formulas without thinking about cell references/address. You can assign the range with any name.
- Create a named range for any data or a named constant and use these names in your formulas in place of data references. By this way, you can make your formulas easier to comprehend better. A named range is just a human-understandable name for a range of cells in Excel.
- By using the name range in excel, you can make your formulas much simpler to comprehend better. You can assign a name for a range in excel sheet for a function, for a constant, or for a table data. Once you start using the names in your excel sheet, you can easily understand these names.
Define Names From a Selected Range
- Select the data range you want to assign a name, then Select Formulas and Create from Selection.
- Click on “Create Names from Selection” then select the Top row, Left column, Bottom row, or Right column check box and Select OK.
Excel names the cells based on the labels in the range you designated.
- Use names in formulas then Select a cell and enter a formula.
- Place the cursor where you want to use the name range formula.
- Type the first letter of the name, and select the name from the list that appears.
- Or, select Formulas then Use in Formula and select the name you want to use.
- Press Enter.
Update named ranges in the Name Manager (Control + F3)
You can update the Name form Name manager. Press Control and F3 to update the name. Select the name you want to change, then change the reference range directly.
How to Use Name Range in Excel?
Let understand the working on conditional formatting by simple excel examples.
Example #1 Create a name by using the Define Name option
- Select the cell(s).
- On the Formulastab, in the Define Names group, click the Define Name
- In the New Namedialog box, specify three things:
- In the Namebox, type the range name.
- In the Scopedropdown, set the name scope (Workbook by default).
- In the Refers tobox, check the reference and correct it if needed, Click OK to save the changes and close the dialog box.
Example #2 Make a named range by using Excel Name Manager
- Go to the Formulas tab then Defined Names group, and click the Name Manager Or, just press Ctrl + F3 (my preferred way).
- the top left-hand corner of the Name Manager Dialog window, click the New… button:
Name Range using VBA
We can apply the naming in VBA, here is the example as follows:
‘Adding a Name
Names.Add Name:=”myData”, RefersTo:=”=Sheet1!$A$1:$A$10″
‘You can use Name property of a Range
Sheet1.Range(“$A$1:$A$10”).Name = “myData”
Things to Remember
You must follow the below instruction while using the name range in excel.
- Names can start with a letter, backslash (\) or an underscore (_).
- An Excel name should be under 255 characters long.
- Names must be continuous and can’t contain spaces and most punctuation characters.
- There must be no conflict with cell references in names using in excel.
- You can use single letters as names, but the letters “r” and “c” are reserved in excel.
- Names are not case-sensitive – “Tanuj”, “TANUJ”, and “TaNuJ” are all the same to Excel.
This has been a guide to Name Range in Excel. Here we discuss how to use Names in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –