Modifying Workbooks

Most of the time, you create a workbook to record information about a particular business activity, such as the number of packages that a regional distribution center handles or the average time of the last delivery on a route. Each worksheet within that workbook should thus represent a subdivision of that activity. To display a particular worksheet, just click the worksheet’s tab on the tab bar (just below the grid of cells).

In the case of Consolidated Messenger, the workbook used to track daily package volumes could have a separate worksheet for each regional distribution center. New Excel 2007 workbooks contain three worksheets; because Consolidated Messenger uses nine regional distribution centers, you need to create six new ones. To create a new worksheet, click the Insert Worksheet button at the right edge of the tab bar.

When you create a worksheet, Excel 2007 assigns it a generic name such as Sheet4, Sheet5, or Sheet6. After you decide what type of data you want to store on a worksheet, you should change the default worksheet names to something more descriptive. For example, you could change the name of Sheet1 in the regional distribution center tracking workbook to Northeast. When you want to change a worksheet’s name, double-click the worksheet’s tab on the tab bar to highlight the worksheet name, type the new name, and press .

Another way to work with more than one workbook is to copy a worksheet from another workbook to the current workbook. One circumstance in which you might consider copying worksheets to the current workbook is if you have a list of your current employees in another workbook. You can copy worksheets from another workbook by right-clicking the tab of the sheet you want to copy and, from the shortcut menu that appears, clicking Move or Copy to display the Move Or Copy dialog box.

Tip 

Selecting the Create a copy check box leaves the copied worksheet in its original workbook, whereas clearing the check box causes Excel 2007 to delete the worksheet from its original workbook.

After the worksheets are in the target workbook, you can change their order to make the data easier to locate within the workbook. To change a worksheet’s location in the workbook, you drag its sheet tab to the desired location on the tab bar. If you want a worksheet to stand out in a workbook, you can right-click its sheet tab and use the menu that appears to change the tab’s color. At the other end of the spectrum, you can hide the active worksheet by right-clicking the worksheet’s tab on the tab bar and clicking Hide on the context menu that appears. When you want Excel 2007 to redisplay the worksheet, right-click any visible sheet tab and click Unhide. In the Unhide dialog box, click the sheet you want to display and click OK.

Note 

If you copy a worksheet to another workbook, and the destination workbook has the same theme applied as the active workbook, the worksheet retains its tab color. If the destination workbook has another theme applied, the worksheet’s tab color changes to reflect that theme.

If you determine that you no longer need a particular worksheet, such as one you created to store some figures temporarily, you can delete the worksheet quickly. To do so, right-click its sheet tab and then click Delete.

In this exercise, you will insert and rename a worksheet, change a worksheet’s position in a workbook, hide and unhide a worksheet, copy a worksheet to another workbook, change a worksheet’s tab color, and delete a worksheet.

Use the Exception Summary workbook in the practice file folder for this topic. This practice file is located in the Creating folder under SBS_Office2007.

Open the Exception Summary workbook.

1. On the tab bar, click the Insert Worksheet button.

A new worksheet appears.

2. Right-click the new worksheet’s sheet tab and click Rename.

Excel 2007 highlights the new worksheet’s name.

3. Type 2007 and press .

4. On the tab bar, right-click the Sheet1 sheet tab and click Rename.

5. Type 2006 and press .

6. Right-click the 2006 sheet tab, point to Tab Color, and then, in the Standard Colors section of the color palette, click a green square.

Excel 2007 changes the 2006 sheet’s tab to green.

7. On the tab bar, drag the 2007 sheet tab to the left of the Scratch Pad sheet tab.

8. Right-click the 2007 sheet tab and then click Hide.

Excel 2007 hides the 2007 worksheet.

9. Right-click the 2006 sheet tab and then click Move or Copy.

The Move or Copy dialog box appears.

10. Click the To Book down arrow and click New Book.

11. Select the Create a copy check box.

12. Click OK.

A new workbook appears; it contains only the worksheet you copied into it.

13. On the Quick Access Toolbar, click the Save button.

The Save As dialog box appears.

14. In the File name field, type 2006 Archive and press .

Excel 2007 saves the workbook, and the Save As dialog box disappears.

15. On the View tab, click the Switch Windows button, and then click Exception Summary.

The Exception Summary workbook appears.

16. On the tab bar, right-click the Scratch Pad sheet tab and click Delete.

The Scratch Pad worksheet disappears.

17. Right-click the 2006 sheet tab and then click Unhide.

The Unhide dialog box appears.

18. Click 2007 and then click OK.

The Unhide dialog box disappears, and the 2007 worksheet appears in the workbook.

Close the Exception Summary workbook.