1.2 Defining Names
Note: We strongly encourage you to watch the accompanying video in each section. While we capture many of the basics in the text, the videos contain better visualizations of the topics we cover and many nuances of the functionality not covered in the text.
Like most functionality in Excel, there are multiple ways to create or define a new name.
The most basic way to define a name is to select a cell that you want to provide a name and then use the name box to define the name. The name box is found to the left of the formula bar and by default contains the reference to the cell that you are currently in.
To define a name, you simply type the name in the name box that you want to use to refer to the currently highlighted cells. While Excel allows you to use many different names and characters, it is important to note that there are some rules. For example, you can’t use a space in a name, and a name can’t look like a cell reference. The following table summarizes the rules of names in Excel:
| Rule | Description |
|---|---|
| Spaces aren’t allowed | Names cannot include spaces. Users typically either remove the space or substitute an underscore. So, Sales Revenue is not allowed, but SalesRevenue or Sales_Revenue is. |
| Names must start with a letter or underscore | While numbers can be used within names, they can’t be the first character. |
| Names can consist of letters, numbers, and the underscore and period characters | Besides the first character, you can use any combination of letters, numbers, and the underscore and period characters to create names in Excel. |
| Names are limited to 255 characters | Names cannot be longer than 255 characters. |
| Names are not case-sensitive | Names are not case-sensitive even though Excel will store the name as it looked when you defined it. If you defined the name SalesRevenue, you can use it in a formula as salesrevenue, salesRevenue, SALESREVENUE, or any other mixed-case combination and it will still work. |
| Avoid symbols | Avoid symbols except for underscores and periods. While you may be able to use other symbols such as ? or \, it’s probably best to avoid them. |
| Names must not look like cell references | Names such as B37 or AB14 are not allowed. Further, because there are different ways to refer to cells, such as the R1C1 style, you can’t use the letter R or the letter C alone as a name. |
Another way to define a name is to use the Define Name option found on the Formulas menu → Defined Names group → Define Name.
When you create a new name using this option, a dialog box will pop up. Notice that Excel will look at the cells around the current selection and suggest a potential name. In this case, it substituted a _ for the space and suggested the Interest_rate name.
You can also suggest a scope for the new name. Scope refers to how widely (or narrowly) this name can be seen. By default, the name will be accessible throughout the entire workbook. However, if we wanted to, we could restrict the name to only be accessible on a current workbook. You can also add an optional comment.
The Refers to input box allows us to specify a cell, value, or formula that we want this name to refer to. When defining a new name, an absolute reference to the cell or cell range for the current selection will be automatically entered into the box.
Notice the reference:
=Basics!$D$7
As you probably know, Basics refers to a specific worksheet name, and $D$7 is an absolute reference to the cell D7 on that page. That means, no matter where I am in the workbook or worksheet, I will always refer to cell D7 on the Basics worksheet. The exclamation point separates the worksheet name from the cell reference. As you will see in later sections of this lesson, sometimes you will refer to an absolute cell reference and other times you will create a relative reference (e.g., Basics!D7) that will vary depending on what cell you use the name in.
Once you have created names, you can find them in the name manager. You access the Name Manager by selecting Formulas → Name Manager.
The Name Manager contains all of the defined names in a workbook. From the manager, you can create new names, edit existing names, and delete names you no longer need.
So, you now know how to create names, but how do you use them? Well, you can simply use them in formulas where you would have used the cells that they reference. For example, instead of calculating the interest on the loan by referring to cell D7 (that contains the interest rate), you can refer to Interest_rate, as shown in the following image.
In this module, we have primarily looked at creating references to a single cell. However, often you will create a name for a cell range. This is often referred to as a named range. Interestingly, as shown in the video, you can create a name for a non-contiguous block of cells.