Naming Ranges

Up to this point, when you have created formulas or functions or formatted cells in a worksheet, you have specified cells and cell ranges using the cell addresses. You can also name a cell or range of cells. You could select a range of values and assign that range a name. For example, you could select a range of cells that includes your expenses and name that range EXPENSES. You can then name a range of cells that includes your income and name that range INCOME. It would be very simple to then create a formula that subtracts your expenses from your income using the range names that you created. The formula would be written as follows :

 =SUM(INCOME)-SUM(EXPENSES) 

You are telling Excel to add the INCOME range and add the EXPENSES range. The formula then subtracts the total EXPENSES from the total INCOME. Note that the SUM function is used along with simple subtraction in this formula to provide the desired results.

Using range names in formulas and functions can definitely make your life easier. Range names are very useful when you create formulas or functions that pull information from more than one worksheet in a workbook or different workbooks. You can even use a range name to create a chart (you learn about charts in Lesson 15, "Creating Charts ").

Follow these steps to name a range:

  1. Select the range you want to name (the cells must be located on the same worksheet). If you want to name a single cell, simply select that cell.

  2. Select the Insert menu, point at Name , and then select Define . The Define Name dialog box appears (see Figure 11.2).

    Figure 11.2. Use the Define Name dialog box to name a cell range.

    graphics/68fig02.jpg

  3. Type the name for the range in the box at the top of the dialog box. You can use up to 255 characters , and valid range names can include letters , numbers , periods, and underlines, but no spaces.

  4. Click the Add button to name the range. The name is added to the list of range names.

  5. Click OK .

graphics/tip_icon.gif

Selecting a Different Range You can change the selected range from the Define Name dialog box. Click the Shrink button at the bottom of the dialog box, and then select the range on the worksheet. To return to the dialog box, click the Expand button on the Define Name dialog box.


You can also use the Define Name dialog box to delete any unwanted range names. Select Insert , point at Name , and then select Define . Select an unwanted range name from the list and click the Delete button. To close the dialog box, click OK .

graphics/tip_icon.gif

Quickly Create a Range Name You can also create a range name by typing it into the Name box on the Formula bar (this box normally shows the location of the selected range or cell). Select the cell range, click in the Name box, and type the name for the range. Then press Enter .




Microsoft Office 2003 All-in-One
Microsoft Office 2003 All-in-One
ISBN: B005HKSHB2
EAN: N/A
Year: 2002
Pages: 660
Authors: Joe Habraken

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