by David Hakala
Excel formulas may require a lot of arguments. It can be hard to remember that “12*4” means 12 monthly payments for four years. It might be easier to read your formula if that argument read, “12Months*Years”. Well, you can give arguments and cells easily remembered names in Excel.
The NAME() function in Excel assigns any meaningful name you wish to a reference, constant, formula, table, or anything you can selected with a mouse-click. Some examples of named items include:
| Reference | =SUM(C20:C30) | =SUM(FirstQuarterSales) |
| Constant | =PRODUCT(A5,8.3) | =PRODUCT(Price,WASalesTax) |
| Formula | =SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) | =SUM(Inventory_Level,-Order_Amt) |
| Table | C4:G36 | =TopSales06 |
It’s much easier to look at someone else’s workbook and tell what does what when things are named, isn’t it? You may feel the same way about your own work.
There are two types of names. A defined name can be applied by you to any cell, range of cells, multiple nonadjacent cells, formula, or constant value. A table name is for Excel tables only. Excel automatically names new tables Table1, Table2, etc., but you can change the names to things more meaningful to you.
To set a defined name in Excel 2007:
Select the item(s) to which you want to give a name.
Click the Formula tab, then Define Name in the Defined Names area.
Type the name you want to assign to the selection and press Enter.
Now, whenever you type the name preceded by an equals sign, the value of the named selection is called. In our illustration, “=Constant” in any cell will repeat the phrase, “this is my constant”.
You can manage your names using the Name Manager function in the Define Names area of the Formulas tab. This is also where you can rename a table name or a defined name.
Each name has a scope; that is, the name is recognized only within the scope of certain Excel elements. By default, a name in a worksheet is valid only throughout the entire workbook. You can change the scope of any name in Name Manager. If a name’s cope is its own worksheet only, you can specify a name in a different worksheet by preceding the name with the number of its worksheet, i. e.,
Sheet1!Budget
invokes the name Budget from worksheet 1 into whatever worksheet it is entered. Don’t forget the exclamation point between worksheet name and item name.
You can also invoke names from entirely different workbooks. The syntax looks like this:
Book1!Constant
where Book1 is the name of the workbook from which you wish to pull the value of Constant.
David Hakala has perpetrated technology tutorials since 1988 in addition to committing tech journalism, documentation, Web sites, marketing collateral, and profitable prose in general. His complete rap sheet can be seen at http://www.linkedin.com/in/dhakala