To perform an operation on a range of cells in a worksheet, you must first select the range. For example, if you want to make the text bold for a range of cells, you must select the range and then choose Home Font Bold (or press Ctrl+B).

When you select a range, the cells appear highlighted. The exception is the active cell, which remains its normal color. Figure 5.1 shows an example of a selected range (B4:C11) in a worksheet. Cell B4, the active cell, is selected but not highlighted.

Figure 5.1. When you select a range, it appears highlighted, but the active cell within the range is not highlighted.

You can select a range in several ways:

Often, you’ll need to select an entire row or column. For example, you may want to apply the same numeric format or the same alignment options to an entire row or column. You can select entire rows and columns in much the same manner as you select ranges:

Most of the time, the ranges that you select are contiguous—a single rectangle of cells. Excel also enables you to work with noncontiguous ranges, which consist of two or more ranges (or single cells) that aren’t next to each other. Selecting noncontiguous ranges is also known as a multiple selection. If you want to apply the same formatting to cells in different areas of your worksheet, one approach is to make a multiple selection. When the appropriate cells or ranges are selected, the formatting that you select is applied to them all. Figure 5.2 shows a noncontiguous range selected in a worksheet. (Three ranges are selected.)

Figure 5.2. Excel enables you to select noncontiguous ranges.

You can select a noncontiguous range in several ways:

In addition to two-dimensional ranges on a single worksheet, ranges can extend across multiple worksheets to be three-dimensional ranges.

Suppose that you have a workbook set up to track budgets. A common approach is to use a separate worksheet for each department, making it easy to organize the data. You can click a sheet tab to view the information for a particular department.

Figure 5.3 shows a simplified example. The workbook has four sheets, named Totals, Marketing, Operations, and Manufacturing. The sheets are laid out identically. The only difference is the values. The Totals sheet contains formulas that compute the sum of the corresponding items in the three departmental worksheets.

Figure 5.3. The worksheets in this workbook are laid out identically.

When a workbook is in Group mode, any changes that you make to cells in one worksheet also apply to all the other grouped worksheets. You can use this to your advantage when you want to set up a group of identical worksheets because any labels, data, formatting, or formulas you enter are automatically added to the same cells in all the grouped worksheets.

In general, selecting a multisheet range is a simple two-step process: Select the range in one sheet and then select the worksheets to include in the range. To select a group of contiguous worksheets, you can press Shift and click the sheet tab of the last worksheet that you want to include in the selection. To select individual worksheets, press Ctrl and click the sheet tab of each worksheet that you want to select. If all the worksheets in a workbook aren’t laid out the same, you can skip the sheets that you don’t want to format. When you make the selection, the sheet tabs of the selected sheets appear with a white background, and Excel displays [Group] in the title bar.

As you use Excel, you may need to locate specific types of cells in your worksheets. For example, wouldn’t it be handy to be able to locate every cell that contains a formula—or perhaps all the cells whose value depends on the current cell? Excel provides an easy way to locate these and many other special types of cells. Simply choose Home Select & Find Go To Special to display the Go To Special dialog box, shown in Figure 5.5.

Figure 5.5. Use the Go To Special dialog box to select specific types of cells.

After you make your choice in the dialog box, Excel selects the qualifying subset of cells in the current selection. Usually, this subset of cells is a multiple selection. If no cells qualify, Excel lets you know with the message No cells were found.

Table 5.1 offers a description of the options available in the Go To Special dialog box. Some of the options are very useful.

Another way to select cells is to use Excel’s Home Editing Find & Select Find command (or press Ctrl+F), which allows you to select cells by their contents. The Find And Replace dialog box is shown in Figure 5.6. This figure shows additional options that are available when you click the Options button.

Figure 5.6. The Find And Replace dialog box, with its options displayed.

Enter the text that you’re looking for; then click Find All. The dialog box expands to display all the cells that match your search criteria. For example, Figure 5.7 shows the dialog box after Excel has located all cells that contain the text Tucson. You can click an item in the list, and the screen will scroll so that you can view the cell in context. To select all the cells in the list, first select any single item in the list. Then press Ctrl+A to select them all.

Figure 5.7. The Find And Replace dialog box, with its results listed.

Note that the Find and Replace dialog box allows you to return to the worksheet without dismissing the dialog box.

Choosing Home Clipboard Copy transfers a copy of the selected cell or range to the Windows Clipboard and the Office Clipboard. After performing the copy part of this operation, select the cell that will hold the copy and choose Home Clipboard Paste.

Rather than using Home Clipboard Paste, you can just activate the destination cell and press Enter. If you use this technique, Excel removes the copied information from the Clipboard so that it can’t be pasted again.

If you’re copying a range, you don’t need to select an entire same-sized range before you click the Paste button. You need only activate the upper-left cell in the destination range.

If you prefer, you can use the following shortcut menu commands for copying and pasting:

Rather than using Paste, you can just activate the destination cell and press Enter. If you use this technique, Excel removes the copied information from the Clipboard so that it can’t be pasted again.

