As Juliet famously said to Romeo, “What’s in a name?” And she was talking about rows (misspelled as “rose”), so maybe Juliet was using a spreadsheet at the time. There are special rules for Excel names, but you might be surprised to see what is allowed.
Names in Excel
In Excel, you can create names that refer to cells, or to a constant value, or a formula. After you create Excel names, you can use them in formulas, or quickly go to a named range.
There are rules for Excel Names, and here’s what Microsoft says is allowed. It seems clear, but a few of the rules aren’t as ironclad as they look:
- The first character of a name must be one of the following characters:
- letter
- underscore (_)
- backslash (\).
- Remaining characters in the name can be
- letters
- numbers
- periods
- underscore characters
- The following are not allowed:
- Space characters are not allowed as part of a name.
- Names can’t look like cell addresses, such as A$35 or R2D2
- C, c, R, r — can’t be used as names — Excel uses them as selection shortcuts
- Names are not case sensitive. For example, North and NORTH are treated as the same name.
How To Name Cells
It’s easy to name a range of cells – here’s what I usually do (there’s a video at the end of this article too):
- Select the cells that you want to name
- Type a valid one-word name for those cells, in the Name Box at the left of the Formula Bar.
- Press Enter, to complete the name. If you forget that step, the name doesn’t stick.
Later, you can use those names in formulas, or for navigation.
Quick Names
Another easy way to create names is based on text that you’re already entered on the worksheet. For example, type “Months”, then the month names in the 12 cells below that.
- First, select the heading cell, and the cells that you want to name.
- Then, on Excel’s Formulas tab, click the Create From Selection command.
- Check the box to tell Excel where your headings are (top, left, bottom or right), and click OK
- Excel names the ranges with valid names, based on your headings
Since Excel creates the names in this method, you don’t have to worry about what’s valid.
NOTE: You can see the step in the video at the end of this article.
Beyond the Basic Rules
Even though Microsoft’s rules for Excel names say that you must use only letters, numbers, periods, underscores and backslashes, other characters are allowed. It seems that “letters” has a broad interpretation.
I learned about “beyond the basics” technique from Peter B., who sent me a workbook in which he used Unicode text in his Excel names (shown below). I didn’t know that was possible. Thanks, Peter!
My Test of the Rules
Inspired by Peter’s examples, I did a few simple name tests, using characters from the “Alt and Number Keypad” set. For example, I typed an “a” in the Name Box, then Alt+1, Alt+30 and Alt+31. That means “a happy faces goes up and down”, in case you were wondering.
In the screen shot below, you can see that name in the Name Box drop down list, along with a few other unconventional names that I created.
Excel used the characters from cell D2, when I created a name using the “Create From Selection technique on that range.
However, when I created a couple of names in the Name Manager, the special characters show up correctly there, but appear as question marks in the Name Box drop down list. You can see a couple of those in the screen shot above.
Testing the Rules for Excel Names
After looking at Peter’s examples, and doing a few experiments, I did some searching, so see what documentation there might be online, for these anomalies. What is allowed, and what isn’t?
An link in an old Excel newsgroup post led me to a treasure trove of information on rules for Excel names – Martin Trummer’s GitHub project on excel-names. Martin has done an in-depth study of what’s allowed, beyond the basic letters and numbers.
Go to Martin’s project page, and you’ll see his written examples, and there is an Excel file to download. Thanks to Martin, for doing this research!
Video: Name a Range of Cells
Watch this short video to see how to name a group of cells, go to that named group of cells, and use the name in a formula.
Video: Create an Excel Name from Selection
To quickly name individual cells, or individual ranges, you can use heading cell text as the names. Watch this video to see the steps, and go to the Excel Names page on my website for more details and videos.
_______________________________________
__________________________