Working with Worksheet Ranges

A range is a group of cells. A selected group of cells composes a range. A range is always rectangular, and it might be a single cell , a row, a column, or several adjacent rows and columns . The cells within a range are always contiguous, but you can select multiple ranges at the same time. You can perform various operations on ranges, such as moving and copying. If, for example, you want to format a row of totals in some way, you first select the range that includes the totals and then apply the format to that range.

Figure 7.3 shows three ranges on a worksheet. You can describe a range by the cell reference of the upper-left cell of the range (the anchor point) and the cell reference of the lower-right cell of the range. As you can see from Figure 7.3, multiple-celled ranges are designated by listing the anchor point , followed by a colon (:), followed by the range's lower-right cell reference. Therefore, the range that begins at B3 and ends at F4 has the range of B3:F4. To select more than one range, in case you want to apply formatting to different areas of your worksheet at once, hold Ctrl while selecting the ranges.

Figure 7.3. Three ranges appear on this worksheet.

In Excel, you often work with ranges. One of the ways to make your worksheets more manageable is to name your ranges. Range names are far easier to remember than range references. You might assign the name Titles to your column titles, for example, Months to your column of month names , and so on.

To Do: Name a Range

To name a range, perform these steps:

1. Select the cells that you want to include in the named range.

2. Click the name box at the left of the formula bar (the text box that displays cell references).

3. Type the range name. The name can be as long as 255 characters , and the first character must be a letter or the underscore character. The rest of the name can contain letters , numbers , a period, and the underscore character but no spaces or other special characters such as a question mark. The name cannot be the same as a possible cell reference, so R2D2 would not count as a valid range name.

4. Press Enter. When you subsequently select the range, you will see that Excel displays the range name rather than the range reference in the name box.

Range names are easier to remember than range references. If you create a payroll worksheet and assign the names GrossPay, NetPay, HoursWorked, TaxRate, and PayRate to the ranges holding that data, for example, you never again have to type the range references. When you want to move or copy one of the ranges or use the range as a formula, just refer to the range name and let Excel figure out the correct references. You learn how to use ranges (including range names) in formulas in the section called "Using Formulas."

Here's an even better reason to name ranges: If you move a range, Excel moves the name with the cells! If you track range references and not names, you often must track down the latest references when you refer to the range. By naming ranges, you never have to worry about keeping track of references because the names won't change even if the references do.

Use meaningful names. Although AAA works as a name for a column of net sales figures, Net Sales (not pw ) makes a lot more sense and is easier to remember.

If you create a large worksheet and you need to return to a named range to make some changes, click the name box's drop-down list arrow and select the name. Excel instantly displays and selects that range for you. Moving around Excel is much simpler once you set up a set of named ranges.