How to Assign a Name to a Range in Excel




You can make it easier to navigate Excel worksheets and build Excel formulas by applying names to your ranges. A range name is a text label that you apply to a single cell or to a range of cells. Once you have defined a name for a range, you can use that name in place of the range coordinates, which has several benefits.

For example, range names are more intuitive than range coordinates, particularly in formulas; range names are more accurate than range coordinates; range names are easier to remember than range coordinates; and range names make it easier to navigate a worksheet.

Define a Range Name: Before you can use a range name in your formulas or to navigate a worksheet, you must first define the range name. You can define as many names as you need, and you can even define multiple names for the same range. You can create range names by hand, or you can get Excel to create the names for you automatically based on the existing text labels in a worksheet.

Use Worksheet Text to Define a Range Name: If you have several ranges to name, you can speed up the process by getting Excel to create the names for you automatically based on the range’s text labels. Text labels make a worksheet easier to read and understand, but in this section you see that they also make it easier to define range names.

For example, if you have a column of sales data that has the label “Sales” on top, Excel can automatically apply the name “Sales” to that range. You can create range names from worksheet text when the labels are in the top, or bottom, row of the range, or the left, or right, column of the range. Select the range or ranges you want to name.

Navigate a Workbook Using Range Names: One of the big advantages of defining range names is that they make it easier to navigate a worksheet. You can choose a range name from a list and Excel automatically selects the associated range.

This is much faster than scrolling through a workbook by hand, or by entering a cell or range reference into Excel’s Go To command. Excel offers two methods for navigating a workbook using range names: the Name box and the Go To command.

Delete a Range Name: If you have a range name that you no longer need, you can use Excel’s Name Manager to delete it. This is a good practice, because you do not want to have unused range names in a workbook. Getting rid of unneeded range names reduces clutter in the Name Manager dialog box, and it also makes the Name box easier to navigate.

It is also a good idea to delete unused range names because you may need to reuse the names for different ranges later on.