As a manager, you can insist on the use of names in projects constructed by people who work in your organization. Named parameters make it easier for you to transfer responsibility for project components from one individual or team to another. This makes your organization less dependent on the availability of particular individuals.

Names have numerous other advantages, including lower costs resulting from:

• Higher reliability.
  It's much easier to tell that a formula that uses the name InterestRateForEquipment instead of the name InterestRateForReceivables is wrong, than it is to tell that it should have used A27 instead of A28. And, as explained below, if you follow certain naming conventions, Excel helps you spot typos.
• Easier copying from one project to another.
  If you use names in formulas, and if you're consistent from one project to the next, copying formulas across projects is much easier — the names just copy along perfectly. If you don't use names, then, most likely, literal references such as "A24" would have to be changed when you copy the formulas that contain those references.
• Easier sharing of templates.
  If you make a template for others to use, they'll have an easier time understanding it if you use names instead of literal references.
• More reliable cross-workbook references.
  In the organizational environment, we often need to link cells across workbooks. If the independent workbook changes, and the cross-workbook reference makes explicit use of rows and column designators, the link might become invalid. Using names ensures that wherever the source range moves, the dependent formulas can find it.

As you become familiar with names, you'll be able to add to this list of advantages.

If you're evaluating an organization's assets, remember that its financial processes use worksheets, and that those worksheets are important assets that help determine organizational effectiveness. If the organization's worksheet tools don't use names, what does that tell you about the bandwidth, flexibility and reliability of the organization's financial processes? Hint: it's not a good omen.

One common practice that raises maintenance costs is parameter sprinkling. Parameter sprinkling is the practice of "hard wiring" numerical parameters into cell formulas. For example, suppose that a depreciation period is 20 quarters. Then in calculating the depreciation amount for one quarter, each cell that computes it must divide by 20. But if later on, a decision is made to depreciate over 12 quarters, all those 20's must be changed to 12's. When "20" is used literally in whichever worksheet cells need it, we say that the parameter has be sprinkled. It's a practice that creates real maintenance headaches.

The legal characters for names are all the letters and numbers, the underscore and the period. A name must start with a letter or underscore, and cannot look like a literal reference. For example, A1 is illegal as a name because it looks exactly like a reference to cell A1. Prior to Excel 2007, since Excel's columns are headed A through IV any name of the form

where Number is any positive integer up to 65536, looks like a reference, and is illegal. So, for example, DF27 and IV3123 are just as illegal as A1.

In Excel 2007 (and presumably henceforward), Excel has rows through 1048576, and columns through XFD.

Excel won't let you define a name that looks like a cell reference: F77 for example. But since Excel 2007 has more rows and columns than earlier versions, the earlier versions sometimes permit you to define names that look like cell references in Excel 2007 and later. For instance, Excel 2003 won't object to Cap1 as a name, even though it's a legal cell reference in Excel 2007. This isn't a problem, unless you intend someday to use that workbook in Excel 2007. Since most of us do someday want to (or have to) upgrade to current versions of Excel, it's best to avoid names that could be legal cell references in Excel 2007. That means: no letter combinations from A XFD, followed by integers less than or equal to 1048576.

It's a really bad idea to use a name that looks like a column designator. Some of them are legal, such as A and B, but even though they're legal, such names cause confusion. In Excel 2003, the column designators are A, B, C, …, Y, Z, AA, AB, AC, …, AY, AZ, BA, BB, BC, …, IV. Avoid using these as names. In Excel 2007, they range from A through XFD.

More candidates to avoid are the names of Excel's worksheet functions. There's no harm in defining a name that is the same as the name of a function you don't plan to use, except that it's sure to confuse you later, or to confuse the poor soul who later comes along and has to maintain what you've built. See the complete list of worksheet functions.

<snip>…

Some of the page you've been reading has been suppressed from this sampler. To see the complete version of this article, why not order the full course? It's available at this Web site or in downloadable format.

Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. Add this item to your order

Or order via Google Checkout.

Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. Add this item to your order

Or order via Google Checkout.

More Info

…<end snip>

Suppose that the Staff Salary Per Quarter is in cell A5. Then you want to define the name StaffSalaryPQ to be $A$5, an absolute reference, so that wherever you use it, it will point to the same place, even when you copy, paste or fill. See the explanation of absolute references.

But there is a use for names for relative references. For example, if you're in cell D4, defining the name Up to be D3 (as opposed to $D$3) gives you a handy way to refer to the cell immediately above wherever you are. Similarly, define Dn (short for "Down") to be D5, Rt (short for "Right") to be E4 and Lt (short for "Left") to be C4. If you include these names in all your worksheets, and use them in formulas, your worksheets will be easier to understand. Of course, it's best to make these local names. If they're global names, then they point to the sheet on which you defined them. There is a tool for doing this, included in the command macros file for this course.

For example, suppose you need a formula that is the sum of the cell to the left and the cell above, as we did when we constructed running sums. Using these relative names, the formula would be =Lt+Up.