Give Excel Cells A Descriptive Name

Q. I’ve been trying to give descriptive names to spreadsheet cells—rather than use cell-location codes (A1, B2 and so forth)—and while sometimes it works, other times I get error messages. Can you help?

A. Perhaps you’re committing a naming error. Many people fall into the trap because of a programming idiosyncrasy. But before showing you the error, let me describe how—and why—names are used in Excel. Giving names to cells, or even groups of cells, makes Excel much easier to use. For example, it’s far easier to recognize a cell if you name it Travel Expenses rather than a code, A1, for example.

But—and this is where you probably made your mistake—if a cell’s name is made up of more than one word, there can be no spaces between the words. If the name does have more than one word, fill in the space with an underscore (_). You can also use periods (.), backslashes () and question marks (?). In addition, names must start with either a letter, a backslash or an underscore.

There are two ways to name a cell: After you highlight the target cell, go to Insert, Name, Define Name and enter the name you want under Names in workbook and click on Add . All the names you added will be displayed on the screen, and when you highlight one, its location will be disclosed under Refers to: (see screenshot at right).

A faster way to add a name is to highlight the target cell and then type your choice of name— Sales in this case—in the Name box in the upper left-hand corner of the worksheet. In the screenshot, the box contains the word Sales .

If you want a cell to show sales minus travel expenses, you would type in this formula: =sum(Sales-Travel_Expenses) .