Using Ranges to Work with Multiple Cells

Any selection of two or more cells is called a range. You can dramatically increase your productivity by using ranges to enter, edit, and format data. For example, if you highlight a range and click the Currency Style button, all the numeric entries in that range appear with dollar signs and two decimal places. Assigning a name to a range makes it easier to construct (and troubleshoot) formulas, and ranges make up the heart and soul of charts by defining data series and labels for values and categories.

The most common way to select multiple cells is to highlight a contiguous range—a rectangular region in which all cells are next to one another. But cells in a range don't have to be contiguous. You can also define a perfectly legal range by selecting individual cells or groups of cells scattered around a single worksheet.

Excel uses two addresses to identify a contiguous range, beginning with the cell in the upper-left corner and ending with the cell in the lower-right corner of the selection. A colon (:) separates the two addresses that identify the range—such as A1:G3. Commas separate the parts of a noncontiguous range, and you can mix individual cells and contiguous ranges to form a new range, as in the example A3,B4,C5:D8.

Selecting Ranges

To select a contiguous range, click the cell at any corner of the range and drag the mouse pointer to the opposite corner. To select a noncontiguous range, select the first cell or group of cells, hold down the Ctrl key, and select the next cell or group of cells. Continue holding the Ctrl key until you've selected all the cells in the range. To select an entire row or column, click the row or column heading. To select multiple rows or columns, drag the selection or hold down the Ctrl key while clicking.

To select all cells in the current worksheet, click the unlabeled Select All button in the upper-left corner of the worksheet, above the row labels and to the left of the column labels.

Moving from Cell to Cell Within a Range

To enter data into a list in heads-down mode, select the range first. As you enter data, press the Enter key to move the active cell down to the next cell within the range, or press Tab to move to the right. (Press Shift+Enter or Shift+Tab to move in the opposite direction.)

When you reach the end of a row or column, pressing Enter or Tab moves the active cell to the next column or row in the selection. When you reach the lower-right corner of the range, pressing Enter or Tab moves you back to the upper-left corner.

Entering the Same Data in Multiple Cells

Occasionally, you'll want to fill a range of cells with exactly the same data in one operation, without using the Clipboard. For example, you might want to enter zero values in cells in which you intend to enter values later; you can also use this technique to enter a formula in several cells at once. To enter a formula in several cells at once, follow these steps:

→ For a discussion of the differences between absolute and relative cell references, see "Using Cell References in Formulas".

→ For instructions on how to automatically fill in data using Excel's AutoFill feature, see "Automatically Filling in a Series of Data".

Selecting Ranges of Data with the Go To Dialog Box

The Go To dialog box is especially useful when you're designing or troubleshooting a large worksheet and you want to quickly view, edit, format, copy, or move a group of cells with common characteristics. In fact, mastering this dialog box can make it possible to do things even most Excel experts swear can't be done, such as copying a range of data while ignoring hidden rows and columns. Open the Go To dialog box as usual, and then click the Special button to display the Go To Special dialog box shown in Figure 21.3. When you select one of these options and click OK, Excel selects all the cells that match that characteristic.

When you select cells using the Go To Special dialog box, the effect is the same as if you had selected a range by pointing and clicking. If you select all constants, for example, you can use the Tab and Enter keys to move through all the cells in your worksheet that contain data, skipping over any cell that contains a formula.

The following list describes the options available in the Go To Special dialog box:

• Comments— Selects all cells that contain comments.Use this option, and then press the Tab key to move from comment to comment instead of using the Previous Comment and Next Comment buttons on the Reviewing toolbar. This option is also useful if you want to remove all comments from a worksheet. Select all comments, and then right-click any of the selected cells and choose Delete Comment from the shortcut menu.

• Constants— Selects all cells that contain text, dates, or numbers, but not formulas. The Numbers and Text check boxes let you restrict the selection by data type (although the Logicals and Errors boxes are available, using these settings when searching for constants will always return an empty set). Select just text, for example, if you want to change the formatting of row and column labels while leaving the data area alone. Select all numbers and clear their contents to turn a worksheet into a template that contains only text labels and formulas.

• Formulas— The opposite of the Constants choice, this option selects only cells that begin with an equal sign. The Numbers and Text check boxes let you restrict the selection by data type. Use the Logicals check box to find cells that contain a TRUE or FALSE value. Check the Errors box to quickly select all cells that currently display an error value, and then use the Tab key to move from cell to cell and fix the misbehaving formulas.

• Blanks— A straightforward option that searches all cells between the top of the worksheet and the last cell that contains data, selecting those that do not contain data or formatting. This option is useful when you want to enter a default value or assign a default format to these cells.

• Current Region— Selects all cells around the active cell, up to the nearest blank row and column in any direction.

• Current Array— If the active cell is within an array, this option selects the entire array.

• Objects— Choose this option to select all charts, text boxes, AutoShapes, and other graphic objects on the current worksheet. This option is particularly useful when you want to change formatting for borders and shading, or when you want to group objects.

• Row Differences— Selects cells whose contents are different from those in a comparison cell. This is a challenging option to master: You must make a selection first, and then position the active cell in the column you want to use for comparison. If you select multiple rows, Excel compares each row independently to the value in the column that contains the active cell. The example in Figure 21.4 shows what happens when you select D11:I16 and then position the active cell in column D and use the Go To Special dialog box with the Row Differences option. The highlights readily identify two rows where expenses are different each month, but it also finds one out-of-the-ordinary value in cell F14.

  Figure 21.4. Using the Row Differences command identifies values that are out of the ordinary; a cost-conscious business manager might ask why March cleaning bills are 25% higher than usual.

  

• Column Differences— Like the previous option, except it works on a column-by-column basis. This option is extremely useful for finding unexpected differences in a list. Use a calculated column that determines whether a particular set of columns is within a normal range and returns a TRUE or FALSE result, and then use this option to find all cells that are FALSE.

• Precedents— Selects all cells to which the current selection refers. Use the Direct Only and All Levels options to find only direct references or all references. This option is useful when you're trying to trace the logic of a complex worksheet by working through a series of formulas.

• Dependents— Similar to the previous option, except it selects all cells that directly or indirectly refer to the active cell or range.

• Last Cell— Jumps to the last cell on the worksheet that contains data or formatting.

• Visible Cells Only— Easily the most useful of all the options in the Go To Special dialog box. Use this type of selection to avoid the common problem of pasting more data than you expect. For example, if you copy a range of data that includes a hidden column, and then paste it into a new sheet, Excel pastes the hidden column as well. To avoid this problem, select the range you want to copy, and then use the Go To Special dialog box to select only visible cells. Copying and pasting that selection will have exactly the result you intend.

• Conditional Formats— Selects all cells that use any form of conditional formatting.Use the All option when you want to quickly find all cells that contain conditional formatting. Use the Same option if you just want to edit these options for cells that match the current cell.

• Data Validation— Similar to the previous option, except it selects cells with data validation rules.

If choosing the Last Cell option in the Go To Special dialog box causes you to jump to a blank cell far below your actual worksheet range, see "Resetting the Last Cell" in the "Troubleshooting" section at the end of this chapter.