Managing Worksheets

You can have as many worksheets in a workbook as your computer's memory will allow (probably hundreds of worksheets, depending on how much data each contains); consequently, you don't need to try to fit everything onto one worksheet. The following sections present the features you can use to organize your worksheet world.

Inserting and Deleting Worksheets

To insert a new worksheet into an existing workbook, click the Insert Worksheet tab, which you can see in Figure 6-14 on the left. The new sheet tab appears to the right of the last worksheet in the workbook. You can also quickly insert worksheets by right-clicking any sheet tab to display the shortcut menu shown in Figure 6-14 on the right. Clicking Insert on this menu opens the Insert dialog box containing other items you can insert besides blank worksheets, including templates and Excel 4.0 macro sheets.

Figure 6-14: To insert a blank worksheet, click the Insert Worksheet tab, or right-click any sheet tab to display a worksheet-focused shortcut menu.

In addition to providing a convenient method for inserting, deleting, renaming, moving, and copying worksheets, this shortcut menu contains the Select All Sheets command. As its name indicates, you use this command to select all the worksheets in a workbook, which you will need to do to perform certain functions, such as copying or formatting, on all the worksheets at once. The View Code command on this shortcut menu launches the Visual Basic Editor, showing the Code window for the current worksheet.

For more information about the Visual Basic Editor, see Chapter 26, "Recording Macros."

Note 

As you can see in the shortcut menu shown in Figure 6-14, the sheet tab shortcut menu also contains a Tab Color command. If you are a visually oriented person, you might find color-coding your worksheets to be as useful as changing the worksheet names.

You can also add multiple worksheets to a workbook at the same time. To do so, click a sheet tab, press Shift, and then click other sheet tabs to select a range of worksheets- the same number you want to insert-before clicking Insert Worksheet on the sheet tab shortcut menu. (Notice that Excel adds [Group] to the workbook title in the window title bar, indicating you have selected a group of worksheets for editing.) Excel inserts the same number of new worksheets as you selected and places them in front of the first worksheet in the selected range. Note that this does not copy the selected worksheets; it is just a way of telling Excel how many fresh, blank worksheets you want to insert at once.

For more information about group editing, see "Editing Multiple Worksheets" on page 236.

You cannot undo the insertion of a new worksheet. If you do need to delete a worksheet, right-click its sheet tab, and click Delete. If you want to delete more than one worksheet, you can hold down Shift to select a range of worksheets, or you can hold down Ctrl and select nonadjacent worksheets, before you click Delete. Be careful! You cannot retrieve a worksheet after you have deleted it.

Naming and Renaming Worksheets

Notice that Excel numbers the new worksheets based on the number of worksheets in the workbook. If your workbook contains three worksheets, the first worksheet you insert is Sheet4, the next is Sheet5, and so on. If you grow weary of seeing Sheet1, Sheet2, and so on, in your workbooks, you can give your worksheets more imaginative and helpful names by double-clicking the tab and typing a new name.

You can use up to 31 characters in your worksheet names. Nevertheless, you should remember that the name you use determines the width of the corresponding sheet tab, as shown in Figure 6-15. Therefore, you might want to keep your worksheet names concise so you can see more than a couple of sheet tabs at a time.

Figure 6-15: Double-click the sheet tab to type a new name. You might want to keep it short.

Moving and Copying Worksheets

As you might expect, Excel provides an easy way to move a worksheet from one place to another in the same workbook. In fact, all you have to do is click a sheet tab to select it and then drag it to its new location. Figure 6-16 shows this process. When you drag a worksheet, a small worksheet icon appears, and a tiny arrow indicates where the worksheet will be inserted in the tab order.

Figure 6-16: Click and drag sheet tabs to rearrange worksheets.

When you move worksheets, remember the following tips:

• If you want to move a worksheet to a location that isn't currently visible on your screen, drag past the visible tabs in either direction. The sheet tabs scroll in the direction you drag.

• You can move several worksheets at the same time. When you select several worksheets and drag, the pointer changes to look like a small stack of pages.

• You can copy worksheets using similar mouse techniques. First, select the worksheets you want to copy, and then hold down Ctrl while you drag the worksheets to the new location. When you copy a worksheet, an identical worksheet appears in the new location. Excel appends a number in parentheses to the copy's name to distinguish it from the original worksheet. For example, making a copy of Sheet1 results in a new worksheet named Sheet1 (2).

• You can move or copy nonadjacent worksheets at the same time by pressing Ctrl while you click to select the sheet tabs. Before dragging, release the Ctrl key to move the selected worksheets, or keep holding it down to create copies.

• You can click Move Or Copy on the sheet tab shortcut menu to handle similar worksheet management functions, including moving and copying worksheets between workbooks.

Dragging Worksheets Between Workbooks

You can move and copy worksheets between workbooks by dragging. You use the same methods to move and copy that you use for worksheets in the same workbook. For example, with two workbooks arranged horizontally in the workspace, you can move a worksheet from one to the other by dragging it to the new location in the other workbook:

Note that you must arrange the two workbooks together on your screen to allow this to work. To do so, click the View tab on the Ribbon, then click the Arrange All button in the Window group, and finally select an arrangement option.