Excel chapter 9 grader project school of information sciences

--

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

VISUAL BASIC FOR APPLICATIONS 773 Creating a Sub Procedure 773 Creating a Custom Function 776

HANDS-ON EXERCISES 4 Visual Basic for Applications 778

CHAPTER OBJECTIVES REVIEW 781 KEY TERMS MATCHING 782 MULTIPLE CHOICE 783 PRACTICE EXERCISES 784 MID-LEVEL EXERCISES 788 BEYOND THE CLASSROOM 790 CAPSTONE EXERCISE 791

xii Acknowledgments

The Exploring team would like to acknowledge and thank all the reviewers who helped us throughout the years by providing us with their invaluable comments, suggestions, and constructive criticism.

Acknowledgments

Adriana Lumpkin Midland College

Alan S. Abrahams Virginia Tech

Alexandre C. Probst Colorado Christian University

Ali Berrached University of Houston–Downtown

Allen Alexander Delaware Technical & Community College

Andrea Marchese Maritime College, State University of New York

Andrew Blitz Broward College; Edison State College

Angel Norman University of Tennessee, Knoxville

Angela Clark University of South Alabama

Ann Rovetto Horry-Georgetown Technical College

Astrid Todd Guilford Technical Community College

Audrey Gillant Maritime College, State University of New York

Barbara Stover Marion Technical College

Barbara Tollinger Sinclair Community College

Ben Brahim Taha Auburn University

Beverly Amer Northern Arizona University

Beverly Fite Amarillo College

Biswadip Ghosh Metropolitan State University of Denver

Bonita Volker Tidewater Community College

Bonnie Homan San Francisco State University

Brad West Sinclair Community College

Brian Powell West Virginia University

Carol Buser Owens Community College

Carol Roberts University of Maine

Carolyn Barren Macomb Community College

Carolyn Borne Louisiana State University

Cathy Poyner Truman State University

Charles Hodgson Delgado Community College

Chen Zhang Bryant University

Cheri Higgins Illinois State University

Cheryl Brown Delgado Community College

Cheryl Hinds Norfolk State University

Cheryl Sypniewski Macomb Community College

Chris Robinson Northwest State Community College

Cindy Herbert Metropolitan Community College–Longview

Craig J. Peterson American InterContinental University

Dana Hooper University of Alabama

Dana Johnson North Dakota State University

Daniela Marghitu Auburn University

David Noel University of Central Oklahoma

David Pulis Maritime College, State University of New York

David Thornton Jacksonville State University

Dawn Medlin Appalachian State University

Debby Keen University of Kentucky

Debra Chapman University of South Alabama

Debra Hoffman Southeast Missouri State University

Derrick Huang Florida Atlantic University

Diana Baran Henry Ford Community College

Diane Cassidy The University of North Carolina at Charlotte

Diane L. Smith Henry Ford Community College

Dick Hewer Ferris State College

Don Danner San Francisco State University

Don Hoggan Solano College

Don Riggs SUNY Schenectady County Community College

Doncho Petkov Eastern Connecticut State University

Donna Ehrhart State University of New York at Brockport

Elaine Crable Xavier University

Elizabeth Duett Delgado Community College

Erhan Uskup Houston Community College–Northwest

Eric Martin University of Tennessee

Erika Nadas Wilbur Wright College

Floyd Winters Manatee Community College

Acknowledgments xiii

Frank Lucente Westmoreland County Community College

G. Jan Wilms Union University

Gail Cope Sinclair Community College

Gary DeLorenzo California University of Pennsylvania

Gary Garrison Belmont University

Gary McFall Purdue University

George Cassidy Sussex County Community College

Gerald Braun Xavier University

Gerald Burgess Western New Mexico University

Gladys Swindler Fort Hays State University

Hector Frausto California State University Los Angeles

Heith Hennel Valencia Community College

Henry Rudzinski Central Connecticut State University

Irene Joos La Roche College

Iwona Rusin Baker College; Davenport University

J. Roberto Guzman San Diego Mesa College

Jacqueline D. Lawson Henry Ford Community College

Jakie Brown Jr. Stevenson University

James Brown Central Washington University

James Powers University of Southern Indiana

Jane Stam Onondaga Community College

Janet Bringhurst Utah State University

Jean Welsh Lansing Community College

Jeanette Dix Ivy Tech Community College

Jennifer Day Sinclair Community College

Jill Canine Ivy Tech Community College

Jill Young Southeast Missouri State University

Jim Chaffee The University of Iowa Tippie College of Business

Joanne Lazirko University of Wisconsin–Milwaukee

Jodi Milliner Kansas State University

John Hollenbeck Blue Ridge Community College

John Seydel Arkansas State University

Judith A. Scheeren Westmoreland County Community College

Judith Brown The University of Memphis

Juliana Cypert Tarrant County College

