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) .

SPONSORED REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.