parsons | oja | carey desjardins Microsoft® Office 365™ Excel® 2016 Comprehensive For Microsoft® Office updates, go to sam.cengage.com Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by ISBN, author, title, or keyword for materials in your areas of interest. Important notice: Media content referenced within the product description or the product text may not be available in the eBook version. Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 Want to turn C’s into A’s? Obviously, right? But the right way to go about it isn’t always so obvious. Go digital to get the grades. MindTap’s customizable study tools and eTextbook give you everything you need all in one place. Engage with your course content, enjoy the flexibility of studying anytime and anywhere, stay connected to assignment due dates and instructor notifications with the MindTap Mobile app... and most of all…EARN BETTER GRADES. TO GET STARTED VISIT WWW.CENGAGE.COM/STUDENTS/MINDTAP Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 NEW PERSPECTIVES ® ™ Microsoft Office 365 & Excel 2016 COMPREHENSIVE June Jamrich Parsons Dan Oja Patrick Carey Carol A. DesJardins St. Clair County Community College Australia • Brazil • Mexico • Singapore • United Kingdom • United States Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 New Perspectives Microsoft® Office 365™ & © 2017 Cengage Learning Excel 2016, Comprehensive ALL RIGHTS RESERVED. No part of this work covered by the copyright June Jamrich Parsons, Dan Oja, Patrick Carey, herein may be reproduced or distributed in any form or by any means, Carol A. DesJardins except as permitted by U.S. copyright law, without the prior written SVP, GM Science, Technology & Math: Balraj S. Kalsi permission of the copyright owner. Senior Product Director: Kathleen McMahon Senior Product Team Manager: Lauren Murphy For product information and technology assistance, contact us at Product Team Manager: Andrea Topping Cengage Learning Customer & Sales Support, 1-800-354-9706 Associate Product Manager: Melissa Stehler For permission to use material from this text or product, submit all Senior Director, Development: Julia Caballero requests online at www.cengage.com/permissions. Product Development Manager: Leigh Hefferon Further permissions questions can be e-mailed to [email protected] Senior Content Developers: Kathy Finnegan, Marjorie Hunt Mac users: If you’re working through this product using a Mac, some of the steps may Developmental Editor: Robin M. Romer vary. Additional information for Mac users is included with the Data Files for this Manuscript Quality Assurance: John Freitas, product. Chris Scriver Some of the product names and company names used in this book have been used for Product Assistant: Erica Chapman identification purposes only and may be trademarks or registered trademarks of their Marketing Director: Michele McTighe respective manufacturers and sellers. Marketing Manager: Stephanie Albracht Windows® is a registered trademark of Microsoft Corporation. © 2012 Microsoft. Marketing Coordinator: Cassie Cloutier Microsoft and the Office logo are either registered trademarks or trademarks of Production Director: Patty Stephan Microsoft Corporation in the United States and/or other countries. Cengage Learning is Senior Content Project Managers: Jennifer Goguen an independent entity from Microsoft Corporation and not affiliated with Microsoft in McGrail, Stacey Lamodi any manner. Manufacturing Planner: Fola Orekoya Disclaimer: Any fictional data related to persons or companies or URLs used throughout Art Director: Diana Graham this text is intended for instructional purposes only. At the time this text was published, Text Designer: Althea Chen any such data was fictional and not belonging to any real persons or companies. Composition: GEX Publishing Services Disclaimer: The material in this text was written using Microsoft Office 365 ProPlus and Cover Template Designer: Wing-Ip Ngan, Microsoft Excel 2016 running on Microsoft Windows 10 Professional and was Quality Ink Design, Inc. Assurance tested before the publication date. As Microsoft continually updates the Cover image(s): BMJ/Shutterstock.com Microsoft Office suite and the Windows 10 operating system, your software experience may vary slightly from what is presented in the printed text. Microsoft product screenshots used with permission from Microsoft Corporation. Unless otherwise noted, all clip art is courtesy of openclipart.org. Library of Congress Control Number: 2016941948 Soft-cover Edition ISBN: 978-1-305-88040-5 Loose-leaf cover Edition ISBN: 978-1-337-25147-1 Cengage Learning 20 Channel Center Street Boston, MA 02210 USA Cengage Learning is a leading provider of customized learning solutions with employees residing in nearly 40 different countries and sales in more than 125 countries around the world. Find your local representative at www.cengage.com. Cengage Learning products are represented in Canada by Nelson Education, Ltd. To learn more about Cengage Learning, visit www.cengage.com Purchase any of our products at your local college store or at our preferred online store www.cengagebrain.com Printed in the United States of America Print Number: 01 Print Year: 2016 Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 Microsoft Office 365 & Excel 2016, Comprehensive iii BRIEF CONTENTS OFFICE Productivity Apps for School and Work ................................ PA-1 EXCEL Module 1 Getting Started with Excel.................................. EX 1 Creating a Customer Order Report Module 2 Formatting Workbook Text and Data ........................ EX 65 Creating a Sales Report Module 3 Performing Calculations with Formulas and Functions .......... EX 131 Calculating Farm Yield and Revenue Module 4 Analyzing and Charting Financial Data ...................... EX 187 Preparing a Business Plan Module 5 Working with Excel Tables, PivotTables, and PivotCharts .........EX 257 TTTrraacckkiinngg SSaalleess DDaattaa Module 6 Managing Multiple Worksheets and Workbooks............... EX 327 Summarizing Rental Income Data Module 7 Developing an Excel Application........................... EX 387 Creating a Registration Receipt Module 8 Working with Advanced Functions ......................... EX 463 Analyzing Employee and Product Data Module 9 Exploring Financial Tools and Functions ..................... EX 529 Analyzing a Business Plan Module 10 Performing What-If Analyses............................. EX 595 Maximizing Profits with the Right Product Mix Module 11 Analyzing Data with Business Intelligence................... EX 659 Creating a Sales Report for a Music Store Module 12 Collaborating on a Shared Workbook...................... EX 729 Working with a Team on a Financial Report AAAppppppeeennndddiiixxx AAA WWoorrkkiinngg wwiitthh TTeexxtt FFuunnccttiioonnss aanndd CCrreeaattiinngg CCuussttoomm FFoorrmmaattss...... EX A1 Cleaning Data in a Spreadsheet Appendix B Advanced Filters, Database Functions, and Summary IFS Functions............................................ EX B1 Filtering and Summarizing Database Information Appendix C Working with Enhanced Formatting Tools................... EX C1 Formatting Seasonal Calendars for a Waterpark Innndddeeexxx ........................................................................................................................................................................................................................................REF 1 Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 iv New Perspectives Series TABLE OF CONTENTS Inserting Quizzes, Live Webpages, PRODUCTIVITY APPS FOR and Apps.............................. PPPAA--1122 SCHOOL AND WORK ..................PA-1 Sharing an Office Mix Presentation ......... PA-12 Introduction to OneNote 2016 ................ PA-2 1: Creating an Office Mix Tutorial for Creating a OneNote Notebook ............. PA-2 OneNote.............................. PPPAA--1133 Syncing a Notebook to the Cloud ........... PA-2 2: Teaching Augmented Reality with Taking Notes............................PA-3 Office Mix............................. PPPAA--1133 Converting Handwriting to Text.............PA-3 3: Marketing a Travel Destination with Office Mix......................... PPPAA--1133 Recording a Lecture ......................PA-4 1: Taking Notes for a Week.................PA-5 Introduction to Microsoft Edge............... PA-14 2: Using OneNote to Organize a Research Browsing the Web with Microsoft Edge...... PA-14 Paper..................................PA-5 Locating Information with Cortana.......... PA-14 3: Planning Your Career ...................PA-5 Annotating Webpages ................... PA-15 Introduction to Sway........................PA-6 1: Using Cortana in Microsoft Edge......... PA-16 Creating a Sway Presentation...............PA-6 2: Viewing Online News with Reading View .......................... PA-16 Adding Content to Build a Story ............ PA-7 3: Inking with Microsoft Edge.............. PA-16 Designing a Sway ........................PA-8 Publishing a Sway ........................PA-8 EXCEL MODULES Sharing a Sway ..........................PA-8 Module 1 Getting Started with Excel 1: Creating a Sway Resume................. PA-9 Creating a Customer Order Report . . . . . . . . . . . . . . . . EX 1 2: Creating an Online Sway Newsletter ....... PA-9 Session 1.1 Visual Overview: 3: Creating and Sharing a Technology The Excel Workbook........................ EX 2 Presentation ............................ PA-9 Introducing Excel and Spreadsheets............ EX 4 Introduction to Office Mix................... PA-10 Opening an Existing Workbook............. EX 4 Adding Office Mix to PowerPoint........... PA-10 Using Keyboard Shortcuts to Work Faster...... EX 6 Capturing Video Clips.................... PA-11 Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 Microsoft Office 365 & Excel 2016, Comprehensive v Getting Help............................ EX 6 Session 1.2 Visual Overview: Excel Formulas and Functions................ EX 30 Using Excel 2016 in Touch Mode ............ EX 6 Performing Calculations with Formulas......... EX 32 Exploring a Workbook....................... EX 7 Entering a Formula ...................... EX 32 Changing the Active Sheet................. EX 8 Copying and Pasting Formulas............. EX 35 Navigating Within a Worksheet ............. EX 9 Simplifying Formulas with Functions........... EX 36 Selecting a Cell Range ................... EX 11 Introducing Function Syntax............... EX 36 Closing a Workbook ....................... EX 13 Entering Functions with AutoSum .......... EX 36 Planning a Workbook ...................... EX 13 Modifying a Worksheet..................... EX 39 Starting a New Workbook................... EX 14 Moving and Copying a Cell or Range........ EX 39 Renaming and Inserting Worksheets ........ EX 15 Using the COUNT Function ............... EX 41 Moving Worksheets ..................... EX 16 Inserting a Column or Row................ EX 42 Deleting Worksheets .................... EX 16 Deleting a Row or Column ................ EX 44 Saving a Workbook...................... EX 17 Inserting and Deleting a Range ............ EX 45 Entering Text, Dates, and Numbers ........... EX 17 Using Flash Fill............................ EX 47 Entering Text........................... EX 17 Formatting a Worksheet.................... EX 48 Undoing and Redoing an Action ........... EX 19 Adding Cell Borders..................... EX 49 Editing Cell Content..................... EX 19 Changing the Font Size................... EX 50 Understanding AutoComplete............. EX 20 Printing a Workbook....................... EX 50 Displaying Numbers as Text............... EX 21 Changing Worksheet Views ............... EX 50 Entering Dates ......................... EX 22 Changing the Page Orientation ............ EX 52 Entering Numbers....................... EX 24 Setting the Scaling Options ............... EX 53 Resizing Columns and Rows ................. EX 25 Setting the Print Options ................. EX 54 Changing Column Widths................. EX 25 Viewing Worksheet Formulas ................ EX 55 Wrapping Text Within a Cell............... EX 27 Saving a Workbook with a New Filename....... EX 56 Changing Row Heights................... EX 28 Session 1.2 Quick Check.................... EX 57 Session 1.1 Quick Check .................... EX 29 Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 vi New Perspectives Series Review Assignments ....................... EX 58 Session 2.2 Visual Overview: Designing a Printout ....................... EX 94 Case Problems............................ EX 59 Calculating Averages....................... EX 96 Module 2 Formatting Workbook Text and Data Applying Cell Styles........................ EX 98 Creating a Sales Report . . . . . . . . . . . . . . . . . . . . . . . . EX 65 Copying and Pasting Formats............... EX 100 Session 2.1 Visual Overview: Copying Formats with the Formatting a Worksheet.................... EX 66 Format Painter......................... EX 100 Formatting Cell Text ....................... EX 68 Copying Formats with the Applying Fonts and Font Styles ............ EX 68 Paste Options Button................... EX 101 Applying a Font Color.................... EX 70 Copying Formats with Paste Special ....... EX 102 Formatting Text Selections Within Finding and Replacing Text and Formats...... EX 103 a Cell................................. EX 72 Working with Themes ..................... EX 105 Working with Fill Colors and Backgrounds...... EX 72 Highlighting Data with Conditional Changing a Fill Color .................... EX 73 Formats ................................ EX 107 Adding a Background Image .............. EX 74 Highlighting Cells Based on Their Values........................... EX 107 Using Functions and Formulas to Calculate Sales Data ............................... EX 75 Highlighting Cells with a Top/ Bottom Rule .......................... EX 109 Formatting Numbers....................... EX 79 Other Conditional Formatting Applying Number Formats................ EX 79 Options.............................. EX 111 Formatting Dates and Times .............. EX 83 Creating a Conditional Formatting Formatting Worksheet Cells................. EX 84 Legend .............................. EX 111 Aligning Cell Content.................... EX 84 Formatting a Worksheet for Printing ......... EX 113 Indenting Cell Content................... EX 85 Using Page Break Preview ............... EX 113 Adding Borders to Cells.................. EX 85 Defining the Print Area.................. EX 114 Merging Cells .......................... EX 87 Inserting Page Breaks................... EX 115 Rotating Cell Contents................... EX 88 Adding Print Titles ..................... EX 117 Exploring the Format Cells Dialog Box......... EX 89 Designing Headers and Footers........... EX 118 Session 2.1 Quick Check.................... EX 93 Setting the Page Margins................ EX 120 Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203 Microsoft Office 365 & Excel 2016, Comprehensive vii Session 2.2 Quick Check................... EX 122 Filling a Series......................... EX 156 Review Assignments ...................... EX 123 Exploring Auto Fill Options .............. EX 158 Case Problems........................... EX 125 Filling Formulas........................ EX 159 Exploring Cell References.................. EX 161 Module 3 Performing Calculations with Formulas and Functions Understanding Relative References ........ EX 161 Calculating Farm Yield and Revenue . . . . . . . . . . . . . EX 131 Understanding Absolute References ....... EX 162 Session 3.1 Visual Overview: Understanding Mixed References ......... EX 163 Formulas and Functions ................... EX 132 Changing Cell References in a Formula ..... EX 165 Making Workbooks User-Friendly............ EX 134 Summarizing Data with the Quick Documenting Formulas.................. EX 135 Analysis Tool ............................ EX 167 Using Constants in Formulas ............. EX 136 Working with Dates and Date Functions....... EX 169 Identifying Notes, Input Values, and Using Lookup Functions ................... EX 171 Calculated Values ...................... EX 140 Finding an Exact Match with the VLOOKUP Using Excel Functions..................... EX 141 Function ............................. EX 171 Understanding Function Syntax........... EX 141 Working with Logical Functions ............. EX 174 Entering the COUNT function ............ EX 142 Session 3.2 Quick Check................... EX 180 Nesting the ROUND and AVERAGE Review Assignments ...................... EX 181 Functions............................. EX 143 Case Problems........................... EX 182 Using the Function Library and the Insert Function Dialog Box.................... EX 145 Module 4 Analyzing and Charting Financial Data Preparing a Business Plan. . . . . . . . . . . . . . . . . . . . . . EX 187 Performing What-If Analyses................ EX 149 Using Trial and Error.................... EX 149 Session 4.1 Visual Overview: Chart Elements .......................... EX 188 Using Goal Seek ....................... EX 150 Introduction to Financial Functions........... EX 190 Interpreting Error Values................... EX 151 Using the PMT Function................. EX 191 Session 3.1 Quick Check................... EX 153 Getting Started with Excel Charts ........... EX 195 Session 3.2 Visual Overview: Creating a Pie Chart.................... EX 197 Cell References and Formulas............... EX 154 Selecting the Data Source ............... EX 197 AutoFilling Formulas and Data .............. EX 156 Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203