Kamaljeet Sanghera George Mason University

Karen Priestly Northern Virginia Community College

Karen Ravan Spartanburg Community College

Karen Tracey Central Connecticut State University

Kathleen Brenan Ashland University

Ken Busbee Houston Community College

Kent Foster Winthrop University

Kevin Anderson Solano Community College

Kim Wright The University of Alabama

Kristen Hockman University of Missouri–Columbia

Kristi Smith Allegany College of Maryland

Laura Marcoulides Fullerton College

Laura McManamon University of Dayton

Laurence Boxer Niagara University

Leanne Chun Leeward Community College

Lee McClain Western Washington University

Linda D. Collins Mesa Community College

Linda Johnsonius Murray State University

Linda Lau Longwood University

Linda Theus Jackson State Community College

Linda Williams Marion Technical College

Lisa Miller University of Central Oklahoma

Lister Horn Pensacola Junior College

Lixin Tao Pace University

Loraine Miller Cayuga Community College

Lori Kielty Central Florida Community College

Lorna Wells Salt Lake Community College

Lorraine Sauchin Duquesne University

Lucy Parakhovnik California State University, Northridge

Lynn Keane University of South Carolina

Lynn Mancini Delaware Technical Community College

Mackinzee Escamilla South Plains College

Marcia Welch Highline Community College

Margaret McManus Northwest Florida State College

Margaret Warrick Allan Hancock College

Marilyn Hibbert Salt Lake Community College

Mark Choman Luzerne County Community College

Maryann Clark University of New Hampshire

Mary Beth Tarver Northwestern State University

Mary Duncan University of Missouri–St. Louis

Melissa Nemeth Indiana University-Purdue University Indianapolis

Melody Alexander Ball State University

Michael Douglas University of Arkansas at Little Rock

xiv Acknowledgments

Michael Dunklebarger Alamance Community College

Michael G. Skaff College of the Sequoias

Michele Budnovitch Pennsylvania College of Technology

Mike Jochen East Stroudsburg University

Mike Michaelson Palomar College

Mike Scroggins Missouri State University

Mimi Spain Southern Maine Community College

Muhammed Badamas Morgan State University

NaLisa Brown University of the Ozarks

Nancy Grant Community College of Allegheny County– South Campus

Nanette Lareau University of Arkansas Community College–Morrilton

Nikia Robinson Indian River State University

Pam Brune Chattanooga State Community College

Pam Uhlenkamp Iowa Central Community College

Patrick Smith Marshall Community and Technical College

Paul Addison Ivy Tech Community College

Paula Ruby Arkansas State University

Peggy Burrus Red Rocks Community College

Peter Ross SUNY Albany

Philip H. Nielson Salt Lake Community College

Philip Valvalides Guilford Technical Community College

Ralph Hooper University of Alabama

Ranette Halverson Midwestern State University

Richard Blamer John Carroll University

Richard Cacace Pensacola Junior College

Richard Hewer Ferris State University

Richard Sellers Hill College

Rob Murray Ivy Tech Community College

Robert Banta Macomb Community College

Robert Dus̆ek Northern Virginia Community College

Robert G. Phipps Jr. West Virginia University

Robert Sindt Johnson County Community College

Robert Warren Delgado Community College

Rocky Belcher Sinclair Community College

Roger Pick University of Missouri at Kansas City

Ronnie Creel Troy University

Rosalie Westerberg Clover Park Technical College

Ruth Neal Navarro College

Sandra Thomas Troy University

Sheila Gionfriddo Luzerne County Community College

Sherrie Geitgey Northwest State Community College

Sherry Lenhart Terra Community College

Sophia Wilberscheid Indian River State College

Sophie Lee California State University, Long Beach

Stacy Johnson Iowa Central Community College

Stephanie Kramer Northwest State Community College

Stephen Z. Jourdan Auburn University at Montgomery

Steven Schwarz Raritan Valley Community College

Sue A. McCrory Missouri State University

Sumathy Chandrashekar Salisbury University

Susan Fuschetto Cerritos College

Susan Medlin UNC Charlotte

Susan N. Dozier Tidewater Community College

Suzan Spitzberg Oakton Community College

Suzanne M. Jeska County College of Morris

Sven Aelterman Troy University

Sy Hirsch Sacred Heart University

Sylvia Brown Midland College

Tanya Patrick Clackamas Community College

Terri Holly Indian River State College

Terry Ray Rigsby Hill College

Thomas Rienzo Western Michigan University

Tina Johnson Midwestern State University

Tommy Lu Delaware Technical Community College

Troy S. Cash Northwest Arkansas Community College

Vicki Robertson Southwest Tennessee Community

Vickie Pickett Midland College

Weifeng Chen California University of Pennsylvania

Wes Anthony Houston Community College

William Ayen University of Colorado at Colorado Springs

