Connecting Technology and Business.

Calling Names in Excel!

Have you ever had to interpret someone else’s worksheet? Or have you ever had to use a worksheet that you constructed months or perhaps years ago, and then been completely unable to figure out what you had in mind when you constructed it? You probably have and, if so, you know what a headache it can be.

The principal difficulty with many otherwise useful worksheets is that their authors don’t document them. Consider this worksheet formula:  

It could take a couple of minutes to figure out what that formula is up to, even if you know the worksheet’s basic purpose. However, it would take you only a few seconds if the author had used this formula instead:  
=IF(AND(YearToDateSales<30000,Tenure<5),Units*Price*LowCommission, Units*Price*HighCommission)

It’s not too difficult to infer what this formula says:  
If this person’s sales during this year are less than $30,000, and this person was hired fewer than five years ago, return the sales amount times the lower commission; other- wise, return the sales amount times the higher commission.

To help make your work self-documenting, in many instances, you should give names to Excel worksheet cells, ranges, and constants. You can then use those names in your formulas and functions so that you can see it’s multiplying sales dollars by a commission, not simply one relatively anonymous cell by another.

Assigning Names 

To name a  cell   or  range , begin by selecting it on the worksheet. Click the Ribbon’s Formulas tab and click the Define Name drop-down in the Defined Names group. Choose Define Name in the drop-down list and type the name you want to use in the Name box. You can also specify the name’s scope as sheet-level or book-level. If you’re using a version of Excel that precedes Excel 2007, choose Insert, Name, Define, and type the name you want to use in the Names in Workbook edit   box; then click OK.  
Or use this quicker method: After you have selected the cell or range, click in the Name box (immediately above the column header for column A and left of the drop-down arrow), type the name, and press Enter.

To name a  constant   such as  LowCommission , click the Define Name dropdown (or choose Insert, Name, Define), and type the name of the constant in the Names in Workbook edit box. Then, in the Refers To edit box, type the value that you want to assign to the constant and click OK. (You can’t use the Name box to define a constant directly.)

A side benefit of using names instead of cell or range addresses is that you can paste names into formulas as you are creating them. After you have started typing a formula, click the Ribbon’s Formulas tab and select the Use in Formula drop-down from the Defined Names group. Then click the name you want in the drop-down list. (In earlier versions, you can choose Insert, Name, Paste and select the name you want to use from the Paste Name list box.) This approach saves you keystrokes and helps prevent misspellings. Also, you don’t have to recall existing names: They’re right there in the list box.   

When you choose a name for a range or a constant, consider using both uppercase and lowercase letters: for example,  TotalLiabilities . Mixing uppercase and lowercase makes the name easier to read. (Compare to  totalliabilities.) You should probably avoid using all uppercase letters. Excel’s worksheet function names (for example,  SUM   and  AVERAGE ) use all uppercase letters, and you don’t want to define a name that could be confused with a function.

Blank spaces and certain special characters, such as the percent symbol, aren’t allowed in names. Some people like to use an underscore in place of a space, preferring Total_Liabilities   over  TotalLiabilities.

- From the library of Wow! eBook