Microsoft Excel 2007 - Naming Cells

What's in a name?

If you are maintaining complex formulas in your worksheets, assigning descriptive names to cells or ranges of cells can help you identify the cells you need to work with.

Assigning Names To Cells

Names are far easier to remember than cell addresses, so it makes sense to assign a descriptive name to cells where possible. You can use a cell's name to reference it in a formula you are creating, or to navigate to that cell by using its name. Also, the use of cell names in forumulas makes them easier to understand.

Methods Of Naming Cells

Excel 2007 provides several ways of naming cells:

The Name Dialog Box

New Name Dialog

Select a cell or range of cells by clicking and dragging over them and click Formulas > Defined Names > Define Name. In the New Name dialog box that is presented, type in the name of the cells. Using the Scope drop down list you can tell Excel whether the name should be valid for the current workbook or only a particular sheet within the workbook. Enter any comments that you think would help. The Refers to box details what cell range you selected.

The Name Box

A quicker way to assign a name is to select the required cells and then simply type the name into the name box (to the left of the formula bar). Press the enter key to complete the assignation. You can also use the name to jump to a previously named cell or range by clicking on its down arrow to display teh drop down list. Select a name and the corresponding cells become active.

Name Box

Create From Selection

Create Names From Selection

You can use the text in adjacent cells to name those cells. For example imagine that you have some friends' names running across a row. Select the cells that contain the names and also the cells immediately below them. Click Formulas > Defined Names > Create From Selection. Excel has a good idea about which cells contain the names and checks appropriate box for top row, left column, bottom row or right column, but you can change this selection manually.

If the selected cells contain text that would be invalid as a name (for example text that contains a blank space), Excel will modify the name. "gross sales" would become "gross_sales". Additionally, if creating names from a selection would result in a duplication of already existing names Excel will issue a confirmation dialog asking whether you want to replace the existing definition. The choice is yours.

Rules To Follow When Naming Cells

You must ensure that the following rules are adhered to when assigning names to cells:

  • Names cannot contain spaces
  • Names cannot begin with a number
  • Symbols are not allowed - with the exception of '_' and '.'
  • The length of a name is limited to 255 characters