Wilma Andrews Virginia Commonwealth University

Yvonne Galusha University of Iowa

Acknowledgments xv

Special thanks to our content development and technical team:

Barbara Stover

Lori Damanti

Elizabeth Lockley

Joyce Nielsen

Janet Pickard

Steven Rubin

Lhe Smith

Mara Zebest

xvi Preface

The Exploring Series and You Exploring is Pearson’s Office Application series that requires students like you to think “beyond the point and click.” In this edition, we have worked to restructure the Exploring experience around the way you, today’s modern student, actually use your resources.

The goal of Exploring is, as it has always been, to go farther than teaching just the steps to accomplish a task—the series provides the theoretical foundation for you to understand when and why to apply a skill. As a result, you achieve a deeper understanding of each application and can apply this critical thinking beyond Office and the classroom.

The How & Why of This Revision Outcomes matter. Whether it’s getting a good grade in this course, learning how to use Excel so students can be successful in other courses, or learning a specific skill that will make learners successful in a future job, everyone has an outcome in mind. And outcomes matter. That is why we revised our chapter opener to focus on the outcomes students will achieve by working through each Exploring chapter. These are coupled with objectives and skills, providing a map students can follow to get everything they need from each chapter.

Critical Thinking and Collaboration are essential 21st century skills. Students want and need to be successful in their future careers—so we used motivating case studies to show relevance of these skills to future careers and incorporated Soft Skills, Collaboration, and Analysis Cases with Critical Thinking steps in this edition to set students up for success in the future.

Students today read, prepare, and study differently than students used to. Students use textbooks like a tool—they want to easily identify what they need to know and learn it efficiently. We have added key features such as Tasks Lists (in purple), Step Icons, Hands-On Exercise Videos, and tracked everything via page numbers that allow efficient navigation, creating a map students can easily follow.

Students are exposed to technology. The new edition of Exploring moves beyond the basics of the software at a faster pace, without sacrificing coverage of the fundamental skills that students need to know.

Students are diverse. Students can be any age, any gender, any race, with any level of ability or learning style. With this in mind, we broadened our definition of “student resources” to include physical Student Reference cards, Hands-On Exercise videos to provide a secondary lecture-like option of review; and MyITLab, the most powerful and most ADA-compliant online homework and assessment tool around with a direct 1:1 content match with the Exploring Series. Exploring will be accessible to all students, regardless of learning style.

Providing You with a Map to Success to Move Beyond the Point and Click All of these changes and additions will provide students an easy and efficient path to follow to be successful in this course, regardless of where they start at the beginning of this course. Our goal is to keep students engaged in both the hands-on and conceptual sides, helping achieve a higher level of understanding that will guarantee success in this course and in a future career.

In addition to the vision and experience of the series creator, Robert T. Grauer, we have assembled a tremendously talented team of Office Applications authors who have devoted themselves to teaching the ins and outs of Microsoft Word, Excel, Access, and PowerPoint. Led in this edition by series editor Mary Anne Poatsy, the whole team is dedicated to the Exploring mission of moving students beyond the point and click.

Preface

Key Features xvii

The How/Why Approach helps students move beyond the point and click to a true understanding of how to apply Microsoft Office skills.

• White Pages/Yellow Pages clearly distinguish the theory (white pages) from the skills covered in the Hands-On Exercises (yellow pages) so students always know what they are supposed to be doing and why.

• Case Study presents a scenario for the chapter, creating a story that ties the Hands-On Exercises together.

• Hands-On Exercise Videos are tied to each Hands-On Exercise and walk students through the steps of the exercise while weaving in conceptual information related to the Case Study and the objectives as a whole.

The Outcomes focus allows students and instructors to know the higher-level learning goals and how those are achieved through discreet objectives and skills.

• Outcomes presented at the beginning of each chapter identify the learning goals for students and instructors.

• Enhanced Objective Mapping enables students to follow a directed path through each chapter, from the objectives list at the chapter opener through the exercises at the end of the chapter. • Objectives List: This provides a simple list of key objectives covered in the chapter. This includes

page numbers so students can skip between objectives where they feel they need the most help. • Step Icons: These icons appear in the white pages and reference the step numbers in the Hands-

On Exercises, providing a correlation between the two so students can easily find conceptual help when they are working hands-on and need a refresher.

• Quick Concepts Check: A series of questions that appear briefly at the end of each white page section. These questions cover the most essential concepts in the white pages required for students to be successful in working the Hands-On Exercises. Page numbers are included for easy reference to help students locate the answers.

• Chapter Objectives Review: Appears toward the end of the chapter and reviews all important concepts throughout the chapter. Newly designed in an easy-to-read bulleted format.

• MOS Certification Guide for instructors and students to direct anyone interested in prepping for the MOS exam to the specific locations to find all content required for the test.

