Excel Tutorials
Learn Excel - Chapter 7
Formula - Name Manager
Named Range
Named Range is one or more cells that is given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via Name Box.Name Box
In Excel, the Name Box refers to an input box that appears to the left of the formula bar. The Name Box normally displays the address of the active cell on the worksheet. You can also use the Name Box to quicky create named range.Once you have created a named range, you can check that it has been created by looking in the drop-down list within the Name Box, which is located at the top of your spreadsheet, to the left of the formula bar.
The drop-down list in the Name Box contains a list of all currently defined named ranges. Selecting a named range from the drop-down list causes that range to be selected and the name of the range to be displayed in the Name Box.
Methods to Create Name for a Cell
Method 11) Go to a specific Cell
2) Type the Name of the cell directly in the Name Box
Method 2
1) Go to a specific Cell
2) Click the Name Manager option from the ribbon at the top of your spreadsheet. This is found within the 'Defined Names' group on the Formulas tab.
3) Click New Command. The New Name window appears.
4) Type the Name of the Cell e.g. Qty in the Name Textbox
5) Click OK
6) Lastly, click Close Command.
Method 3
1) Go to a specific Cell
2) Click the Define Name option from the 'Defined Names' group on the Formulas Ribbon.
3) Click Define Name Command. The New Name window appears.
4) Type the Name of the Cell e.g. Price in the Name Textbox
5) Click OK
How to Create an Excel Named Range
Method 1 :Create a Named Range Using the 'Define Name' Command
Imagine you want to create a named range that refers to the sales figures in the example spreadsheet above (i.e. the named range will refer to the range of cells B2-B5).
The named range is created as follows:
1. Select the range that you want to name (cells B2-B5 in the example spreadsheet).
2. Select the Define Name option from the ribbon at the top of your spreadsheet. This is found within the 'Defined Names' group on the Formulas tab.
3. You will be presented with the 'New Name' dialog box, as shown below:
4. Within the 'New Name' dialog box:
-
Note that the name Product_Price has been inserted into the Name: field of the dialog box. Excel has automatically taken this name from the column header (in cell B1 of the spreadsheet). As Excel names cannot include spaces, Excel has inserted an underscore in place of the space in the header Product Price.
If you want (or if Excel does not insert a default name), you can type any name that you want to use into the Name: field. However, you should be aware of the following rules for Excel names:- Names must be no more than 255 characters long;
- Names must start with a letter, an underscore or a backslash character;
- The rest of the name should be made up of letters, numbers, underscores or periods (no other characters are allowed);
- Strings that are the same as a cell reference (e.g. "B1") or the single letters, "C", "c", "R" or "r" can not be used as names.
- The Scope: field in the dialog box refers to the parts of the Excel workbook where the new name will be recognised. You can select this to be the entire workbook or a single sheet.
- The Refers to: field has been populated with the current selected cell range. If you want, you can overwrite this with a different range. Once you are satisfied that the values in the dialog box are correct, click OK. Excel will then create the new name Product_Price that refers to the range B2-B5.
Create a Named Range Using the 'Create from Selection' Command
Excel also provides a 'Create from Selection' command, that allows you quickly create a named range from a range of cells containing headers or labels.
A named range for the sales values in the example spreadsheet above can therefore also be created as follows:
1. Select the range that you want to apply the name to including the column header (i.e. cells B1-B5 in the example spreadsheet).
2. Select the option Create from Selection from the ribbon at the top of your spreadsheet. This is found within the 'Defined Names' group on the Formulas tab
3. You will be presented with the 'Create Names From Selection' dialog box, as shown below:
4.Within the 'New Name' dialog box:
-
Note that the name Product_Price has been inserted into the Name: field of the dialog box. Excel has automatically taken this name from the column header (in cell B1 of the spreadsheet). As Excel names cannot include spaces, Excel has inserted an underscore in place of the space in the header Product Price.
If you want (or if Excel does not insert a default name), you can type any name that you want to use into the Name: field. However, you should be aware of the following rules for Excel names:- Names must be no more than 255 characters long;
- Names must start with a letter, an underscore or a backslash character;
- The rest of the name should be made up of letters, numbers, underscores or periods (no other characters are allowed);
- Strings that are the same as a cell reference (e.g. "B1") or the single letters, "C", "c", "R" or "r" can not be used as names.
- The Scope: field in the dialog box refers to the parts of the Excel workbook where the new name will be recognised. You can select this to be the entire workbook or a single sheet.
- The Refers to: field has been populated with the current selected cell range. If you want, you can overwrite this with a different range. Once you are satisfied that the values in the dialog box are correct, click OK. Excel will then create the new name Product_Price that refers to the range B2-B5.
Create a Named Range Using the Name Box
You can also create a named range using the Name Box, which is located at the top of your spreadsheet, to the left of the formula bar In order to create a named range using the Name Box:
1. Highlight the range of cells that you want to the named range to refer to;
2. Type the required name into the Name Box and press the Return (or Enter) key on your keyboard.
Note that the name used must start with a letter, an underscore or a backslash character and the remaining characters must be letters, numbers, underscores or periods. No spaces or other characters are accepted in named ranges.
If a range already exists for the name you typed in, this existing range will be selected. Otherwise, a new named range will be created, which refers to the current selection.
Using Named Ranges In Formulas
Named Ranges can be used in Excel Formulas, in place of cell references.For example, if you wish to sum all the values in the named range Sales_Values, you can use the formula:
=SUM(Sales_Values )
which is equivalent to the formula:
=SUM( B2:B5 )
Clearly, as your formulas get longer and more complex, the use of names can help to clarify them, and therefore assist in preventing errors.
How to Edit an Excel Named Range
If you wish to edit a named range simply select the Name Manager option from the 'Defined Names' group on the Formulas tab of the Excel ribbon. You will then be presented with the 'Name Manager' dialog box, as shown below:
The 'Name Manager' dialog box lists all currently defined named ranges. Select the name of the range that you want to edit and you can then edit this range by either:
- Changing the range within the Refers to: field at the bottom of the dialog box and then clicking on the Close button;
or
- Clicking on the Edit... button at the top of the dialog box. This causes the 'Edit Name' dialog box to be displayed, as shown below
The 'Edit Name' dialog box allows you to edit other features of the current named range, including the name and the range of cells referred to.
How to Delete an Excel Named Range
If you wish to delete an Excel named range simply select the Name Manager option from the 'Defined Names' group on the Formulas tab of the Excel ribbon You will then be presented with the 'Name Manager' dialog box, as shown below:
Select the name of the range that you want to delete and click on the Delete button from the top of the dialog box.
If asked, confirm that you want to delete the Named Range and then click on the Close button to close the Name Manager dialog box.
To Learn Advanced Excel Whatapp/Call 8422000829. Disount Code WEBSAB501
Home Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 8 Chapter 9 Chapter 10 Excel Shortcuts
Keeping Learning...