In Excel, you can define a named range using three methods: the Name Box, the Define Name button, and the Excel Name Manager.
Method 1: Using the Name Box
The Name Box is the quickest way to create a named range:
- Select the cell or range of cells you want to name.
- Enter the desired name in the Name Box.
- Press Enter.
And just like that, your new named range is created!
Method 2: Using the Define Name Option
Here’s another way to create a named range:
- Select the cell(s) you want to name.
- Go to the Formulas tab and click the Define Name button in the Define Names group.
- In the New Name dialog box, specify the following:
- Name: Type the name for the range.
- Scope: Choose the scope (default is Workbook).
- Refers to: Verify the reference and adjust if necessary.
- Click OK to save your changes and close the dialog box.
Note: By default, Excel uses absolute references for named ranges. To create a relative named range, remove the dollar signs ($) from the reference. Ensure you understand how relative names function in worksheets before doing this.
While this method involves a few more steps, it offers additional options such as setting the name’s scope and adding comments. The Define Name feature also allows you to name constants or formulas.
Method 3: Using the Excel Name Manager
The Name Manager is typically used to manage existing names but can also be used to create new ones:
- Go to the Formulas tab and click Name Manager in the Defined Names group, or press Ctrl + F3.
- In the Name Manager dialog box, click the New button in the top left corner.
- This opens the New Name dialog box where you can set up a new name as described in the previous section.
Tip: To test the newly created name, select it from the Name Box dropdown list. Once selected, the corresponding range will be highlighted on the worksheet.
For More: Microsoft Excel
To Learn: Vlookup