End-of-Chapter Exercises offer instructors several options for assessment. Each chapter has approximately 11–12 exercises ranging from multiple choice questions to open-ended projects.

• Multiple Choice, Key Terms Matching, Practice Exercises, Mid-Level Exercises, Beyond the Classroom Exercises, and Capstone Exercises appear at the end of all chapters. • Enhanced Mid-Level Exercises include a Creative Case (for PowerPoint and Word), which

allows students some flexibility and creativity, not being bound by a definitive solution, and an Analysis Case (for Excel and Access), which requires students to interpret the data they are using to answer an analytic question, as well as Discover Steps, which encourage students to use Help or to problem-solve to accomplish a task.

• Application Capstone exercises are included in the book to allow instructors to test students on the entire contents of a single application.

Key Features

CREATIVE CASE

Watch the Video for this Hands- On Exercise!

ANALYSIS CASE

GraderHOE1 Training

xviii Resources

Resources

Instructor Resources The Instructor’s Resource Center, available at www.pearsonhighered.com, includes the following:

• Instructor Manual provides one-stop-shop for instructors, including an overview of all available resources, teaching tips, as well as student data and solution files for every exercise.

• Solution Files with Scorecards assist with grading the Hands-On Exercises and end-of-chapter exercises.

• Prepared Exams allow instructors to assess all skills covered in a chapter with a single project. • Rubrics for Mid-Level Creative Cases and Beyond the Classroom Cases in Microsoft Word format

enable instructors to customize the assignments for their classes.

• PowerPoint Presentations with notes for each chapter are included for out-of-class study or review.

• Multiple Choice, Key Term Matching, and Quick Concepts Check Answer Keys • Test Bank provides objective-based questions for every chapter. • Scripted Lectures offer an in-class lecture guide for instructors to mirror the Hands-On Exercises. • Syllabus Templates

• Outcomes, Objectives, and Skills List • Assignment Sheet • File Guide

Student Resources

Student Data Files Access your student data files needed to complete the exercises in this textbook at www.pearsonhighered.com/exploring.

Available in MyITLab • Hands-On Exercise Videos allow students to review and study the concepts taught in the Hands-

On Exercises. • Audio PowerPoints provide a lecture review of the chapter content, and include narration. • Multiple Choice quizzes enable you to test concepts you have learned by answering auto-graded

questions. • Book-specific 1:1 Simulations allow students to practice in the simulated Microsoft Office 2016

environment using hi-fidelity, HTML5 simulations that directly match the content in the Hands-On Exercises.

• eText available in some MyITLab courses and includes links to videos, student data files, and other learning aids.

• Book-specific 1:1 Grader Projects allow students to complete end-of-chapter Capstone Exercises live in Microsoft Office 2016 and receive immediate feedback on their performance through various reports.

Microsoft®

Excel 2016 COMPREHENSIVE

(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

Common Features

LEARNING OUTCOME

OBJECTIVES & SKILLS:

4 Open a Microsoft Office Application, Change Your Microsoft Account

5 Create a New File, Open a File, Save a File

8 Use the Ribbon, Use a Shortcut Menu, Use Keyboard Shortcuts, Customize the Ribbon, Use the Quick Access Toolbar, Customize the Quick Access Toolbar

Use the Tell me what you want to do Box, Use Enhanced ScreenTips

OBJECTIVE 5: INSTALL ADD-INS Use an Add-in from the Store

HANDS-ON EXERCISE 1:

Open a Template, Apply a Theme

Select Text, Edit Text, Use the Mini Toolbar, Use Format Painter

Cut, Copy, and Paste Text; Use the Office Clipboard

Check Spelling and Grammar 34

Insert Pictures and Graphics, Resize and Format Pictures and Graphics

HANDS-ON EXERCISE 2:

Customize Application Options, Enter Document Properties

44 Change the Document View

45 Change Margins, Change Page Orientation, Use the Page Setup Dialog Box

48 Insert a Footer, Insert a Header

Preview a File, Print a File

HANDS-ON EXERCISE 3:

Office 2016

CASE STUDY You are an administrative assistant for Spotted Begonia, a local art gallery. The gallery does a lot of community outreach and tries to help local artists develop a network of clients and supporters. Local schools are invited to bring students to the gallery for enrichment programs.

As the administrative assistant for Spotted Begonia, you are responsible for overseeing the production of documents, spreadsheets, newspaper articles, and presentations that will be used to increase public awareness of the gallery. Other clerical assistants who are familiar with Microsoft Office will prepare the promotional materials, and you will proofread, make necessary corrections, adjust page layouts, save and print documents, and identify appropri- ate templates to simplify tasks. Your experience with Microsoft Office 2016 is limited, but you know that certain fundamental tasks that are common to Word, Excel, and PowerPoint will help you accomplish your oversight task. You are excited to get started with your work!