Editing Multiple Worksheets

If you need to create a bunch of similar worksheets, Office Excel 2007 helps you save some clicks and keystrokes. For example, if the workbook you're creating calls for a separate worksheet for each month, division, product, or whatever, you can save a lot of time by creating them all at once using the techniques described in this section and then tweak each worksheet as needed.

For information about moving and copying worksheets to other workbooks, see "Dragging Worksheets Between Workbooks" on page 150. For more information about formatting, see Chapter 9, "Worksheet Formatting Techniques."

Grouping Worksheets for Editing

You can group any number of worksheets in a workbook and then add, edit, or format data in all the worksheets in the group at the same time. Use this feature when you're creating or modifying a set of worksheets that are similar in purpose and structure-a set of monthly reports or departmental budgets, for example.

You can select and group worksheets using one of these methods:

• Click the sheet tab of the first worksheet in a range of adjacent worksheets you want to work on, hold down Shift, and click the tab of the last worksheet in the range.

• Click the tab of any of the worksheets you want to work on, hold down Ctrl, and click the tabs of each worksheet you want to include in the group, whether or not the worksheets are adjacent.

• Right-click a sheet tab, and click Select All Sheets on the shortcut menu.

Let's go through the procedure of creating a workbook containing a separate worksheet for each month, starting with a blank workbook:

1. Click the Sheet1 tab, hold down Shift, and then click the Sheet3 tab. The worksheets are now grouped, as shown in Figure 8-48. Notice that the title bar of the workbook displays [Group] after the worksheet name, and all three sheet tabs are white.

2. Right-click any of the selected tabs, and click Move Or Copy on the shortcut menu. In the Before Sheet list in the dialog box that appears, select Move To End. Select the Create A Copy check box, and then click OK. Excel creates three new worksheets, as shown in Figure 8-49.

3. Right-click any tab, and click Select All Sheets on the shortcut menu; then repeat step 2 to create 12 worksheets.

   Note 

   The easiest way to create new, blank worksheets is to click the Insert Worksheet tab, the last sheet tab on the right, which creates a new numbered worksheet. The technique described here is especially useful when you want to create copies of existing worksheets containing data.

4. Rename the worksheets by double-clicking each tab and typing a new name. We used the month abbreviations Jan through Dec.

5. Group all 12 worksheets by selecting their tabs, as shown in step 1. Now, any entries or formatting changes you make in any one of the worksheets is duplicated in all the worksheets in the group.

6. Enter and apply formats as shown in Figure 8-50.

7. When you finish all the entries, common formulas, and formatting, click any worksheet to ungroup, and then make edits to individual worksheets, such as adding each month name and entering units and sales data.

Figure 8-48: The three worksheets are grouped.

Figure 8-49: Copying a group of worksheets creates the same number of new worksheets.

Figure 8-50: With group editing, Excel applies all edits and formats to all the worksheets.

On the CD You'll find the Pacific Brass Sales. xlsx file in the Sample Files section of the companion CD.

You can add formatting, formulas, or any other data to the active worksheet in a group, and Excel modifies all member worksheets simultaneously. Excel transfers any changes you make to column width, row height, view options such as gridlines and scroll bars, and even the location of the active cell to the group.

Inside Out-Group-Editing Tricks

When you group several worksheets and then click one of the worksheets in the group with the intention of editing it individually, you're still in group-editing mode, possibly making inadvertent changes to all your grouped worksheets. Keep your eye on the tabs-when they are white, they are all editable. Getting out of group-editing mode works differently, depending on how many worksheets you have grouped.

If you have grouped all the worksheets in a workbook, clicking any tab except that of the active worksheet exits group-editing mode and removes the [Group] indicator from the title bar of the workbook. However, if you have selected some but not all of the worksheets in a workbook, clicking any other grouped sheet tab makes that worksheet active but does not exit group-editing mode. In this case, click any tab outside the group to exit group-editing mode.

Besides using the Move Or Copy command to rearrange and duplicate worksheets in a workbook, you can also use the mouse to perform the same actions directly. Select a group and drag to move it to a different location. The cursor changes to include a little pad of paper, as shown here (top):

To copy a group of worksheets, drag the group, and then press Ctrl before releasing the mouse button. The little pad of paper appears with a plus sign inside it, as shown here (bottom). You can also drag grouped worksheets from one open workbook to another.

What You Can Do in Group-Editing Mode

Use the Excel group-editing feature to perform the following actions on all member worksheets simultaneously:

• Entering Text This duplicates what you type in one worksheet in all grouped worksheets.

• Printing Using the Print, Print Preview, and Page Setup commands on the Microsoft Office Button affects every worksheet in your group.

• Viewing On the View tab, the Zoom, Workbook Views, Show/Hide, and Window options apply to all the selected worksheets.

• Formatting This applies any changes to all group members at the same time. Changing row height and column width and applying font formats, Conditional Formatting, and Style options affect all worksheets. You can even click View, Hide to hide all grouped worksheets.

• Editing This applies all editing actions including entering formulas, inserting rows and columns, and using Find and Replace to all worksheets in the group.

• Page Layout On the Page Layout tab, changes made to Themes, Page Setup options, Scale To Fit, and most Sheet Options apply to the group.

• Inserting Headers and Footers Using the Header & Footer command on the Insert tab applies to every worksheet in the group.

Filling a Group

If you aren't starting from scratch but want to duplicate existing data in one worksheet to a number of other worksheets in a workbook, you can click the Across Worksheets command, located on the Fill menu in the Editing group on the Home tab. This option is available only if you first establish a group. When you click this option, Excel displays the Fill Across Worksheets dialog box shown in Figure 8-51.

Figure 8-51: Use the Fill Across Worksheets dialog box to copy selected data to all the worksheets in a group.

For example, to copy all the text and formatting of the worksheet shown in Figure 8-50 to all the other grouped worksheets in the workbook (if we hadn't already done that using group-editing mode), we could select the range A1:J110 and then click Fill, Across Worksheets. With the All option selected, Excel transfers all text, formulas, and formatting to every other worksheet in the group. If you select the Contents option, Excel duplicates only text and values; the Formatting option predictably duplicates only the formats. Filling across worksheets does not apply row height, column width, or view options.