Exploring Microsoft Excel 2016 Series Editor Mary Anne

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 9 Multiple-Sheet Workbook Management Ensuring Quality Control Copyright © 2017 Pearson Education, Inc.

Objectives • Work with Grouped Worksheets • Insert Hyperlinks • Manage Windows • Insert Formulas and Functions with 3 -D References • Link Workbooks • Audit Formulas • Set Up a Watch Window • Validate Data Copyright © 2017 Pearson Education, Inc.

Objective 1: Work with Grouped Worksheets Skills: Group Worksheets Fill Across Worksheets Enter and Format Data Across Worksheets Copyright © 2017 Pearson Education, Inc.

Work with Grouped Worksheets [Group] Active grouped worksheets Copyright © 2017 Pearson Education, Inc.

Work with Grouped Worksheets Copyright © 2017 Pearson Education, Inc.

Objective 2: Insert Hyperlinks Skills: Insert Hyperlinks Copyright © 2017 Pearson Education, Inc.

Insert Hyperlinks Link options Copyright © 2017 Pearson Education, Inc.

Objective 3: Manage Windows Skills: Open and Arrange Worksheets Split a Window Copyright © 2017 Pearson Education, Inc.

Manage Windows Hidden worksheets Worksheet scroll bars Copyright © 2017 Pearson Education, Inc.

Manage Windows Arrange options Copyright © 2017 Pearson Education, Inc.

Manage Windows Split bar Copyright © 2017 Pearson Education, Inc.

Objective 4: Insert Formulas and Functions with 3 -D References Skills: Insert a 3 -D Reference in a Formula Insert 3 -D References in a Function Copyright © 2017 Pearson Education, Inc.

Insert Formulas and Functions with 3 -D References Cell address =Worksheet. Name!Range. Of. Cells Copyright © 2017 Pearson Education, Inc.

Insert Formulas and Functions with 3 -D References Cell address =October!B 4+November!B 4+December!B 4 =SUM(October: December!B 4) Copyright © 2017 Pearson Education, Inc.

Objective 5: Link Workbooks Skills: Link Workbooks Complete the Linked Workbook Copyright © 2017 Pearson Education, Inc.

Link Workbooks =Range. Of. Cells =B 2: F 10 =[Workbook. Name]Worksheet. Name!Range. Of. Cells =May!B 2: F 10 =[Workbook. Name]Worksheet. Name!Range. Of. Cells =[Bloomington. xlsx]May!B 2: F 10 Copyright © 2017 Pearson Education, Inc.

Link Workbooks Select Enable Content Copyright © 2017 Pearson Education, Inc.

Objective 6: Audit Formulas Skills: Trace Precedents and Dependents Check for Errors Copyright © 2017 Pearson Education, Inc.

Audit Formulas • Worksheet errors: §Syntax—formula or function violates correct construction rules §Run-time—formula or function contains a cell reference with invalid or missing data §Logic error—produces inaccurate results § Circular reference—formula contains a direct or an indirect reference to the cell containing the formula Copyright © 2017 Pearson Education, Inc.

Audit Formulas Trace tools Two trace arrows Copyright © 2017 Pearson Education, Inc.

Audit Formulas Click Error Checking Additional help Error and description Copyright © 2017 Pearson Education, Inc.

Objective 7: Set Up a Watch Window Skills: Set Up a Watch Window Copyright © 2017 Pearson Education, Inc.

Set Up a Watch Window Click Watch Window Watch window Copyright © 2017 Pearson Education, Inc.

Objective 8: Validate Data Skills: Create a Validation Rule Specify Inputs and Alerts Copyright © 2017 Pearson Education, Inc.

Validate Data • Data validation—requires specified rules when data is entered • Data validation enables: §Specification and correction of entered data §Specification of an input message §Specification of an error message Copyright © 2017 Pearson Education, Inc. RANK. EQ

Validate Data Click Data Validation Settings tab Copyright © 2017 Pearson Education, Inc.

Validate Data Copyright © 2017 Pearson Education, Inc.

Summary • Multiple-sheet workbook skills: §Group and manage worksheets §Create hyperlinks §Write multiple-sheet formulas §Link to data on other worksheets and workbooks • For complex workbooks: §Trace Precedents/Dependents §Create a watch window §Validate data Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

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 © 2017 Pearson Education, Inc.