Spreadsheet tips with Lotta Rosenkallums . . .

Naming Cells and Blocks

©2005, Information Disciplines, Inc., Chicago

Suppose you're trying to understand an Excel^® spreadsheet model developed by someone else, and you find the following formula in cell D6:

      =sum(d11:d99)*a$201

But suppose instead the formula was:

      =sum(d11:d99)*CommissionRate

or perhaps this:

      =sum(Sales)*CommissionRate

Clearly, the first version conveys no information about the intent of the formula. You have to examine the cells being referred to and try to infer what they contain. Commentary or "labelling" may help, but the developer who coded such a cryptic formula was also unlikely to supply adequate documentation.

Computer programmers writing procedural programs habitually give meaningful names to data items. Failure to do so is considered shockingly bad practice.

Surprisingly many spreadsheet developers don't. Why not? Perhaps because of the way spreadsheet concepts are taught to naive end users. In lesson one, we learn the scheme for identifying columns by letters and rows by numbers. In a later lesson we learn about relative coordinates and how to replicate a formula through a column or a row. We also learn how to thwart relativization by inserting dollar signs into the cell reference. The course may mention symbolic names near the end, but before then the user feels confident of doing everything he or she needs without that "advanced" feature.

Unfortunately, the spreadsheet processors themselves may discourage users from defining symbolic names. You have to go to some trouble to define a name, and the way you do it is different in each of the leading software products. The process is somewhat error prone, and careless future changes can too easily cause a previously defined name either to become undefined or to designate the wrong cells. Nevertheless, using symbolic names is an essential good practice, and the effort invested in mastering your software's techniques for managing names will be amply repaid in simpler maintenance.

As a minimum, I recommend that you define symbolic names for:

• All constants, including tables
• The body of the data area that is to be printed in a hard-copy report.
• Intermediate values used to simplify long formulas.
• Global parameters that can be set by the user. (In this case we often repeat the name of the cell as a prompt in the cell immediately to the left. Excel and other processors offer a feature for producing a dictionary of defined names as string constants.)

Return to Spreadsheet tips
IDI home page

Last modified January 4, 2005