Excel Lesson 7 Working with Multiple Worksheets and Workbooks

1. Excel Lesson 7Working with Multiple Worksheets and Workbooks Microsoft Office 2007: Introductory

2. Objectives • Move between worksheets in a workbook. • Rename worksheets. • Change the color of sheet tabs. • Reposition worksheets. • Hide and unhide worksheets. • Insert and delete worksheets. Microsoft Office 2007: Introductory

3. Objectives (continued) • Create cell references to other worksheets. • Create 3-D references. • Print all or part of workbooks. • Arrange multiple workbooks in the program window. • Move and copy worksheets between workbooks. Microsoft Office 2007: Introductory

4. 3-D reference Active sheet Destination Sheet tab Source Worksheet range Vocabulary Microsoft Office 2007: Introductory

5. Moving Between Worksheets • A workbook is a collection of worksheets. • The worksheets within the workbook are identified by sheet tabs that appear at the bottom of the workbook window. The name of the worksheet appears on the tab. • To view a specific worksheet, simply click its sheet tab. • The worksheet that appears in the workbook window is called the active sheet. The active sheet has a white sheet tab. Microsoft Office 2007: Introductory

6. Identifying Worksheets • Renaming Worksheets: A good practice is to use descriptive names to help identify the contents of each worksheet. For example, the worksheet name Quarter 1 Budget is a better reminder of the worksheet contents than Sheet1. To rename a worksheet, double-click its sheet tab, type the new name, and then press the Enter key. • Changing the Color of Sheet Tabs: Another way to categorize worksheets is by changing the color of the sheet tabs. To change the tab color of a worksheet, right-click the sheet tab you want to recolor, point to Tab Color on the shortcut menu, and then click the color you want for that tab. Microsoft Office 2007: Introductory

7. Managing Worksheets within a Workbook • Repositioning Worksheets: You can reposition a worksheet by dragging its sheet tab to a new location. A placement arrow indicates the new location. When you release the mouse button, the worksheet moves to that position. • Hiding and Unhiding Worksheets: You can keep the sheet tabs streamlined by hiding the worksheets to which you do not need immediate access. Right-click the worksheet you want to hide, and then click Hide on the shortcut menu. To unhide a worksheet, right-click any sheet tab, and then click Unhide on the shortcut menu. Microsoft Office 2007: Introductory

8. Managing Worksheets within a Workbook (continued) • Inserting Worksheets: To insert a blank worksheet, click the Insert Worksheet tab next to the existing sheet tabs. A new worksheet is added after the other worksheets. • Deleting Worksheets: Deleting permanently removes a worksheet and all its contents from the workbook. You cannot undo the action. To delete a worksheet, click the sheet tab for the worksheet you want to remove. On the Home tab of the Ribbon, in the Cells group, click the arrow to the right of the Delete button, and then click Delete Sheet. Microsoft Office 2007: Introductory

9. Consolidating Workbook Data • Creating Cell References to Other Worksheets: You can create a reference to existing data and formulas in other worksheets. • To display data or formula results from one worksheet in another worksheet of the same workbook, you use a formula. • Click the destination cell where you want to display the data or formula results from another worksheet. Type an equal sign. Click the sheet tab for the worksheet that contains the source cell or range you want to reference, and then click the source cell or select the source rang. Finally, press the Enter key to complete the formula. The contents of the source cell appear in the destination cell. Microsoft Office 2007: Introductory

10. Consolidating Workbook Data (continued) • A 3-D reference is a reference to the same cell or range in multiple worksheets that you use in a formula. You can use 3-D references to incorporate data from other worksheets into the active worksheet. • 3-D reference lists the worksheet range, an exclamation point, and a cell or range. A worksheet range is a group of adjacent worksheets. In a worksheet range, as in a cell range, a colon separates the names of the first worksheet and the last worksheet in the group. An exclamation mark separates the worksheet range from its cell or range reference. Microsoft Office 2007: Introductory

11. Printing a Workbook • You can print an entire workbook, selected worksheets, or selected areas of a workbook. You designate the portion of the workbook to print in the Print what section of the Print dialog box. • Printing Non-adjacent Selections of a Worksheet: To select more than one cell or range in a worksheet, select the first cell or range, hold down the Ctrl key, select each additional cell or range, and then release the Ctrl key. • Printing More Than One Worksheet: To print all of the worksheets in the workbook, click the Entire workbook option in the Print what section of the Print dialog box. Microsoft Office 2007: Introductory

12. Working with Multiple Workbooks • Arranging Workbooks: Arranging lets you view more than one workbook on the screen at the same time. To arrange all the open workbooks, click the Arrange All button in the Window group on the View tab of the Ribbon. The Arrange Windows dialog box appears. • Moving and Copying Worksheets Between Workbooks: When you need to use a worksheet from one workbook in another, you can copy or move the worksheet. Right-click the sheet tab of the worksheet you want to move or copy, and then click Move or Copy on the shortcut menu. Microsoft Office 2007: Introductory

13. Summary • Sheet tabs identify the names of worksheets. You click a sheet tab to make a worksheet the active sheet. • You can rename worksheets with more descriptive names to better distinguish them. You can also change the color of the sheet tabs. • Data is often best organized in multiple worksheets. You can drag a sheet tab to a new position to organize the worksheets in a more logical order. You can hide worksheets from view and then unhide them when needed. You can also insert and delete worksheets to accommodate the data. Microsoft Office 2007: Introductory

14. Summary (continued) • Rather than retyping data, you can create references to cells in another worksheet. You can also create formulas with 3-D references to the same cell or range in multiple worksheets. • Entire workbooks, selected worksheets, or selected ranges in a worksheet can be printed. • Arranging multiple workbooks in the program window lets you view their contents at the same time. Worksheets can be moved or copied from one workbook to the location you specify in the same or another workbook. Microsoft Office 2007: Introductory