If you need to create a bunch of similar worksheets, Excel 2010 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.
Note
For information about moving and copying worksheets to other workbooks, see Dragging Worksheets Between Workbooks on page 163. For more information about formatting, see Chapter 9.
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 by 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:
Click the Sheet1 tab, hold down Shift, and then click the Sheet3 tab. The worksheets are now grouped, as shown in Figure 8-47. Notice that the title bar of the workbook displays [Group] after the worksheet name, and all three sheet tabs are white.
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-48.
Right-click any tab, and click Select All Sheets on the shortcut menu; then repeat step 2 to create 12 worksheets.
Rename the worksheets by double-clicking each tab and typing a new name. We used the month abbreviations Jan through Dec.
Group all 12 worksheets by selecting their tabs, as described in step 1. Now, any entries or formatting changes you make in any one of the worksheets are duplicated in all the worksheets in the group.
Enter and apply formats as shown in Figure 8-49.
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’s name and entering units and sales data.
Note
You’ll find the Pacific Brass Sales 2011.xlsx file with the other examples on the companion Web site.
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.
Tip
INSIDE OUT Group-Editing Tasks
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 and could possibly make 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 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 at the 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 in the bottom illustration. You can also drag grouped worksheets from one open workbook to another.
Use the Excel group-editing feature to perform the following actions on all member worksheets simultaneously:
Entering Text Whatever you type in one worksheet is entered in all grouped worksheets.
Printing Using the Print, Print Preview, and Page Setup commands on the File tab affects every worksheet in your group.
Viewing On the View tab, the Zoom, Workbook Views, Show, and Window options apply to all the selected worksheets. You can even click View, Hide to hide all grouped worksheets.
Formatting Any formatting you do is applied to all group members at the same time, including row height, column width, font formats, conditional formatting, and cell styles.
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, the Scale To Fit setting, 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.
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-50.
For example, to copy all the text and formatting of the worksheet shown in Figure 8-49 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:J11 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. Using the Fill, Across Worksheets command does not copy row height, column width, or view options, but you can still apply these to the group manually.
