Transcript kjjhghgff

Excel Lesson 7
Working with Multiple
Worksheets and Workbooks
Microsoft Office 2007:
Introductory
1
Pasewark & Pasewark
Objectives


Excel – Lesson 7

2



Move between worksheets in a workbook.
Rename worksheets.
Change the color of sheet tabs.
Reposition worksheets.
Hide and unhide worksheets.
Insert and delete worksheets.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Objectives (continued)


Excel – Lesson 7

3


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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Vocabulary

Excel – Lesson 7

4




3-D reference
Active sheet
Destination
Sheet tab
Source
Worksheet range
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Moving Between Worksheets

Excel – Lesson 7

5


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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Identifying Worksheets
Excel – Lesson 7

6

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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Managing Worksheets within a
Workbook
Excel – Lesson 7

7

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, rightclick any sheet tab, and then click Unhide on the
shortcut menu.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Managing Worksheets within a
Workbook (continued)
Excel – Lesson 7

8

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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Consolidating Workbook Data
Excel – Lesson 7

9


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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Consolidating Workbook Data
(continued)
Excel – Lesson 7

10

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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Printing a Workbook
Excel – Lesson 7

11


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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Working with Multiple Workbooks
Excel – Lesson 7

12

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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Summary

Excel – Lesson 7

13

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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Summary (continued)
Excel – Lesson 7

14


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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory