Tip #67 - Excel - Defining Names

100 Computer Tips in 100 Days

Tip #67 - Excel Defining Names

Every cell in an Excel worksheet already has a name. The name of the cell is the column letter and row number, in that order, such as B27, G9. In addition to the default name, you can give a cell or a group of cells, a “nickname” that can be used in formulas, navigation and selection.

The name box is located in the upper left corner of the worksheet, above the row numbers. It displays the name of the active cell. When you hover above this box it shows the Name Box label. Clicking in this box will select the current name making it possible for you to type in a new name. After the new name is typed in the name box you MUST press the Enter key. Forgetting to press the Enter key will result in the name you've created being lost. There are just a few rules when naming cells:

• A name must be more than one character and less than 255 characters. If you can’t come up with a name that’s shorter than 255 characters then you’re not thinking hard enough.
•  A name cannot begin with a number. It can, however, begin with a letter, an underscore (_)or a backslash (\).
•  A name cannot contain spaces. If you want to name your cell First Quarter you can do so by taking out the space (FirstQuarter) or by inserting an underscore (First_Quarter).
•  A name must be unique and must not resemble a number, cell address or reserve word. A reserve word is text used for functions and formulas. For example you cannot call a cell by the name SUM as it is already used in the function SUM.
•  A name cannot use mathematical symbols such as + - * / <>.

If you examine the rules, you’ll see that you really have a lot of flexibility in naming cells.

Notice the name "second_qtr_sales" in the name
box in the upper left side of the picture.

Now let’s see names in action. In the screen shot I have selected the cells that I want to name. I clicked in the name box and typed “second_qtr_sales” and pressed the Enter key. Notice the new name in the name box. You might think the name box should display C7 but defined names dominate. I then used this name in the average formula (=average(second_qtr_sales).

Happy computing!