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.

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.

4
Vocabulary

• 3-D reference
• Active sheet
• Destination
• Sheet tab
• Source
• Worksheet range

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.