What is Name Range in Excel?

  • The name ranges are significant because you can put any names in your formulas without considering cell references/addresses. Furthermore, 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. In 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.Using the name range in Excel, you can simplify and comprehend your formulas better. For example, you can assign a name for a range in an excel sheet for a function, a constant, or table data. Once you start using the names in your Excel sheet, you can easily understand these names.

Define Names For a Selected Range

  • Select the data range you want to assign a name, then select formulas and create from the selection.

  • Click on the “Create Names from Selection,” then select the “Top row,” “Left column,” “Bottom row,” or “Right column” checkbox 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 the “Enter” key.

Update named ranges in the Name Manager (Control + F3)

You can update the name from the Name Manager. Press “Ctrl” 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 us understand the working of conditional formatting by simple Excel examples.

Example #1 Create a name by using the Define Name option

Example #2 Make a named range by using Excel Name Manager

  • Go to the “Formulas” tab, then the “Defined Names” group, and click the “Name Manager” or press “Ctrl + F3” (the preferred way).

  • In 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:

  • First, select the cell(s). On the “Formulas” tab, click the “Define Name” in the “Defined Names” group. In the “New Name” dialog box, specify three things: First, in the “Name” box, type the range name. In the “Scope” drop-down, set the name scope (Workbook by default). In the “Refers to” box, check the reference and correct it if needed. Finally, click “OK” to save the changes and close the dialog box.

Sub sbNameRange()

‘Adding a Name

Names.Add Name:=”myData”, RefersTo:=”=Sheet1!$A$1:$A$10″

‘OR

‘You can use Name property of a Range.

Sheet1.Range(“$A$1:$A$10”).Name = “myData”

End Sub

Things to Remember

We must follow the below instruction while using the name range.

  • The names can start with a letter, backslash (), or an underscore (_).The name should be under 255 characters long.The names must be continuous and cannot contain spaces and most punctuation characters.There must be no conflict with cell references in names using in excelCell References In Names Using In ExcelCell 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.We can use single letters as names, but the letters “r” and “c” are reserved in Excel.The names are not case-sensitive – “Tanuj”, “TANUJ”, and “TaNuJ” are all the same in Excel.

This article is a guide to the Name Range in Excel. We discuss using names in Excel, practical examples, and downloadable Excel templates here. You also may look at these useful functions in Excel: –

  • VBA RangeVBA RangeRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read moreData Tables ExcelData Tables ExcelA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read moreData Validation using ExcelData Validation Using ExcelThe data validation in excel helps control the kind of input entered by a user in the worksheet.read morePaste SpecialPaste SpecialPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more