Exploring Microsoft Office Excel 2010 INSERT BOOK COVER
Exploring Microsoft Office Excel 2010 INSERT BOOK COVER by Robert Grauer, Keith Mulbery, and Mary Anne Poatsy Chapter 8 Multiple-Sheet Workbook Management Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 1
Objectives • • Work with grouped worksheets Manage windows and workspaces Insert hyperlinks Insert a 3 -D formula Link workbooks Audit formulas Set up a watch window Validate data Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 2
Work with Grouped Worksheets • Create worksheets with identical structure and formatting in the same workbook when data is similar but: – Time periods differ – Company locations differ • Grouping �selecting two or more worksheets so you can perform the same action at the same time • When worksheets are grouped: – Worksheet tabs display a white background color – [Group] appears in the title bar Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 3
Work with Grouped Worksheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 4
Work with Grouped Worksheets • To group: – All worksheets: Right-click and then click Select All Sheets – Adjacent Worksheets: Click the first worksheet tab, press and hold Shift, and then click the last worksheet tab • To ungroup: – Click any unselected worksheet tab – Right-click any selected worksheet tab, and then click Ungroup Sheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 5
Work with Grouped Worksheets • Grouping worksheets improves productivity by letting you perform the following tasks on each worksheet at the same time: – Data Entry – Structural Changes – Formatting – Page Layouts – Printing Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 6
Work with Grouped Worksheets Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 7
Manage Windows and Workspaces • To help you manage worksheet windows you can: – Control worksheet visibility – Open and arrange windows – Split a window – Save an Excel workspace Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 8
Manage Windows and Workspaces Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 9
Manage Windows and Workspaces • To see the contents of two worksheets in the same workbook at the same time: – Use New Window to view another window of the current workbook – Use Arrange All to see both windows of the current workbook Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 10
Manage Windows and Workspaces Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 11
Manage Windows and Workspaces • To split a window ─ divide a worksheet window into two or four resizable panes • Synchronized scrolling �allows you to scroll in one pane while the other pane(s) move similarly Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 12
Manage Windows and Workspaces Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 13
Manage Windows and Workspaces • Workspace file – allows you to save the layout of open windows – File does not contain the actual workbook – File extension is. xlw Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 14
Insert Hyperlinks • A hyperlink ─ an electronic marker that connects to a(n): – Specific cell in the same workbook – Specific cell in a different workbook – Existing file – Web page – E-mail address Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 15
Insert Hyperlinks Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 16
Insert Hyperlinks Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 17
Insert a 3 -D Formula • To consolidate data from several worksheets: – Use a worksheet reference – Create a 3 -D formula – Link data Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 18
Insert a 3 -D Formula Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 19
Link Workbooks • Consolidate data by linking workbooks – Do not move or rename workbooks after creating a link • Benefit of linked workbooks �a change made in one workbook (source file) is updated in the other workbook (destination file) Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 20
Link Workbooks • When you link files, you establish an external reference: – Similar to a worksheet reference – Must include the workbook name, including the extension, between brackets – Default is that absolute cell references are created – =[Cleveland. xlsx]'Qtr 3'A 1 creates a link to cell A 1 in the Qtr 3 worksheet in the Cleveland workbook. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 21
Link Workbooks • Excel displays formulas with external references in two ways: – When the source workbook is open, the file name, worksheet, and cell reference display – When the source workbook is closed, the full path displays • If the destination file is closed when data is changed in the source file, you need to enable links – Excel does not update automatically Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 22
Link Workbooks Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 23
Audit Formulas • Formula Auditing group on the Formulas tab enables you to: – Display or trace relationships – Show formulas – Check for errors – Evaluate formulas • Formula auditing tools help to detect errors: – Syntax error: one that violates correct construction of a formula – Logical error: one that produces inaccurate results Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 24
Audit Formulas Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 25
Audit Formulas • Precedent cells ─ cells referenced by a formula in another cell • Dependent cells ─ contain formulas that refer to other cells • Use Trace Precedents and Trace Dependents commands to graphically display, using tracer arrows, the relationship between these cells and formulas • Tracer arrows �help you identify cells that cause errors Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 26
Audit Formulas • Error Checking command – lets you check for errors that have occurred in formulas anywhere in a worksheet • Error Checking dialog box opens when an error is identified Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 27
Audit Formulas Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 28
Set Up a Watch Window • Watch Window ─ formulas in cells that are not visible can be “watched” – Enables you to create a small window to view the formula calculation – Convenient to inspect, audit, or confirm formula calculations involving cells that are not visible Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 29
Set Up a Watch Window Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 30
Validate Data • Data validation: – Specify and correct the kind of data that can be entered – Specify an input message alerting users when they click a cell that only specific types of data can be entered in that cell – Specify error messages, which appear when others persist and attempt to enter incorrect data Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 31
Validate Data Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 32
Validate Data • To specify data validation criteria, click Data Validation in the Data Tools group on the Data tab • On the Settings tab of the Data Validation dialog box, specify the type of data permitted in a cell • Input messages consist of two parts: – Title and input message • Error alert message should be polite and should clearly state what the error is – Use the Error Alert tab Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 33
Validate Data • Another way to help prevent a user from entering invalid data is to create a list: 1. Create a list of valid entries in a single column or row without blank cells 2. Click the cell for which you want to create a validation rule 3. Click Data Validation in the Data Tools group to show the Data Validation dialog box 4. Click the Settings tab, click the Allow arrow, and then select List 5. Enter a reference to the list in the Source box 6. Make sure that the In-cell dropdown check box is selected and that the Ignore blank check box is clear or selected Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 34
Summary • In this chapter, you learned to manage multiple worksheets and consolidate data as well as ensure valid results. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 35
Questions Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 36
Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2011 Pearson Education, Inc. Publishing as Prentice Hall. 37
- Slides: 37