Microsoft®
Excel 2016 Comprehensive
Boston Columbus Indianapolis New York San Francisco Hoboken Amsterdam Cape Town Dubai London Madrid Milan Munich Paris Montréal Toronto
Delhi Mexico City São Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo
Series Editor Mary Anne Poatsy Mulbery | Davidson
Series Created by Dr. Robert T. Grauer
(ex•ploring) 1. Investigating in a systematic way: examining. 2. Searching into or ranging over for the purpose of discovery.
S E R I E S
Vice President of Career Skills: Andrew Gilfillan Senior Editor: Samantha Lewis Team Lead, Project Management: Laura Burgess Project Manager: Laura Karahalis Program Manager: Emily Biberger Development Editor: Barbara Stover Editorial Assistant: Michael Campbell Director of Product Marketing: Maggie Waples Director of Field Marketing: Leigh Ann Sims Product Marketing Manager: Kaylee Carlson Field Marketing Managers: Molly Schmidt & Joanna Sabella Marketing Coordinator: Susan Osterlitz Senior Operations Specialist: Diane Peirano Senior Art Director: Diane Ernsberger Interior and Cover Design: Diane Ernsberger Cover Photo: Courtesy of Shutterstock® Images Associate Director of Design: Blair Brown Senior Product Strategy Manager: Eric Hakanson Product Manager, MyITLab: Zachary Alexander Media Producer, MyITLab: Jaimie Noy Digital Project Manager, MyITLab: Becca Lowe Media Project Manager, Production: John Cassar Full-Service Project Management: Jenna Vittorioso, Lumina Datamatics, Inc. Composition: Lumina Datamatics, Inc. Efficacy Curriculum Manager: Jessica Sieminski
Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate page within text.
Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and related graphics published as part of the services for any purpose. All such documents and related graphics are provided “as is” without warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information, including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages or any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action, arising out of or in connection with the use or performance of information available from the services.
The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s) and/or the program(s) described herein at any time. Partial screen shots may be viewed in full within the software version specified.
Microsoft® and Windows® are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries. This book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.
Copyright © 2017 by Pearson Education, Inc. All rights reserved. Manufactured in the United States of America. This publication is protected by copyright and permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions department, please visit www.pearsoned.com/permissions/.
Many of the designations by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations have been printed in initial caps or all caps.
Library of Congress Control Number: 2015956945
10 9 8 7 6 5 4 3 2 1
ISBN 10: 0-13-447944-0 ISBN 13: 978-0-13-447944-6
Dedications
For my husband, Ted, who unselfishly continues to take on more than his share to support me throughout the process; and for my children, Laura, Carolyn, and Teddy, whose encouragement and love have been inspiring.
Mary Anne Poatsy
I dedicate this book to three people who had a significant impact on my career. Penny Sumpter, my high school business teacher, taught me about professionalism and paying attention to details; she inspired me to become a business educator. Dr. Amanda Copeland, the department chair while I completed my bachelor’s degree, was an excellent mentor and visionary. Barbra Hoge, my first department chair at Utah Valley State College, was a strong leader and advocate for her faculty; her leadership serves as a role model for me in my capacity as department chair today.
Keith Mulbery
I dedicate this book in loving memory of my grandfather Laurence L. Leggett. A passionate lifelong educator, gifted musician, and incredible role model. I will never forget our time together. I strive every day to make you proud.
Jason Davidson
To my husband Dan, whose encouragement, patience, and love helped make this endeavor possible. Thank you for taking on the many additional tasks at home so that I could focus on writing.
Amy Rutledge
iv About the Authors
About the Authors
Mary Anne Poatsy, Series Editor, Windows 10 Author Mary Anne is a senior faculty member at Montgomery County Community College, teaching various computer application and concepts courses in face-to-face and online environments. She holds a B.A. in Psychology and Education from Mount Holyoke College and an M.B.A. in Finance from Northwestern University’s Kellogg Graduate School of Management.
Mary Anne has more than 12 years of educational experience. She is currently adjunct faculty at Gwynedd-Mercy College and Montgomery County Community College. She has also taught at Bucks County Community College and Muhlenberg College, as well as conducted personal training. Before teaching, she was Vice President at Shearson Lehman in the Municipal Bond Investment Banking Department.
Dr. Keith Mulbery, Excel Author Dr. Keith Mulbery is the Department Chair and a Professor in the Information Systems and Technology Department at Utah Valley University (UVU), where he currently teaches systems analysis and design, and global and ethical issues in information systems and technology. He has also taught computer applications, C# programming, and management information systems. Keith served as Interim Associate Dean, School of Computing, in the College of Technology and Computing at UVU.
Keith received the Utah Valley State College Board of Trustees Award of Excellence in 2001, School of Technology and Computing Scholar Award in 2007, and School of Technology and Computing Teaching Award in 2008. He has authored more than 17 textbooks, served as Series Editor for the Exploring Office 2007 series, and served as developmental editor on two textbooks for the Essentials Office 2000 series. He is frequently asked to give presentations and workshops on Microsoft Office Excel at various education conferences.
Keith received his B.S. and M.Ed. in Business Education from Southwestern Oklahoma State University and earned his Ph.D. in Education with an emphasis in Business Information Systems at Utah State University. His dissertation topic was computer-assisted instruction using Prentice Hall’s Train and Assess IT program (the predecessor to MyITLab) to supplement traditional instruction in basic computer proficiency courses.
Jason Davidson, Excel Author Jason Davidson is a faculty member in the College of Business at Butler University, where he teaches Advanced Web Design, Data Networks, Data Analysis and Business Modeling, and introductory information systems courses. He is the co-author of Exploring Microsoft Excel 2013 Comprehensive, Exploring Microsoft Office 2013 Volume 2, Exploring Microsoft Office 2013 Plus, and Exploring VBA for Microsoft Office 2013.
With a background in media development, prior to joining the faculty at Butler, he worked in the technical publishing industry. Along with teaching, he currently serves as an IT consultant for regional businesses in the Indianapolis area. He holds a B.A. in Telecommunication Arts from Butler University and an M.B.A. from Morehead State University. He lives in Indianapolis, Indiana, and in his free time enjoys road biking, photography, and spending time with his family.
Amy Rutledge, Common Features Author Amy Rutledge is a Special Instructor of Management Information Systems at Oakland University in Rochester, Michigan. She coordinates academic programs in Microsoft Office applications and introductory management information systems courses for the School of Business Administration. Before joining Oakland University as an instructor, Amy spent several years working for a music distribution company and automotive manufacturer in various corporate roles including IT project
About the Authors v
management. She holds a B.S. in Business Administration specializing in Management Information Systems, and a B.A. in French Modern Language and Literature. She holds an M.B.A from Oakland University. She resides in Michigan with her husband Dan and daughters Emma and Jane.
Dr. Robert T. Grauer, Creator of the Exploring Series Bob Grauer is an Associate Professor in the Department of Computer Information Systems at the University of Miami, where he is a multiple winner of the Outstanding Teaching Award in the School of Business, most recently in 2009. He has written numerous COBOL texts and is the vision behind the Exploring Office series, with more than three million books in print. His work has been translated into three foreign languages and is used in all aspects of higher education at both national and international levels. Bob Grauer has consulted for several major corporations including IBM and American Express. He received his Ph.D. in Operations Research in 1972 from the Polytechnic Institute of Brooklyn.
vi Brief Contents
Brief Contents
Office Office 2016 Common Features 2
Excel CHAPTER 1 Introduction to Excel 68 CHAPTER 2 Formulas and Functions 152 CHAPTER 3 Charts 198 CHAPTER 4 Datasets and Tables 262 CHAPTER 5 Subtotals, PivotTables, and PivotCharts 328 CHAPTER 6 What-If Analysis 396 CHAPTER 7 Specialized Functions 454 CHAPTER 8 Statistical Functions 510 CHAPTER 9 Multiple-Sheet Workbook Management 558 CHAPTER 10 Imports, XML, and Power Add-Ins 616 CHAPTER 11 Collaboration and Workbook Distribution 670 CHAPTER 12 Templates, Styles, and Macros 736
Excel Introductory Capstone Exercise (Chs. 1–4) 792 Excel Comprehensive Capstone Exercise (Chs. 5–12) 795
MICROSOFT OFFICE 2016 SPECIALIST EXCEL 799 GLOSSARY 807 INDEX 817
Application Capstone Exercises
Contents vii
Contents
Microsoft Office 2016 ■ CHAPTER ONE Office 2016 Common Features: Taking the First Step 2 CASE STUDY SPOTTED BEGONIA ART GALLERY 2 GETTING STARTED WITH OFFICE APPLICATIONS 4
Starting an Office Application 4 Working with Files 5 Using Common Interface Components 8 Getting Help 15 Installing Add-ins 17
HANDS-ON EXERCISES 1 Getting Started with Office Applications 19
FORMAT DOCUMENT CONTENT 24 Using Templates and Applying Themes 24 Modifying Text 26 Relocating Text 30 Checking Spelling and Grammar 32 Working with Pictures and Graphics 34
HANDS-ON EXERCISES 2 Format Document Content 37
MODIFY DOCUMENT LAYOUT AND PROPERTIES 42 Using Backstage View 42 Changing the Document View 44 Changing the Page Layout 45 Inserting a Header and Footer 48 Previewing and Printing a File 49
HANDS-ON EXERCISES 3 Modify Document Layout and Properties 51
CHAPTER OBJECTIVES REVIEW 56 KEY TERMS MATCHING 58 MULTIPLE CHOICE 59 PRACTICE EXERCISES 60 MID-LEVEL EXERCISES 64 BEYOND THE CLASSROOM 66 CAPSTONE EXERCISE 67
Microsoft Office Excel 2016 ■ CHAPTER ONE Introduction to Excel: Creating and Formatting
68 CASE STUDY OK OFFICE SYSTEMS 68 INTRODUCTION TO SPREADSHEETS 70
Exploring the Excel Window 70 Entering and Editing Cell Data 73
HANDS-ON EXERCISES 1 Introduction to Spreadsheets 79
MATHEMATICAL OPERATIONS AND FORMULAS 83 Creating Formulas 83 Displaying Cell Formulas 86
HANDS-ON EXERCISES 2 Mathematical Operations and Formulas 88
WORKSHEET STRUCTURE AND CLIPBOARD TASKS 93 Managing Columns and Rows 93 Selecting, Moving, Copying, and Pasting Data 98
HANDS-ON EXERCISES 3 Worksheet Structure and Clipboard Tasks 104
WORKSHEET FORMATTING 110 Applying Cell Styles, Alignment, and Font Options 110 Applying Number Formats 113
HANDS-ON EXERCISES 4 Worksheet Formatting 116
WORKSHEETS, PAGE SETUP, AND PRINTING 121 Managing Worksheets 121 Selecting Page Setup Options 123 Previewing and Printing a Worksheet 129
HANDS-ON EXERCISES 5 Worksheets, Page Setup, and Printing 131
CHAPTER OBJECTIVES REVIEW 135 KEY TERMS MATCHING 137 MULTIPLE CHOICE 138 PRACTICE EXERCISES 139 MID-LEVEL EXERCISES 145 BEYOND THE CLASSROOM 148 CAPSTONE EXERCISE 149
viii Contents
■ CHAPTER TWO Formulas and Functions: Performing Quantitative Analysis 152 CASE STUDY TOWNSEND MORTGAGE COMPANY 152 FORMULA BASICS 154
Using Relative, Absolute, and Mixed Cell References in Formulas 154
HANDS-ON EXERCISES 1 Formula Basics 158
FUNCTION BASICS 161 Inserting a Function 161 Inserting Basic Math and Statistics Functions 163 Using Date Functions 167
HANDS-ON EXERCISES 2 Function Basics 169
LOGICAL, LOOKUP, AND FINANCIAL FUNCTIONS 174 Determining Results with the IF Function 174 Using Lookup Functions 177 Calculating Payments with the PMT Function 180
HANDS-ON EXERCISES 3 Logical, Lookup, and Financial Functions 182
CHAPTER OBJECTIVES REVIEW 187 KEY TERMS MATCHING 188 MULTIPLE CHOICE 189 PRACTICE EXERCISES 190 MID-LEVEL EXERCISES 193 BEYOND THE CLASSROOM 196 CAPSTONE EXERCISE 197
■ CHAPTER THREE Charts: Depicting Data Visually 198 CASE STUDY COMPUTER JOB OUTLOOK 198 CHART BASICS 200
Selecting the Data Source 200 Choosing a Chart Type 202 Moving, Sizing, and Printing a Chart 214
HANDS-ON EXERCISES 1 Chart Basics 218
CHART ELEMENTS 224 Adding, Editing, and Formatting Chart Elements 225
HANDS-ON EXERCISES 2 Chart Elements 235
CHART DESIGN AND SPARKLINES 240 Applying a Chart Style and Colors 240
Modifying the Data Source 241 Creating and Customizing Sparklines 243
HANDS-ON EXERCISES 3 Chart Design and Sparklines 246
CHAPTER OBJECTIVES REVIEW 249 KEY TERMS MATCHING 251 MULTIPLE CHOICE 252 PRACTICE EXERCISES 253 MID-LEVEL EXERCISES 257 BEYOND THE CLASSROOM 260 CAPSTONE EXERCISE 261
■ CHAPTER FOUR Datasets and Tables: Managing Large Volumes of Data 262 CASE STUDY REID FURNITURE STORE 262 LARGE DATASETS 264
Freezing Rows and Columns 265 Printing Large Datasets 266
HANDS-ON EXERCISES 1 Large Datasets 270
EXCEL TABLES 275 Understanding the Benefits of Data Tables 275 Designing and Creating Tables 275 Applying a Table Style 280
HANDS-ON EXERCISES 2 Excel Tables 282
TABLE MANIPULATION 287 Creating Structured References in Formulas 287 Sorting Data 288 Filtering Data 290
HANDS-ON EXERCISES 3 Table Manipulation 295
TABLE AGGREGATION AND CONDITIONAL FORMATTING 302 Adding a Total Row 302 Applying Conditional Formatting 304 Creating a New Rule 309
HANDS-ON EXERCISES 4 Table Aggregation and Conditional Formatting 312
CHAPTER OBJECTIVES REVIEW 317 KEY TERMS MATCHING 318 MULTIPLE CHOICE 319 PRACTICE EXERCISES 320 MID-LEVEL EXERCISES 324 BEYOND THE CLASSROOM 326 CAPSTONE EXERCISE 327
Contents ix
■ CHAPTER FIVE Summarizing and Analyzing Data 328
CASE STUDY IVORY HALLS PUBLISHING COMPANY 328 SUBTOTALS AND OUTLINES 330
Subtotaling Data 330 Grouping and Ungrouping Data 333
HANDS-ON EXERCISES 1 Subtotals and Outlines 335
PIVOTTABLE BASICS 339 Creating a PivotTable 339 Modifying a PivotTable 343
HANDS-ON EXERCISES 2 PivotTable Basics 350
PIVOTTABLE OPTIONS 355 Filtering and Slicing a PivotTable 355 Creating a Calculated Field 359 Changing the PivotTable Design 362
HANDS-ON EXERCISES 3 PivotTable Options 364
DATA MODELING AND PIVOTCHARTS 369 Creating a Data Model 369 Creating a PivotChart 372
HANDS-ON EXERCISES 4 Data Modeling and PivotCharts 374
CHAPTER OBJECTIVES REVIEW 379 KEY TERMS MATCHING 381 MULTIPLE CHOICE 382 PRACTICE EXERCISES 384 MID-LEVEL EXERCISES 389 BEYOND THE CLASSROOM 392 CAPSTONE EXERCISE 393
■ CHAPTER SIX What-If Analysis: Using Decision-Making Tools 396 CASE STUDY PERSONAL FINANCE: BUYING YOUR
FIRST HOME 396 RANGE NAMES 398
Creating and Maintaining Range Names 398
HANDS-ON EXERCISES 1 Range Names 402
ONE- AND TWO-VARIABLE DATA TABLES 406 Creating a One-Variable Data Table 406 Creating a Two-Variable Data Table 410
HANDS-ON EXERCISES 2 One- and Two-Variable Data Tables 413
GOAL SEEK AND SCENARIO MANAGER 418 Determining Optimal Input Values Using Goal Seek 418 Using Scenario Manager 419
HANDS-ON EXERCISES 3 Goal Seek and Scenario Manager 424
SOLVER 428 Loading the Solver Add-In 428 Optimizing Results with Solver 429
HANDS-ON EXERCISES 4 Solver 435
CHAPTER OBJECTIVES REVIEW 440 KEY TERMS MATCHING 441 MULTIPLE CHOICE 442 PRACTICE EXERCISES 443 MID-LEVEL EXERCISES 447 BEYOND THE CLASSROOM 450 CAPSTONE EXERCISE 451
■ CHAPTER SEVEN Specialized Functions: Using Date, Logical, Lookup, 454
CASE STUDY TRANSPAYNE FILTRATION 454 DATE, LOGICAL, AND LOOKUP FUNCTIONS 456
Using Date Functions 456 Creating a Nested Logical Function 457 Using Advanced Lookup Functions 462
HANDS-ON EXERCISES 1 Date, Logical, and Lookup Functions 466
DATABASE FILTERING AND FUNCTIONS 471 Applying Advanced Filtering 471 Manipulating Data with Database Functions 475
HANDS-ON EXERCISES 2 Database Filtering and Functions 478
FINANCIAL FUNCTIONS 482 Using Financial Functions 482 Creating a Loan Amortization Table 484
HANDS-ON EXERCISES 3 Financial Functions 488
CHAPTER OBJECTIVES REVIEW 493 KEY TERMS MATCHING 494 MULTIPLE CHOICE 495 PRACTICE EXERCISES 496 MID-LEVEL EXERCISES 502 BEYOND THE CLASSROOM 506 CAPSTONE EXERCISE 507
x Contents
■ CHAPTER EIGHT Statistical Functions: Analyzing Statistics 510 CASE STUDY EDUCATION EVALUATION 510 MATH AND STATISTICAL FUNCTIONS 512
Using Conditional Math and Statistical Functions 512 Calculating Relative Standing with Statistical Functions 516
HANDS-ON EXERCISES 1 Math and Statistical Functions 520
DESCRIPTIVE STATISTICAL FUNCTIONS 525 Measuring Central Tendency 525
HANDS-ON EXERCISES 2 Descriptive Statistical Functions 531
INFERENTIAL STATISTICS 534 Loading the Analysis ToolPak 534 Performing Analysis Using the Analysis ToolPak 535 Creating a Forecast Sheet 538
HANDS-ON EXERCISES 3 Inferential Statistics 540
CHAPTER OBJECTIVES REVIEW 545 KEY TERMS MATCHING 546 MULTIPLE CHOICE 547 PRACTICE EXERCISES 548 MID-LEVEL EXERCISES 552 BEYOND THE CLASSROOM 555 CAPSTONE EXERCISE 556
■ CHAPTER NINE Multiple-Sheet Workbook Management: 558
CASE STUDY CIRCLE CITY SPORTING GOODS 558 MULTIPLE WORKSHEETS 560
Working with Grouped Worksheets 560 Inserting Hyperlinks 563 Managing Windows 565
HANDS-ON EXERCISES 1 Multiple Worksheets 569
3-D FORMULAS AND LINKED WORKBOOKS 574 Inserting Formulas and Functions with 3-D References 574 Linking Workbooks 577
HANDS-ON EXERCISES 2 3-D Formulas and Linked Workbooks 581
FORMULA AUDITS AND DATA VALIDATION 585 Auditing Formulas 586 Setting Up a Watch Window 589 Validating Data 590
HANDS-ON EXERCISES 3 Formula Audits and Data Validation 594
CHAPTER OBJECTIVES REVIEW 600 KEY TERMS MATCHING 602 MULTIPLE CHOICE 603 PRACTICE EXERCISES 604 MID-LEVEL EXERCISES 609 BEYOND THE CLASSROOM 612 CAPSTONE EXERCISE 613
■ CHAPTER TEN Imports, XML, and Power Add-Ins: Managing Data 616 CASE STUDY STOCK ANALYSIS 616 EXTERNAL DATA 618
Importing Data from External Sources 618 Managing Connections 624
HANDS-ON EXERCISES 1 External Data 627
TEXT MANIPULATION 630 Converting Text to Columns 630 Manipulating Text with Functions 631 Using Flash Fill 633
HANDS-ON EXERCISES 2 Text Manipulation 634
XML 638 Understanding XML Syntax 638 Importing XML Data into Excel 640
HANDS-ON EXERCISES 3 XML 642
POWER ADD-INS 644 Using Power Pivot Functionality 644 Importing Data with Power Query 646 Visualizing Data with Power View 648
HANDS-ON EXERCISES 4 Power Add-Ins 650
CHAPTER OBJECTIVES REVIEW 656 KEY TERMS MATCHING 657 MULTIPLE CHOICE 658 PRACTICE EXERCISES 659 MID-LEVEL EXERCISES 664 BEYOND THE CLASSROOM 666 CAPSTONE EXERCISE 667
Contents xi
Application Capstone Exercises
Excel Introductory Capstone Exercise (Chs. 1–4) 792
Excel Comprehensive Capstone Exercise (Chs. 5–12) 795
MICROSOFT OFFICE 2016 SPECIALIST EXCEL 799
GLOSSARY 807
INDEX 817
■ CHAPTER ELEVEN Collaboration and Workbook Distribution: 670
CASE STUDY MARCHING BAND SENIOR DINNER 670 WORKBOOK CUSTOMIZATION 672
Customizing Excel Options 672 Changing Properties 677
HANDS-ON EXERCISES 1 Workbook Customization 680
COLLABORATION 683 Inserting Comments 683 Sharing and Merging Workbooks 686 Tracking Changes 690
HANDS-ON EXERCISES 2 Collaboration 694
WORKBOOK INFORMATION 701 Checking for Issues 701 Protecting a Workbook 705
HANDS-ON EXERCISES 3 Workbook Information 709
WORKBOOK DISTRIBUTION 713 Saving a Workbook in Different Formats 713 Sending a Workbook to Others 715
HANDS-ON EXERCISES 4 Workbook Distribution 719
CHAPTER OBJECTIVES REVIEW 722 KEY TERMS MATCHING 724 MULTIPLE CHOICE 725 PRACTICE EXERCISES 726 MID-LEVEL EXERCISES 730 BEYOND THE CLASSROOM 733 CAPSTONE EXERCISE 734
■ CHAPTER TWELVE Standardizing Workbooks 736
CASE STUDY STAFF ACCOUNTING SERVICES 736 TEMPLATES, THEMES, AND STYLES 738
Selecting a Template 738 Applying Themes and Backgrounds 739 Applying Cell Styles 741
HANDS-ON EXERCISES 1 Templates, Themes, and Styles 745
CUSTOM TEMPLATES AND WORKBOOK PROTECTION 749 Creating and Using a Template 749 Protecting a Cell, a Worksheet, and a Workbook 750
HANDS-ON EXERCISES 2 Custom Templates and Workbook Protection 755
MACROS 761 Creating a Macro 761 Creating Macro Buttons 764 Setting Macro Security 766
HANDS-ON EXERCISES 3 Macros 768