Managing Worksheets | Running Microsoft Office 2000

Workbooks help you organize the reports, ledgers, tables, and forms you use every day. In the first electronic spreadsheets, users typically created a new file for each worksheet they built. This approach worked fine for casual spreadsheet users, but experienced business users, who often worked with literally hundreds of worksheets, were soon swamped with files and folders. (If this sounds like you now, you'll like this section!)

Excel now provides the ability to create default workbooks containing up to 255 worksheets. To smooth out the file management problem, all the worksheets in a workbook are now stored in one file. Although you're not actually required to place more than one worksheet in a workbook, this organizational feature gives you the option of collecting similar worksheets in one place. For example, you could store all the worksheets related to product development costs (research, manufacturing, marketing, packaging, and so on) in one workbook entitled 2001 Development Costs.

In the first part of this chapter, you'll learn the basic skills needed to manage worksheets in workbooks. You'll learn how to:

Switch between worksheets in a workbook

Name your worksheets

Delete worksheets from a workbook

Add worksheets to a workbook

Change the order of worksheets in a workbook

Switching Between Worksheets

By default, each new Excel workbook contains three identical worksheets, named Sheet1, Sheet2, and Sheet3. Each worksheet is identified by a worksheet tab at the bottom of the worksheet window, as shown in Figure 18-1. To switch between worksheets, you click the worksheet tab you want to display, and it appears as the active worksheet in the workplace. To the left of the worksheet tabs are the tab scroll buttons, which you can use to display worksheet tabs not currently visible. Clicking the outside navigation arrows displays the first and last tabs in the workbook, and clicking the inside arrows displays hidden tabs to the left and right. To switch between worksheets in a workbook, follow the steps in Figure 18-1.

click to view at full size.

Figure 18-1. Switching between worksheets.

Naming Worksheets

The default names for worksheets— Sheet1, Sheet2, and so on— are just placeholders for more useful and intuitive names that you devise. You can name or rename a worksheet at any time by double-clicking the worksheet tab to select the title and then typing a new name. Figure 18-2 shows the two-step process. (To cancel your edit, press the Escape key.) You can use up to 31 characters in your worksheet names (including spaces), but remember that the more characters you use for the name, the less room you leave for other worksheet tabs. It's a good idea to strike a balance between meaningful and brief names.

You can also use a menu command to rename the worksheet that is currently visible: Choose Sheet from the Format menu, choose Rename from the submenu, and then type the new worksheet name and press Enter.

Deleting Worksheets

While each empty worksheet in a workbook only takes up about 500 bytes of disk space, if you don't plan to use all the worksheets in a workbook, you can delete the unused worksheets to save space.

Figure 18-2. Renaming a worksheet tab.

(You can always add new worksheets later.) To delete a worksheet, display it in the workplace window, and then choose Delete Sheet from the Edit menu.

WARNING
Once you delete a worksheet— even if it contains several rows and columns of data— the worksheet will be permanently erased and you won't be able to undo the command.

To delete a worksheet, follow these steps:

In the workplace window, display the worksheet you want to delete by selecting its tab.

To select multiple sheets, hold down Ctrl and click additional tabs, or hold down Shift to select a tab and all the tabs in between.

Choose Delete Sheet from the Edit menu. Excel displays the following dialog box to confirm the deletion:

Click OK to permanently delete the worksheet.

Changing the Default Number of Worksheets
By default, Excel 2000 displays three worksheets in a workbook. However, you can adjust this number by choosing Options from the Tools menu and specifying a new number on the General tab. In the Sheets In New Workbook text box, on the General tab (shown below), specify the number of worksheets you want, either by typing a new number in the text box or by using the text box scroll arrows to increase or decrease the current value. You can specify any number from 1 through 255. This feature is quite useful, especially if you find you're usually adding extra worksheets to your workbooks.

Inserting Worksheets

Excel lets you add a new, empty worksheet to your workbook at any time by choosing the Worksheet command on the Insert menu. When you insert a new worksheet, Excel places it before the active worksheet and numbers it consecutively; that is, the eleventh worksheet will be given the name Sheet11. Figure 18-3shows how a new sheet will be inserted if your workbook contains three worksheets and if Europe is the active worksheet. After you insert a new worksheet, you can change its name, as described in "Naming Worksheets."

Figure 18-3. Excel inserts a new worksheet before the active worksheet in the workbook.

To add a new worksheet to your workbook, follow these steps:

Click the worksheet tab that you want shifted to the right to make room for a new worksheet.

Choose Worksheet from the Insert menu. A new worksheet appears in the workbook, and it becomes the active worksheet.

TIP
If you right-click a worksheet tab, a shortcut menu appears that contains commands that let you insert, delete, rename, move, or copy the active worksheet. You can use this technique to speed up many of your workbook management tasks.

Moving Worksheets

If you don't like the placement of the worksheets you have created, you can easily move them within the workbook by using a simple drag-and-drop technique. To relocate a worksheet, click the worksheet tab you want to move, and then drag it between two other worksheet tabs. (A tiny arrow appears to help you place the worksheet.) Figure 18-4 shows how the three-step process works.

TIP
Make a Duplicate Worksheet with the Ctrl Key
You can duplicate a worksheet in a workbook by holding down the Ctrl key while you drag a tab from one location to another. This procedure creates an extra copy of the worksheet in the workbook, which has identical rows and columns. The name will be copied also, and a "(2)" will be added to show that it's the second worksheet with that name. To remind you that you're duplicating a worksheet, the mouse pointer will include a plus (+) sign during the drag-and-drop operation.

click to view at full size.

Figure 18-4. To move a worksheet, drag the worksheet tab to a new location.