Naming constants

Virtually every experienced Excel user knows how to create cell and range names (although not all Excel users actually do so). But most Excel users do not know that you can use names to refer to values that don't appear in your worksheet: that is, constants.

"Natural Language" References

Beginning with Excel 97, you can write "natural language" formulas that use row and column headers. It's not necessary to actually define these names — Excel figures them out automatically. You connect these pseudo-names by using the intersection operator (that is, a space character). For example, you might create a formula like this:

=January Sales

Excel would display the value at the intersection of the column header (Sales) and the row header (January).

Although this type of thing may be convenient, I suggest that you avoid this feature like the plague. Using these pseudo-names is unreliable and difficult to document, and you cannot use these names in your VBA code. When this feature first became available, Microsoft touted it as a significant ease-of-use feature. Microsoft now downplays this feature, and it is turned off by default. You can check the value of Accept Labels in Formulas setting in the Calculation panel of the Options dialog box.

Suppose that many formulas in your worksheet need to use a particular interest rate. Some people would type the interest rate into a cell and give it a name, such as RATE, so that they could use the name in their formulas. For example, the following formula uses the name RATE:

=RATE*A3

The other alternative is to call up the Define Name dialog box and enter the interest rate directly into the Refers To box (see Figure 3-3). Then you can use the name in your formulas just as if the value were stored in a cell. If the interest rate changes, just change the definition for RATE, and Excel updates all the cells that contain this name.

Figure 3-3: Excel lets you name constants that don't appear in worksheet cells.

By the way, this technique also works for text. For example,you can define the name IWC to stand for International Widget Corporation. Then you can enter =IWC into a cell,and the cell displays the full name.

0 0

Post a comment