Naming Cells and Ranges


Naming Cells and Ranges

Many of the calculations that you might want to perform on this worksheet will use the total you have calculated in cell A1. You could include a copy of the SUM function now in cell A1 in these other calculations, or you could simply reference cell A1. The latter method seems quick and simple, but what if you subsequently move the formula in A1 to another location? Excel gives you a way to reference this formula no matter where on the worksheet you move it. You can assign cell A1 a name, and then use the name in any calculations that involve the total.

Assigning a Name to a Cell

You can easily assign a name to a cell or range in a worksheet, and then use that name in formulas. Here s how to assign a name to a cell:

  1. Select cell A1 , and click the name box located to the left of the formula bar.

    Excel highlights the reference in the name box.

  2. Type Total , and press Enter .

    The name box now contains the cell s name instead of its reference. You can use either designation in formulas.

Referencing a Named Cell in a Formula

You can use cell names in formulas or functions. To see how Excel treats the names you assign, try this:

  1. Click cell D16 .

    This cell currently contains the SUM function you inserted earlier in the chapter.

  2. Type =Total, and press Enter .

    The worksheet does not appear to change, but now instead of two SUM functions, the worksheet contains only one. You have told Excel to assign the value of the cell named Total , which contains the SUM function, to cell D16.

Assigning a Name to a Range

You can also assign names to ranges. Let s use a different method to assign the name Amount to the cells containing amounts in column D:

  1. Select D4:D15, and then click Name and then Define on the Insert menu.

    Excel displays the dialog box shown in this graphic:

    click to expand

    Notice that the Names in workbook list box contains Total, the name you assigned to cell A1 earlier. The range reference in the Refers to text box, Sheet1!$D$4:$D$15, is an absolute reference to the selected range on Sheet1 of the current workbook.

  2. In the Names in workbook text box, replace Excel s suggested name with Amount , and press Enter .

Information about  

Absolute references, page 140

Referencing a Named Range in a Formula

Just as you can use named cells in formulas, you can use named ranges. Let s replace the range reference in the SUM function in cell A1 with the new range name. Follow these steps:

  1. Click cell A1 to select it and display its contents in the formula bar.

  2. Drag through the D4:D15 reference in the formula bar to highlight it.

  3. On the Insert menu, click Name , and then click Paste .

    Excel displays the dialog box shown in this graphic:

  4. Click Amount , and click OK .

    Excel replaces the range reference with the name that is assigned to the range, and the formula bar now reads =SUM(Amount).

  5. Click the Enter button.

    The total in cell A1 remains the same as before, even though you ve changed the formula.

  6. Click the Save button to save your work.

    From now on, we won t give you specific instructions to save your work, but you should get in the habit of saving often, perhaps after working through each example.

start sidebar
Naming conventions

Certain rules apply when you name cells or ranges. Names can include capital letters . Although you can use a number within the name, you must start the name with a letter, an underscore, or a backslash. Spaces are not allowed within the name, so you should use underscore characters to represent spaces. For example, you cannot use Totals 2002 as a name, but you can use Totals_2002. Cell references cannot be used as names, and you also cannot have two names that are distinguished only by their capitalization.

end sidebar
 



Online Traning Solutions - Quick Course in Microsoft Office XP
Online Traning Solutions - Quick Course in Microsoft Office XP
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 116

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net