• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Working with Files > Workbook Sheets

Workbook Sheets

Excel workbook files can include up to 255 individual sheets, which are like pages in the workbook. Each workbook, by default, includes three sheets named Sheet1 through Sheet3.

In Excel, there are two kinds of sheets:

  • A worksheet (Figure 1) is for entering information and performing calculations. You can also embed charts in a worksheet.

    Figure 1. Here's a worksheet.

  • A chart sheet (Figure 2) is for creating charts that aren't embedded in a worksheet.

    Figure 2. And here's a chart sheet in the same workbook file.

Tips

  • Use the multiple sheet capabilities of workbook files to keep sheets for the same project together. This is an excellent way to organize related work.

  • I tell you about worksheets throughout this book and about charts and chart sheets in Chapter 8.


To switch between sheets

Click the sheet tab at the bottom of the workbook window (Figure 3) for the sheet you want.

Figure 3. Use sheet tabs to move from sheet to sheet in a workbook.


or

Press or to scroll through all of the sheets in a workbook, one at a time.

Tips

  • If the sheet tab for the sheet you want is not displayed, use the tab scrolling buttons (Figure 4) to scroll through the sheet tabs.

    Figure 4. Use the sheet tab scrolling buttons to view sheet tabs that are not displayed.

  • To display more or fewer sheet tabs, drag the tab split box (Figure 5) to increase or decrease the size of the sheet tab area. As you change the size of the sheet tab area, you'll also change the size of the horizontal scroll bar at the bottom of the workbook window.

    Figure 5. Drag the tab split box to change the size of the sheet tab area and display more or fewer sheet tabs.


To select multiple sheets

1.
Click the sheet tab for the first sheet you want to select.

2.
Hold down and click the sheet tab(s) for the other sheet(s) you want to select. The sheet tabs for each sheet you include in the selection turn white (Figure 6).

Figure 6. To select multiple sheets, hold down while clicking each sheet tab.


Tips

  • To select multiple adjacent sheets, click the sheet tab for the first sheet, then hold down and click on the sheet tab for the last sheet you want to select. All sheet tabs in between also become selected.

  • Selecting multiple sheets makes it quick and easy to print, delete, edit, format, or perform other tasks with more than one sheet at a time.


To insert a worksheet

1.
Click the tab for the sheet you want to insert a new sheet before (Figure 7).

Figure 7. Begin by selecting the sheet you want the new sheet to be inserted before.


2.
Choose Insert > Worksheet (Figure 8).

Figure 8. The Insert menu.


A new worksheet is inserted before the one you originally selected (Figure 9).

Figure 9. An inserted worksheet.


Tip

  • By default, the new worksheet is named with the word Sheet followed by a number. I tell you how to rename sheets on the next page.


To insert a chart sheet

1.
Click the tab for the sheet you want to insert a new sheet before (Figure 7).

2.
Choose Insert > Chart (Figure 8).

3.
The Chart Wizard - Step 1 of 4 dialog appears (Figure 10). Follow the steps in the Chart Wizard (as discussed in Chapter 8) to create a chart.

Figure 10. The first step of the Chart Wizard.


4.
In the Chart Wizard - Step 4 of 4 dialog (Figure 11), select the As new sheet radio button and enter a name for the sheet beside it. Then click Finish.

Figure 11. The last step of the Chart Wizard enables you to insert the chart as a new sheet.


A new chart sheet is inserted before the worksheet you originally selected (Figure 12).

Figure 12. An inserted chart sheet.


To delete a sheet

1.
Click on the sheet tab for the sheet you want to delete to select it.

2.
Choose Edit > Delete Sheet (Figure 13).

Figure 13. The Edit menu.


3.
A warning dialog (Figure 14) or Office Assistant balloon appears. Click OK to confirm that you want to delete the sheet.

Figure 14. When you delete a sheet, Excel warns you that it will be permanently deleted.


