[Group]is no longer displayed in the title bar. Excel applies the formatting to the selected range across the selected sheets.
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 iden- tical worksheets because any labels, data, formatting, or formulas you enter are automatically added to the same cells in all the grouped worksheets.
When Excel is in Group mode, some commands are “grayed out” and can’t be used. In the pre- ceding example, you can’t convert all these ranges to tables by choosing Insert➪Tables➪ Table.
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 indi- vidual 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.
To select all sheets in a workbook, right-click any sheet tab and choose Select All Sheets from the shortcut menu.
TIP TIP
NOTE NOTE
Selecting special types of cells
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.
If you bring up the Go To Special dialog box with only one cell selected, Excel bases its selec- tion on the entire used area of the worksheet. Otherwise, the selection is based on the selected range.
Table 5.1 offers a description of the options available in the Go To Special dialog box. Some of the options are very useful.
TABLE 5.1
Go To Special Options
Option What It Does
Comments Selects only the cells that contain a cell comment.
Constants Selects all nonempty cells that don’t contain formulas. Use the check boxes under the Formulas option to choose which types of nonformula cells to include.
Formulas Selects cells that contain formulas. Qualify this by selecting the type of result: numbers, text, logical values (TRUEor FALSE), or errors.
Blanks Selects all empty cells.
TIP TIP
Option What It Does
Current Region Selects a rectangular range of cells around the active cell. This range is determined by surrounding blank rows and columns. You can also the use Ctrl+Shift+* shortcut key combination.
Current Array Selects the entire array. See Chapter 17 for more information about arrays.
Objects Selects all graphic objects on the worksheet.
Row Differences Analyzes the selection and selects cells that are different from other cells in each row.
Column Differences Analyzes the selection and selects the cells that are different from other cells in each column.
Precedents Selects cells that are referred to in the formulas in the active cell or selection (limited to the active sheet). You can select either direct precedents or precedents at any level.
Dependents Selects cells with formulas that refer to the active cell or selection (limited to the active sheet). You can select either direct dependents or dependents at any level.
Last Cell Selects the bottom-right cell in the worksheet that contains data or formatting.
Visible Cells Only Selects only visible cells in the selection. This option is useful when dealing with outlines or a filtered table.
Conditional Formats Selects cells that have a conditional format applied (by choosing Home➪Styles➪ Conditional Formatting).
Data Validation Selects cells that are set up for data-entry validation (by choosing Data➪Date Tools➪ Data Validation). The All option selects all such cells. The Same option selects only the cells that have the same validation rules as the active cell.
When you select an option in the Go To Special dialog box, be sure to note which suboptions become available. For example, when you select Constants, the suboptions under Formulas become available to help you further refine the results. Likewise, the suboptions under Dependents also apply to Precedents, and those under Data Validation also apply to Conditional formats.
Selecting cells by searching
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.
TIP TIP
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.
Copying or Moving Ranges
As you create a worksheet, you may find it necessary to copy or move information from one location to another. Excel makes copying or moving ranges of cells easy. Here are some common things you might do:
n Copy a cell to another cell.
n Copy a cell to a range of cells. The source cell is copied to every cell in the destination range.
n Copy a range to another range. Both ranges must be the same size.
n Move a range of cells to another location.
The primary difference between copying and moving a range is the effect of the operation on the source range. When you copy a range, the source range is unaffected. When you move a range, the contents are removed from the source range.
Copying a cell normally copies the cell’s contents, any formatting that is applied to the original cell (including conditional formatting and data validation), and the cell comment (if it has one). When you copy a cell that contains a formula, the cell references in the copied formulas are changed automatically to be relative to their new destination.
Copying or moving consists of two steps (although shortcut methods do exist):