The copy and paste operations also have shortcut keys associated with them:

Excel also enables you to copy or move a cell or range by dragging. Be aware, however, that dragging and dropping does not place any information on either the Windows Clipboard or the Office Clipboard.

To copy using drag-and-drop, select the cell or range that you want to copy and then press Ctrl and move the mouse to one of the selection’s borders (the mouse pointer is augmented with a small plus sign). Then, simply drag the selection to its new location while you continue to press the Ctrl key. The original selection remains behind, and Excel makes a new copy when you release the mouse button. To move a range using drag-and-drop, don’t press Ctrl while dragging the border.

Using Smart Tags When Inserting and Pasting

Some cell and range operations—specifically inserting, pasting, and filling cells by dragging—result in the display of a Smart Tag. A Smart Tag is a small square that, when clicked, presents you with options. For example, if you copy a range and then paste it to a different location, a Smart Tag appears at the lower-right of the pasted range. Click the Smart Tag, and you see the options shown in the following figure. These options enable you to specify how the data should be pasted. In this case, using the Smart Tag is an alternative to using options in the Paste Special dialog box.

Some users find these Smart Tags helpful, while others think that they’re annoying. (Count me in the latter group.) To turn off these Smart Tags, choose Office Excel Options and click the Advanced tab. Remove the check mark from the two options labeled Show Paste Options Buttons and Show Insert Options Buttons.

Often, you’ll find that you need to copy a cell to an adjacent cell or range. This type of copying is quite common when working with formulas. For example, if you’re working on a budget, you might create a formula to add the values in column B. You can use the same formula to add the values in the other columns. Rather than re-enter the formula, you can copy it to the adjacent cells.

Excel provides additional options for copying to adjacent cells. To use these commands, select the cell that you’re copying and the cells that you’re copying to. Then issue the appropriate command from the following list for one-step copying:

None of these commands places information on either the Windows Clipboard or the Office Clipboard.

You can use the copy procedures described previously to copy a cell or range to another worksheet, even if the worksheet is in a different workbook. You must, of course, activate the other worksheet before you select the location to which you want to copy.

Excel offers a quicker way to copy a cell or range and paste it to other worksheets in the same workbook. Start by selecting the range to copy. Then, press Ctrl and click the sheet tabs for the worksheets to which you want to copy the information. (Excel displays [Group] in the workbook’s title bar.) Choose Home Editing Fill Across Worksheets, and a dialog box appears to ask you what you want to copy (All, Contents, or Formats). Make your choice and then click OK. Excel copies the selected range to the selected worksheets; the new copy occupies the same cells in the selected worksheets as the original occupies in the initial worksheet.

Whenever you cut or copy information in an Office program, such as Excel, you can place the data on both the Windows Clipboard and the Office Clipboard. When you copy information to the Office Clipboard, you append the information to the Office Clipboard instead of replacing what is already there. With multiple items stored on the Office Clipboard, you can then paste the items either individually or as a group.

To use the Office Clipboard, you first need to open it. Use the dialog launcher on the bottom right of the Home Clipboard group to toggle the Clipboard task pane on and off.

After you open the Clipboard task pane, select the first cell or range that you want to copy to the Office Clipboard and copy it by using any of the preceding techniques. Repeat this process, selecting the next cell or range that you want to copy. As soon as you copy the information, the Office Clipboard task pane shows you the number of items that you’ve copied and a brief description (it will hold up to 24 items). Figure 5.8 shows the Office Clipboard with five copied items.

Figure 5.8. Use the Clipboard task pane to copy and paste multiple items.

When you’re ready to paste information, select the cell into which you want to paste information. To paste an individual item, click it in the Clipboard task pane. To paste all the items that you’ve copied, click the Paste All button.

You can clear the contents of the Office Clipboard by clicking the Clear All button.

The following items about the Office Clipboard and its functioning are worth noting:

You may not always want to copy everything from the source range to the destination range. For example, you may want to copy only the formula results rather than the formulas themselves. Or you may want to copy the number formats from one range to another without overwriting any existing data or formulas.

To control what is copied into the destination range, choose Home Clipboard Paste and use the drop-down menu shown in Figure 5.9. Options are

Figure 5.9. Excel offers several pasting options.

For maximum flexibility in what gets pasted, choose Home Clipboard Paste Paste Special to display the Paste Special dialog box (see Figure 5.10). You can also right-click and select Paste Special to display this dialog box. This dialog box has several options, which I explain in the following list.

Figure 5.10. The Paste Special dialog box.

In addition, the Paste Special dialog box enables you to perform other operations, described in the following sections.

Transposing a range

The Transpose option in the Paste Special dialog box changes the orientation of the copied range. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed. Note that you can use this check box with the other options in the Paste Special dialog box. Figure 5.11 shows an example of a horizontal range (A1:F1) that was transposed to a vertical range (A3:A8).

Figure 5.11. Transposing a range changes the orientation as the information is pasted into the worksheet.

Tip

If you click the Paste Link button in the Paste Special dialog box, you create formulas that link to the source range. As a result, the destination range automatically reflects changes in the source range.

Excel provides several different methods that you can use to create range names. Before you begin, however, you should be aware of some important rules about what is acceptable:

Excel also uses a few names internally for its own use. Although you can create names that override Excel’s internal names, you should avoid doing so. To be on the safe side, avoid using the following for names: Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title.

Using the New Name dialog box

To create a range name, start by selecting the cell or range that you want to name. Then, choose Formulas Defined Names Define Name. Excel displays the New Name dialog box, shown in Figure 5.12. Note that this is a resizable dialog box. Click and drag a border to change the dimensions.

Figure 5.12. Create names for cells or ranges by using the New Name dialog box.

Type a name in the box labeled Name (or use the name that Excel proposes, if any). The selected cell or range address appears in the box labeled Refers To. Use the Scope drop-down to indicate the scope for the name. The scope indicates where the name will be valid, and it’s either the entire workbook, or a particular sheet. If you like, you can add a comment that describes the named range or cell. Click OK to add the name to your workbook and close the dialog box.

Using the Create Names From Selection dialog box

You may have a worksheet that contains text that you want to use for names for adjacent cells or ranges. For example, you may want to use the text in column A to create names for the corresponding values in column B. Excel makes this task easy to do.

To create names by using adjacent text, start by selecting the name text and the cells that you want to name. (These items can be individual cells or ranges of cells.) The names must be adjacent to the cells that you’re naming. (A multiple selection is allowed.) Then, choose Formulas Defined Names Create From Selection. Excel displays the Create Names From Selection dialog box, shown in Figure 5.13. The check marks in this dialog box are based on Excel’s analysis of the selected range. For example, if Excel finds text in the first row of the selection, it proposes that you create names based on the top row. If Excel didn’t guess correctly, you can change the check boxes. Click OK, and Excel creates the names.

Figure 5.13. Use the Create Names From Selection dialog box to name cells using labels that appear in the worksheet.

Note

If the text contained in a cell would result in an invalid name, Excel modifies the name to make it valid. For example, if a cell contains the text Net Income (which is invalid for a name because it contains a space), Excel converts the space to an underscore character. If Excel encounters a value or a numeric formula where text should be, however, it doesn’t convert it to a valid name. It simply doesn’t create a name—and does not inform you of that fact.

Caution

If the upper-left cell of the selection contains text and you choose the Top Row and Left Column options, Excel uses that text for the name of the entire data excluding the top row and left column. So, before you accept the names that Excel creates, take a minute to make sure that they refer to the correct ranges. If Excel creates a names that is incorrect, you can delete or modify it by using the Name Manager (described next).

A workbook can have any number of names. If you have many names, you should know about the Name Manager, shown in Figure 5.14.

Figure 5.14. The Name Manager is new in Excel 2007.

The Name Manager appears when you choose Formulas Defined Names Name Manager (or press Ctrl+F3). The Name Manager has the following features:

If you delete the rows or columns that contain named cells or ranges, the names contain an invalid reference. For example, if cell A1 on Sheet1 is named Interest and you delete row 1 or column A, the name Interest then refers to =Sheet1!#REF! (that is, to an erroneous reference). If you use Interest in a formula, the formula displays #REF.

If you don’t like the default look of cell comments, use the Home Font and Home Alignment groups to make changes to the comment’s appearance.

For even more formatting options, right-click the comment’s border and choose Format Comment from the shortcut menu. Excel responds by displaying the Format Comment dialog box, which allows you to change many aspects of its appearance.

Figure 5.16. This comment contains a graphic image.

Normally, a cell comment is rectangular, but they don’t have to be. To change the shape of a cell comment, add a command to your Quick Access Toolbar (QAT):

After performing these steps, your QAT has a new Change Shape icon.

To change the shape of a comment, make sure that it’s visible (right-click the cell and select Show/Hide Comments). Then click the comment’s border to select it as a Shape (or, Ctrl+click the comment to select it as a Shape). Click the Change Shape button on the QAT and choose a new shape for the comment. Figure 5.17 shows a cell comments with a nonstandard shape.

Figure 5.17. Cell comments don’t have to be rectangles.

To read all of the comments in a workbook, choose Review Comments Next. Click this command repeatedly to cycle through all the comments in a workbook. Choose Review Comments Previous to view the comments in reverse order.

If you want all cell comments to be visible (regardless of the location of the cell pointer), choose Review Comments Show All Comments. This command is a toggle; select it again to hide all cell comments. To toggle the display of an individual comments, select its cell and then choose Review Comments Show/Hide Comment.

To edit a comment, activate the cell, right-click, and then choose Edit Comment from the shortcut menu. When you’ve made your changes, click any cell.

To delete a cell comment, activate the cell that contains the comment and then choose Review Comments Delete. Or, right-click and then choose Delete Comment from the shortcut menu.