Tips

  • The appearance of the Edit menu (Figure 13) varies depending on the type of sheet.

  • As the dialog in Figure 14 warns, sheets are permanently deleted. That means even the Undo command won't get a deleted sheet back.

  • If another cell in the workbook contains a reference to a cell on the sheet you've deleted, that cell will display a #REF! error message. The formula in that cell will have to be rewritten.


To rename a sheet

1.
Click on the sheet tab for the sheet you want to rename to make it active.

2.
Choose Format > Sheet > Rename (Figure 15) or double-click the sheet tab.

Figure 15. The Sheet submenu under the Format menu.


3.
The sheet tab becomes highlighted (Figure 16). Enter a new name for the sheet (Figure 17) and press or to save it.

Figure 16. Double-click the sheet tab to select its name.


Figure 17. Enter a new name for the sheet.


The sheet tab displays the new name (Figure 18).

Figure 18. Press to save the new name.


Tips

  • The appearance of the Format menu (Figure 15) varies depending on the type of sheet.

  • Sheet names can be up to 31 characters long and can contain any character except a colon (:) or mathematical operator.


To hide a sheet

1.
Select the sheet(s) you want to hide (Figure 18).

2.
Choose Format > Sheet > Hide (Figure 15).

The sheet and its sheet tab disappear (Figure 19), just as if the sheet were deleted! But don't worry—the sheet still exists in the workbook file.

Figure 19. When you hide a sheet, its tab disappears.


Tips

  • You cannot hide a sheet if it is the only sheet in a workbook.

  • Don't confuse this command with the Hide command under the Window menu. These commands do two different things! I tell you about the Window menu's Hide command later in this chapter.


To unhide a sheet

1.
Choose Format > Sheet > Unhide (Figure 20).

Figure 20. When a sheet is hidden, the Unhide command is available on the Sheet submenu.


2.
In the Unhide dialog that appears (Figure 21), select the sheet you want to unhide.

Figure 21. The Unhide dialog lists all hidden sheets.


3.
Click OK.

The sheet and its sheet tab reappear.

Tips

  • You can only unhide one sheet at a time.

  • If the Unhide command is gray (Figure 15), no sheets are hidden.

  • Don't confuse this command with the Unhide command under the Window menu. I tell you about the Window menu's Unhide command later in this chapter.


To move or copy a sheet

1.
Select the tabs for the sheet(s) you want to move or copy.

2.
Choose Edit > Move or Copy Sheet (Figure 13). The Move or Copy dialog appears (Figure 22).

Figure 22. Use the Move or Copy dialog to pick a destination for the selected sheet(s) and tell Excel to copy them rather than move them.


3.
Use the To book pop-up menu (Figure 23) to choose the workbook you want to move or copy the sheet(s) to.

Figure 23. The To book pop-up menu lists all of the workbooks that are currently open.


4.
Use the Before sheet scrolling list to choose the sheet you want the sheet(s) to be copied before.

5.
If you want to copy or duplicate the sheet rather than move it, turn on the Create a copy check box.

6.
Click OK.

Tips

  • To move or copy sheets to another workbook, make sure that workbook is open (but not active) before you choose the Move or Copy Sheet command. Otherwise, it will not be listed in the To book pop-up menu (Figure 23).

  • If you choose (new book) from the To book pop-up menu (Figure 23), Excel creates a brand new, empty workbook file and places the selected sheet(s) into it.

  • You can use the Move or Copy Sheet command to change the order of sheets in a workbook. Just make sure the current workbook is selected in the To book pop-up menu (Figure 23). Then select the appropriate sheet from the Before sheet scrolling list or select (move to end).

  • You can also move or copy a sheet within a workbook by dragging. To move the sheet, simply drag the sheet tab to the new position (Figure 24). To copy the sheet, hold down while dragging the sheet tab (Figure 25).

    Figure 24. You can also drag a sheet tab to move it…

    Figure 25. …or hold down while dragging a sheet tab to copy it.


  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint