Cells can be grouped into lists or ranges in Excel. Assigning names to cells or ranges of cells allows us to quickly select the cells. A named range is also easier to remember and to use in formulas.
Excel provides three methods to name a cell or a range of cells:
- Name Box
- Defined Name Tools
- Excel Name Manager
Figure 1. Final result: Assign name to cells and range
Name Box
The name box is located to the left of the formula bar. It displays the currently selected cell in the worksheet. For the example below, the name box displays “B3”, which is the reference of the selected cell.
Figure 2. Sample of Name Box
In order to create a named range, we simply select the cells we want to name and type the name in the Name Box.
Example:
Select cells B3:B14 and type “Month” in the name box. It’s that easy to create an Excel named range.
Figure 3. Output: Assign name using Name Box
Defined Name Tools
Another way to create a named range is through the Defined Name Tools.
Step 1. Click Formulas, then Define Name
Figure 4. Defined Name Tools
Step 2. The New Name dialog box will appear.
Example:
In the Name textbox, enter “Days”.
In the Refers to: text box, enter the range =Sheet1!$C$3:$C$14, or click the button then select the range C3:C14.
Figure 5. New Name preview
We have now created a named range “Days”. We can also add some comments when naming the range of cells to help us better understand the named range through an explanation.
Name Manager
The Name Manager, be it version 2016, 2013 or 2010, allows us to add named ranges, or to edit existing names. It also helps us to view existing named ranges.
How to find a named range?
In order to find named ranges, we have to launch the Name Manager through the Formulas tab, or press Ctrl + F3.
Figure 6. Named Manager in Excel Formulas tab
The Name Manager dialog box will appear, showing the existing named range “Days” that we created above.
Figure 7. Find named range through Name Manager
How to add a named range?
In the Name Manager dialog box, we click New to add a named range. The New Name dialog box will appear. We can then add a new named range by filling out the Name and Refers to textbox.
Example:
In the Name textbox, enter “Data”.
In the Refers to: text box, enter the range =Sheet1!$B$2:$C$14, or click the button then select the range B2:C14.
Figure 8. Output: Add a named range
We have now successfully created another named range “Data” through Name Manager.
Figure 9: Output: Add a named range
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment