Microsoft Official Academic Course
MICROSOFT EXCEL 2016
Includes coverage of the following Microsoft Office Specialist (MOS) exam: MOS EXAM 77-727: EXCEL 2016
II
Tis book was set in Garamond Gara mond by Box welve welve Communications. Copyright © 2016 by Microsoft. 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, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate approp riate per-copy fee to the Copyright Clearance Clea rance Center, Inc. 222 Rosewood Drive, Danvers, MA 01923, 01923, (978) (978) 750-8400, fax fa x (978) 646-8600. Requests R equests to the t he Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, (201) 748-6011, fax (201) 748-6008. o order books or for customer service, please call 1-800-CALL WILEY (225-5945). Microsoft, ActiveX, Excel, InfoPath, Microsoft Press, MSDN, OneNote, Outlook, PivotChart, Pivotable, PowerPoint, SharePoint, SQL Server, Visio, Windows, Windows Mobile, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the UnitUnited States and/or other countries. Other Ot her product and company names mentioned herein may be the trademarks of their respective owners. Te example companies, organizations, organiz ations, products, domain names, e-mail addresses, addresses , logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, addre ss, logo, person, place, or event is intended i ntended or should be inferred. Te book expresses the author’s views and opinions. Te information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, John Wiley & Sons, Inc., Microsoft Corporation, nor their resellers or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book. Tis document is provided “as-is”. Information and views expressed in this document, including URL UR L and other Internet Web site references, references, may change without w ithout notice. Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred. ©2016 Microsoft Corporation and/or its suppliers. All rights reserved. Microsoft and the trademarks listed at http:/ http://www.microsoft.com/ /www.microsoft.com/enus/legal/intellectualproperty/trademarks/en-us.aspx enus/legal/intellectualproperty/trademarks/en-us.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. ISBN: 978-1-11-927299-1
II
Tis book was set in Garamond Gara mond by Box welve welve Communications. Copyright © 2016 by Microsoft. 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, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate approp riate per-copy fee to the Copyright Clearance Clea rance Center, Inc. 222 Rosewood Drive, Danvers, MA 01923, 01923, (978) (978) 750-8400, fax fa x (978) 646-8600. Requests R equests to the t he Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, (201) 748-6011, fax (201) 748-6008. o order books or for customer service, please call 1-800-CALL WILEY (225-5945). Microsoft, ActiveX, Excel, InfoPath, Microsoft Press, MSDN, OneNote, Outlook, PivotChart, Pivotable, PowerPoint, SharePoint, SQL Server, Visio, Windows, Windows Mobile, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the UnitUnited States and/or other countries. Other Ot her product and company names mentioned herein may be the trademarks of their respective owners. Te example companies, organizations, organiz ations, products, domain names, e-mail addresses, addresses , logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, addre ss, logo, person, place, or event is intended i ntended or should be inferred. Te book expresses the author’s views and opinions. Te information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, John Wiley & Sons, Inc., Microsoft Corporation, nor their resellers or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book. Tis document is provided “as-is”. Information and views expressed in this document, including URL UR L and other Internet Web site references, references, may change without w ithout notice. Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred. ©2016 Microsoft Corporation and/or its suppliers. All rights reserved. Microsoft and the trademarks listed at http:/ http://www.microsoft.com/ /www.microsoft.com/enus/legal/intellectualproperty/trademarks/en-us.aspx enus/legal/intellectualproperty/trademarks/en-us.aspx are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. ISBN: 978-1-11-927299-1
III
WILEY END-US ER LICENSE AGREEMENT
Tis End-User License Agreement (the “Agreement”) is a legal agreement between you and John Wiley & Sons, Inc. (“Wiley”) for the Wiley proprietary material (the “Licensed Material ”) that th at you may have licensed through a third party or from Wiley directly. Your use of the Licensed Materials Your Materia ls indicates your acceptance of the t he terms and conditions of this thi s Agreement. License Wiley hereby hereby grants you, and you accept, a non-exclusive and non-transferable non-transferable license to view, use and display the Licensed Material on the following terms and conditions only: a. You acknowledge that the Licensed Material is being licensed to you and use is subject to the terms and conditions of this A greement. b. Te Licensed Material is for your personal use only. c. You You may use the Licensed Material Materia l only on the number of devices permitted by the license terms set forth elsewhere els ewhere and/or the Digital Rights Rig hts Management (“DRM”), if any. d. You You may not copy/paste/print copy/paste/print the Licensed Material except as expressly permitted by the license licen se terms set forth elsewhere el sewhere and/or the DRM, if any. e. You may not upload, copy, modify, print, transmit, transfer, sublicense, sell, make or distribute copies of the Licensed Material in whole or in part. If you transfer possession of any copy or modification of the Licensed Material to any third t hird party part y, your license is automatically terminated termin ated and any attempt to sell or otherwise transfer the Licensed Material shall be null and void. Such termination will be in addition to and not in lieu of any equitable, civil, or other remedies available to Wiley. f. If you accessed the Licensed Material from a third party website or using a device which requires you to accept any other user agreement, you are bound by both this Agreement and any third party website or device-specific agreement. In the event of a conflict between this Agreement and any third party agreement, the more restrictive terms will apply. g. If the website or device applies any form of Digital Rights Management (“DRM”) to the Licensed Material, you may not disable or remove that DRM. h. You You agree that any a ny materials in print format that may be included with this th is license (for example, materials printed on demand) or portions thereof ordered or printed from any third part y platform are licensed for your personal use only and a nd may not be transferred or sold under any condition. condition. All terms of this Agreement also apply to these print materials. erm Tis Agreement shall terminate upon the conditions discussed elsewhere in this Agreement, or if you fail to comply with any term or condition of this th is Agreement. Upon such termination, you agree to immediately discontinue all access to the Licensed Material.
IV
Wiley’s Rights You acknowledge You ack nowledge that all rights (including without limitation, copyrights, c opyrights, patents and trade tr ade secrets) in the Licensed Material are the sole and exclusive property of Wiley and/or its licensors. By accepting this th is Agreement, you do not become the owner of the Licens ed Material, but you do have a limited license to use it in accordance with the provisions of this Agreement. You agree to protect the Licensed Material Materia l from unauthorized use, u se, upload, download, reproduction, reproduction, or distribution. You further agree not to translate, decompile, disassemble or otherwise reverse engineer the Licensed Material. DISCLAIMER LICENSED MAERIAL(S) ARE PROVIDED “AS IS,” WIHOU WARRANY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BU NO LIMIED O HE IMPLIED WARRANI WARR ANIES ES OF MERCHAN MERCHANABILIY ABILIY OR OR FINESS FOR A PARICULAR PURPOSE. HE ENIRE RISK AS O HE RESULS OR PERFORMANCE, AND HE COS OF ALL NECESSARY SERVICING, REPAIR, R EPAIR, OR CORR C ORRECION ECION OF HE HE LICENSED MAERIAL IS ASSUMED BY YOU. IN NO EVEN WILL WILEY OR IS LICENSORS BE LIABLE O YOU FOR ANY DAMAGES, INCLUDING WIHOU LIMIAION LOS PROFIS, LOS SAVINGS, OR OHER INCIDENAL OR CONSEQUENIAL DAM AGES ARISING OU OF HE USE OR INABILIY INABILI Y O USE HE LICENSED MAERI AL(S). General Tis Agreement represents the entire agreement between us and supersedes a ny prior Agreements, oral or written, and any other communication between us relating to the subject matter of this Agreement. Tis T is Agreement will wi ll be governed and construed as if wholly entered into and a nd performed within the State of New York. You acknowledge that you have read this Agreement, and agree to be bound by its terms and conditions.
V
Preface Welcome to the Microsoft Welcome Microsoft Official Academic Course (MOAC) (MOAC) program for Microsoft Office Office 2016. 2016. MOAC represents the collaboration between Microsoft Learning and John Wiley & Sons, Inc. publishing company. company. Microsoft and Wiley tea med up to produce a series of textbooks that deliver compelling and innovative teaching solutions to instructors and superior learning experiences for students. Infused and a nd informed by in-depth knowledge from the creators of of Microsoft Office and Windows, and crafted by a publisher publisher known worldwide for the pedagogical quality of its products, products, these textbooks maximize skills transfer in minimum time. Students are challenged to reach their potential by using their new technical skills as highly productive members of the workforce. Because this knowledgebase comes directly from Microsoft, architect of the Office 2016 system and creator of the Microsoft Office Specialist (MOS) exams, you are sure to receive the topical coverage that is most relevant to students’ personal and professional success. Microsoft’s direct participation not only assures you that MOAC textbook content is accurate and current; it also means that students will receive the best instruction possible to enable their success on certification exams and in the workplace. THE MICROSOFT OFFICIAL ACADEMIC COURSE PROGRAM
Te Microsoft Official Academic Course series is a complete program for instructors and institutions to prepare and deliver great courses on Microsoft software technologies. With MOAC, we recognize that, because of the rapid pace of change in the technology and curriculum developed by Microsoft, there is an ongoing set of needs beyond classroom instruction instruct ion tools for an instructor to be ready to teach the course. Te MOAC program endeavors to provide solutions for all these needs in a systematic manner in order to ensure a successful and rewarding course experience for both instructor and student—technical and curricu lum training for instructor readiness with new software releases; the software itself for student use at home for building hands-on skills, assessment, and validation of skill developm development; ent; and a great set of tools for delivering instruction in the classroom and lab. All are a re important to the smooth delivery of an interesting course on Microsoft software, and a nd all are a re provided with the MOAC MOAC program.
VI
Book Tour
PEDAGOGICAL FEATURES
Te MOAC courseware for Microsoft Office 2016 system are designed to cover all the learning objectives for that MOS exam, which is referred to as its “objective domain.” Many pedagogical features have been developed specifically for Microsoft Official Academic Course programs. Presenting the extensive procedural information and technical concepts woven throughout the textbook raises challenges for the student and instructor alike. Following is a list of key features in each lesson designed to prepare students for succes s on the certification exams and in the workplace: Each lesson begins with a Lesson Skill Matrix . More than a standard list of learning objectives, the skill matrix correlates each software skill covered in the lesson to the specific MOS exam objective domain. Every lesson opens with a Software Orientation. Tis feature provides an overview of the soft ware features students will be working with in the lesson. Te orientation will detail the general properties of the software or specific features, such as a ribbon or dialog box; and it includes a large, labeled screen image. Concise and frequent Step-by-Step instructions teach students new features and provide an opportunity for hands-on practice. Numbered steps give detailed, step-by-step instructions to help students learn software skills. Te steps also show results and screen images to match what students should see on their computer screens. Illustrations: Screen images provide visual feedback as students work through the exercises. Te images reinforce key concepts, provide visual clues about the steps, and allow students to check their progress. Knowledge Assessment: Provides questions from a mix of rue/False, Fill-in-the-Blank, and Multiple Choice, testing students on concepts learned in t he lesson. Projects: Provide progressively more challenging lesson-ending activities. Online files: Te student companion website contains the data files needed for each lesson.
•
•
•
•
•
• •