©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:
But suppose instead the formula was:
or perhaps this:
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:
Return to Spreadsheet tips
IDI home page
Last modified January 4, 2005