How to Create Named Ranges in Excel

How to Create Named Ranges in Excel

In Excel, you can give a name to a cell or a range of cells. Instead of using the cell reference (such as A1 or A1:A100), you can simply use the name that you assigned to it. Excel Named Ranges makes it easy to refer to data sets in Excel. You can create a named range in Excel for each data category, and then use that name instead of the cell references. There are three ways to create Named Ranges in Excel.

Method 1

1    1.   Select the range or single cell for which you want to create a Named Range in Excel (except header).

2.    Click on Formulas and then Click to Define Name.

3. Type the suitable Name for the selected data range. You can specify the scope as the entire workbook or a specific worksheet, if you select a particular sheet, the name would not be available on other sheets. Click “Ok”. This will create a Named Range.

 Method 2:

1.      Select the range or single cell for which you want to create a Named Range in Excel (except header).

2.   Go to the Name Box on the left of Formula bar and Type the suitable Name for the selected data range for create the named range and press Enter. (Example MATHEMATICS COLUMN REFERENCE (E2:E11) NAME  as MATH)

Method 3:

1.Select the range for which you want to create a Named Range in Excel (Include header).

2.     Click “Formulas” and the click on “Create from Selection” or Press shortcut key crtl+shift+F3

3.  Create Names from Selection Dialog Box open. Select any option from where you want create Names i.e where you have the headers. You can Select two option if have two headers line( one in row and other in column) and click Ok.

1   4. To show the Names Go to Formulas -> Name Manager.

5.   You will see Named Ranges are created as per Header names  as FIRST_LANGUAGE, SECOND_LANGUAGES and so on automatically picks up from the Headers. If there are any spaces between words, it inserts an underscore (header FIRST LANGUAGE becomes FIRST_LANGUAGE) and arrange alphabetically in “Name Manager”.

6. Note: To see Named Range you can click Name Box down arrow.

 Named Ranges rule in Excel

1   1.  First character Should be a letter and underscore (_), or a backslash(\).

2   2.  Name should be Combination of Letters, Numbers, Underscor(_), blacsslash(\) and dot (.).

3   3.   Name cannot contain any space.

4   4.  You can not use names that represent cell references in Excel.

5   5.  A Named Range can be up to 255 characters long.

    6.   Names are not case sensitive. Hence RollNo, ROLLNO Rollno are treated as same name in excel.