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 next To Do exercise, you set up and name some ranges in your Sales 1st Qtr workbook. The workbook should be open and visible on the screen before you begin.

To Do: Set Range Names
  1. Select cells A4 through A8 on the Sales 1st Qtr workbook. These cells make up the sales 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 4.6. Excel has assigned the range name to the text shown in the first cell.

    Figure 4.6. 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 March (B3:D3). Click inside the Name box and type Months. When you're through, your screen should look like the example in Figure 4.7.

    Figure 4.7. 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.

  8. The Go To dialog box contains a listing of all the named ranges within the worksheet, both by coordinates and assigned names. Click Months and click OK.

  9. The Months range is selected. Deselect the cells by pressing the right arrow key.

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. Then you can delete the old range name by selecting the name in the Names In Workbook box, and clicking 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 during Hour 5, "Letting Excel Do the Math."

    Part I: Excel Basics