Using Ranges

If you know the cell address of the data you need to locate, you'll have no trouble moving to the spot. But unless you have a photographic memory, you probably won't remember the cell locations of all your important data. Even if you can spout cell locations, the cell addresses of your data change as you add and delete cells , rows, and columns .

Ranges provide a better way to organize and describe your data. A range is a rectangular block of cells that can be named with a descriptive name. Instead of trying to remember where the Utilities budget is located, you can specify the range name that holds the information to have Excel whisk to the first cell of the range. You can also use ranges for formatting and printing, and you can use range names in formulas.

A range can consist of one cell (after all, one cell is a rectangular block), or it can comprise the entire worksheet. Each range has two anchor points: the top-left and bottom-right cells. You can add or delete cells, rows, or columns within the body of the range, but you cannot make changes at either anchor point of the range.

Assigning Range Names

Instead of calling a range by its coordinates, it makes much more sense to assign it a descriptive name. Follow the rules shown here when you assign names to your ranges:

• Begin each range name with an underscore character or a letter.

• Keep your range names short, descriptive, and to the point.

• You can't use spaces or hyphens in range names so separate words with the underscore character, for example [Jan_Sales].

• You can use upper- and lowercase letters .

In the following steps, you learn to set up and name ranges in the My Budget workbook you created in the previous chapter. The workbook should be open and visible on the screen before you begin.

1. Select cells A4 through A12 on the My Budget workbook. These cells make up the budget categories.

2. When the cells appear highlighted, click the Insert menu, choose Name, and then choose Define. The Define Name dialog box appears, as shown in Figure 44.5. Excel has assigned the range name to the text shown in the first cell.

   Figure 44.5. The name Excel assigns to the range is selected so you can change the range.

   

3. Because the default name is already highlighted, you don't need to delete it to type a new name. For this example, type Categories and click OK. The box closes , and the range name is added to the workbook.

4. Excel provides an alternative way of assigning range names. Highlight the cells that hold the labels January through December (B3:M3). Click inside the Name box and type Months . When you're through, your screen should look like the example in Figure 44.6.

   Figure 44.6. The Name box holds the range name.

   

5. Deselect the highlighted cells by pressing the right-arrow key.

6. Move to the first range you defined by typing Categories in the Name box and pressing Enter. The mouse pointer moves to the first cell in the range, and the cells in the range appear highlighted.

7. Use a keystroke shortcut to view all the range names in the workbook. Press Ctrl+G to open the Go To dialog box, which contains a listing of all the named ranges within your worksheet, both by coordinates and assigned names. Click the range you want to go to and Excel takes you there. By naming appropriate ranges you can quickly navigate throughout a complex worksheet.

Editing Range Names

You're never locked into the ranges you create. You can edit the range coordinates and rename or delete the range. Click the Insert menu, choose Name, and then choose Define to open the Define Name dialog box. When you click a range, the name appears in the Names in Workbook text box. To rename a range, type a new name in the Names in Workbook box. To delete a range, click the Delete button.

The range coordinates appear in the Refers To text box and are represented by the worksheet and anchor cells of the range. A range name might look something like =Sheet1!$A$4:$A$12 . Although it might seem somewhat cryptic, each character has a distinct meaning. The column and row indicators appear after each $ character. If you want to change the range coordinates, replace only the column letters and row letters with the new coordinates for the range. Take care not to delete or change any other characters .

The dollar signs in the range name indicate absolute cell references . You'll learn about absolute cell references later in this chapter.