Excel 2013 by Wilfrido Nájera

Microsoft速 Official Academic Course

Microsoft速 Excel 2013

Credits CONTENT DEVELOPMENT Editor Bryan Gambrel Publisher Don Fowley Director of Sales Mitchell Beaton Technical Editor Joyce Nielsen Executive Marketing Manager Chris Ruel Assistant Marketing Manager Debbie Martin

Educational Technology Consulting (ETC)

General Direction

Eduardo A. Valencia González

Head Coordinator Claudia Laura Limón Luna Edna Margarita Arruti Hernández Project Management Adriana Margarita García Muciño Verónica Carmona Alcántara Editorial production & Cover Design Wilfrido Eduardo Najéra Marín

Copyright © 2014. All rights reserved. Reproduction in whole or part of this work by any means (electronic, mechanical, photocopying, recording, scanning or otherwise), procedure or storage system without the express prior written permission and consent of the publisher or Educational Technology Consulting. Any form of unauthorized use will be prosecuted under the provisions of the federal law of copyright. Under the Copyright Act. Microsoft, ActiveX, Excel, InfoPath, Microsoft Press, MSDN, OneNote, Outlook, PivotChart, PivotTable, PowerPoint, SharePoint, SQL Server, Visio, Windows, Windows Mobile, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. The book expresses the author’s views and opinions. The 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. ISBN pending for this release. Authorized translation and adaptation into Spanish, the English edition published by John Wiley & Sons, Inc. All rights reserved.

Foreword from the Publisher Wiley’s publishing vision for the Microsoft Official Academic Course series is to provide students and instructors with the skills and knowledge they need to use Microsoft technology effectively in all aspects of their personal and professional lives. Quality instruction is required to help both educators and students get the most from Microsoft’s software tools and to become more productive. To accomplish this mission, is to make our instructional programs trusted educational companions for life. To accomplish this mission, Wiley and Microsoft have partnered to develop the highest quality educational programs for Information Workers, IT Professionals, and Developers. Materials created by this partnership carry the brand name “Microsoft Official Academic Course,” assuring minstructors and students alike that the content of these textbooks is fully endorsed by Microsoft, and that they provide the highest quality information and instruction on Microsoft products. The Microsoft Official Academic Course textbooks are “Official” in still one more way— they are the officially sanctioned courseware for Microsoft IT Academy members. The Microsoft Official Academic Course series focuses on workforce development. These programs are aimed at those students seeking to enter the workforce, change jobs, or embark on new careers as information workers, IT professionals, and developers. Microsoft Official Academic Course programs address their needs by emphasizing authentic workplace scenarios with an abundance of projects, exercises, cases, and assessments. The Microsoft Official Academic Courses are mapped to Microsoft’s extensive research and jobtask analysis, the same research and analysis used to create the Microsoft Office Specialist (MOS) exams. The textbooks focus on real skills for real jobs. As students work through the projects and exercises in the textbooks they enhance their level of knowledge and their ability to apply the latest Microsoft technology to everyday tasks. These students also gain resume-building credentials that can assist them in finding a job, keeping their current job, or in furthering their education. The concept of life-long learning is today an utmost necessity. Job roles, and even whole job categories, are changing so quickly that none of us can stay competitive and productive without continuously updating our skills and capabilities. The Microsoft Official Academic Course offerings, and their focus on Microsoft certification exam preparation, provide a means for people to acquire and effectively update their skills and knowledge. Wiley supports students in this endeavor through the development and distribution of these courses as Microsoft’s official academic publisher. Joe Heider Senior Vice President, Wiley Global Education

Why MOS Certification?

Microsoft Office Specialist (MOS) 2013 is a valuable credential that recognizes the desktop computing skills needed to use the full features and functionality of the Microsoft Office 2013 suite. In the worldwide job market, Microsoft Office Specialist is the primary tool companies use to validate the proficiency of their employees in the latest productivity tools and technology, helping them select job candidates based on globally recognized standards for verifying skills. The results of an independent research study show that businesses with certified employees are more productive compared to noncertified employees and that certified employees bring immediate value to their jobs. In academia, as in the business world, institutions upgrading to Office 2013 may seek ways to protect and maximize their technology investment. By offering certification, they validate that decision—because powerful Office 2013 applications such as Word, Excel and PowerPoint can be effectively used to demonstrate increases in academic preparedness and workforce readiness. Individuals seek certification to increase their own personal sense of accomplishment and to create advancement opportunities by establishing a leadership position in their school or department, thereby differentiating their skill sets in a competitive college admissions and job market

About the author Kim Lindros Kim Lindros is a full-time writer, content developer, and project manager who has worked around high technology and computing since the early 1990s. She co-authored Introduction to Computers with Windows XP and Office 2007 (Kaplan, 2013), MTA Microsoft Technology Associate Exam 98-349 Windows Operating System Fundamentals (Wiley, 2012), PC Basics with Windows 7 and Office 2010 (Jones & Bartlett Learning, 2010), and numerous courses focused on Windows and Microsoft Office.

Rick Winter Rick Winter has published over 50 computer and children’s books and articles and has trained over 3,000 adults on computer software. He has a Bachelor’s of Arts degree in International Environment from Colorado College and a Master of Public Administration from the University of Colorado at Denver. Rick has received awards that include Clear Creek School District Leadership and Service Award, Information Systems Trainers Distinguished Service Award, Rocky Mountain Chapter Society for Technical Communication Distinguished Award, Phi Beta Kappa, Eagle Scout, and is listed in “Who’s Who in America.”

Jennifer Fulton Jennifer Fulton, Senior Partner of Ingenus, LL C and iVillage’s former “Computer Coach,” is an experienced technical writer with over 20 years in the business. Jennifer has written and edited hundreds of online course materials for both college and middle school audience, and authored over 150 bestselling computer books for beginner, intermediate, and advanced users, including Outlook 2010 All-in-One for Dummies, Windows 7 eLearning Kit for Dummies, and Outlook 2007 All-in-One Desk Reference for Dummies. Jennifer is also a computer trainer for corporate personnel, teaching a variety of classes on Windows, Microsoft Office, Paint Shop Pro, Photoshop Elements and others.

Preface Preface Welcome to the Microsoft Official Academic Course (MOAC) program for Microsoft Office 2013. MOAC represents the collaboration between Microsoft Learning and John Wiley & Sons, Inc. publishing company. Microsoft and Wiley teamed up to produce a series of textbooks that deliver compelling and innovative teaching solutions to instructors and superior learning experiences for students. Infused and informed by in-depth knowledge from the creators of Microsoft Office and Windows, and crafted by a publisher known worldwide for the pedagogical quality of its 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 2013 system and creator of the Microsoft Office Specialist (MOS) exams (www.microsoft.com/learning/ mcp/ mcts), 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 The 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 tools for an instructor to be ready to teach the course. The 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 curriculum 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 development; and a great set of tools for delivering instruction in the classroom and lab. All are important to the smooth delivery of an interesting course on Microsoft software, and all are provided with the MOAC program. We think about the model below as a gauge for ensuring that we completely support you in your goal of teaching a great course. As you evaluate your instructional materials options, you may wish to use the model for comparison purposes with available products.

Instructor Readines Student Software Learning Validation

Classroom Tools

Student Assessment

Conventions and Features Used in This Book This book uses particular fonts, symbols, and heading conventions to highlight important information or to call your attention to special steps. For more information about the features in each lesson, refer to the Illustrated Book Tour section.

Convention The Bottom Line

This feature provides a brief summary of the material to be covered in the section that follows.

88 Certification Ready - How do you start Excel? 1.1.1

This feature signals the point in the text where a specific certification objective is covered. It provides you with a chance to check your understanding of that particular MOS objective and, if necessary, review the section of the lesson where it is cover.

`` Take Note

Reader aids appear in shaded boxes found in your text. Take Note provides helpful hints related to particular tasks or topics.

Notes the sequence of instructions for developing a practice in online learnigplatform. Ă&#x201D;Ă&#x201D; Another Way

MM Troubleshooting

ALT+Tab The new workbook contains one worksheet

Meaning

Another Way provides an alternative procedure for accomplishing a particular task. Reader aid that points out things to watch out for or avoid. A plus sign (+) between two key names means that you must press both keys at the same time. Keys that you are instructed to press will appear in bold. Key terms appear in bold.

Illustrated Book Tour

Pedagogical Features The Microsoft® Official Academic Course (MOAC) is designed to meet the learning objectives of the Microsoft® Office Specialist (MOS) certification exam, which includes three elements for its deployment. 1. Student book: it includes the theory and the process to be followed for the development of the technological skills of the application, as well as reinforcement exercises for each lesson. The structure of the book is divided as follows: • Mini projects: each lesson is divided in Mini projects, which integrate a number of steps that correspond to the technological skills of general knowledge and certification; these skills are necessary to achieve the certification objectives. General Knowledge skills are a prerequisite for the development of the Certification skills. Certification skills are those that cover the objectives of the MOS certification exam. 2. Learning platforms: it comprises two modes. The first one is worked individually and locally through the installation of an application, where the user will have the opportunity to perform the Mini projects mentioned in the book. It also contains multimedia resources for the feedback and progress. The second mode is online, through a website that allows the access to different resources that complement the certification process. 3. GMetrix simulator: is a tool that offers the user an environment similar to the certification exam, and allows the reinforcement of the certification skills prior to the examination. An outline with the elements of the course is presented below: MOAC course

Student book

Mini projects

General Knowledge skills

Certification skills

Learning platform

Simulator

MOAC

Simulation exam in GMetrix

CIIDTE

Contents Lesson 1: Overview....................................................... 13

Lesson 3: Using Office Backstage.............................. 45

• Mini project 1........................................... 15

• Mini project 1........................................... 47

ÔÔ Start Excel................................................... 15 ÔÔ Use the Onscreen Tools.............................. 16 ÔÔ Navigate the Ribbon ............................... 17 ÔÔ Open Backstage View................................. 18 ÔÔ Use the Microsoft Office FILE Tab and Backstage View................................................. 18 ÔÔ Change Excel’s View................................... 19 ÔÔ Split the Window........................................ 19 ÔÔ Open a New Window.................................. 20 ÔÔ Open an Existing Workbook....................... 20 ÔÔ Open a Workbook from Your OneDrive..... 21 ÔÔ Use the Help System.................................. 22 • Mini project 2........................................... 23 ÔÔ Navigate a Worksheet................................. 23 ÔÔ Navigate Data with the Go To Command.. 23 • Knowledge Assessment.......................... 24

Lesson 2: Working with Microsoft® Excel 2013....... 27 • Mini project 1........................................... 29 ÔÔ Create a Workbook from Scratch............... 29 ÔÔ Switch Between Open Workbooks............ 29 ÔÔ Name and Save a Workbook ..................... 30 ÔÔ Save to Your OneDrive............................... 30 ÔÔ Save a Workbook Under a Different Name........................................................... 30 ÔÔ Save a Workbook in a Previous Excel Format.............................................................. 31 ÔÔ Save in Different File Formats................... 31 • Mini project 2........................................... 32 ÔÔ Enter Basic Data in a Worksheet................ 32 ÔÔ Change the Column Width......................... 32 • Mini project 3........................................... 33 ÔÔ Edit a Cell’s Contents.................................. 33 ÔÔ Delete and Clear a Cell’s Contents............ 34 ÔÔ Enter Labels and Use AutoComplete........ 35 ÔÔ Enter Numeric Values................................. 36 ÔÔ Enter Dates.................................................. 36 ÔÔ Fill a Series with Auto Fill........................... 37 • Mini project 4........................................... 39 ÔÔ Fill Cells with Flash Fill............................... 39 • Mini project 5........................................... 39 ÔÔ Copy a Data Series with the Mouse.......... 39 ÔÔ Move a Data Series with the Mouse......... 40 ÔÔ Copy and Paste Data.................................. 40 ÔÔ Cut and Paste Data..................................... 41 ÔÔ Assign Keywords........................................ 41 • Knowledge Assessment.......................... 42

ÔÔ Access Backstage View............................... 47

• Mini project 2........................................... 47 ÔÔ Print and Preview a Document.................. 47 ÔÔ Use Quick Print to Print a Worksheet......... 48 ÔÔ Set the Print Area........................................ 48 • Mini project 3........................................... 48 ÔÔ Print Selected Worksheets.......................... 48 ÔÔ Print Selected Workbooks.......................... 49 • Mini project 4........................................... 49 ÔÔ Apply Print Options.................................... 49 ÔÔ Change a Printer......................................... 49 • Mini project 5........................................... 50 ÔÔ Customize the Quick Access Toolbar......... 50 ÔÔ Customize the Ribbon................................ 50 ÔÔ Customize the Excel Default Settings....... 51 ÔÔ Reset Default Settings, the Ribbon, and Quick Access Toolbar ...................................... 51 • Mini project 6........................................... 52 ÔÔ Select a Template from the New Tab......... 52 ÔÔ Search for Additional Templates................ 52 • Knowledge Assessment.......................... 53

Lesson 4: Using Basic Formulas................................. 55 • Mini project 1........................................... 57 ÔÔ Display Formulas........................................ 57 ÔÔ Understand Order of Operations.............. 58 • Mini project 2........................................... 59 ÔÔ Create a Formula that Performs Addition....................................................... 59 ÔÔ Create a Formula that Performs Subtraction.................................................. 60 ÔÔ Create a Formula that Performs Multiplication.............................................. 60 ÔÔ Create a Formula that Performs Division........................................................ 60 • Mini project 3........................................... 60 ÔÔ Use Relative Cell References in a Formula....................................................... 61 ÔÔ Use an Absolute Cell Reference in a Formula............................................................ 61 ÔÔ Use a Mixed Cell Reference in a Formula.62 ÔÔ Refer to Data in Another Worksheet.......... 62 ÔÔ Reference Data in Another Workbook....... 63 • Mini project 4........................................... 64 ÔÔ Name a Range of Cells............................... 64 ÔÔ Change the Size of a Range....................... 65

ÔÔ Create a Formula that Operates on a Named Range............................................ 66 ÔÔ Keep Track of Named Ranges..................... 66 • Knowledge Assessment.......................... 67

Lesson 5: Using Functions........................................... 69 • Mini project 1........................................... 71 ÔÔ Explore Functions....................................... 71 ÔÔ Explore Dates.............................................. 72 ÔÔ Use the TODAY Function............................ 72 ÔÔ Use the NOW Function............................... 72 • Mini project 2........................................... 73 ÔÔ Use the SUM Function............................... 73 ÔÔ Use the COUNT Function........................... 74 ÔÔ Use the COUNTA Function........................ 74 ÔÔ Use the AVERAGE Function....................... 74 ÔÔ Use the MIN Function................................. 74 ÔÔ Use the MAX Function............................... 75 • Mini project 3........................................... 75 ÔÔ Use the PMT Function................................ 75

• Mini project 4........................................... 76 ÔÔ Select and Create Ranges for Subtotaling................................................. 76 ÔÔ Build Formulas to Subtotal........................ 76 ÔÔ Modify Ranges for Subtotaling................. 77 • Mini project 5........................................... 77 ÔÔ Review an Error Message.......................... 77 ÔÔ Trace a Formula and Remove Trace Arrows........................................................ 78 • Mini project 6........................................... 78 ÔÔ Print Formulas............................................. 78 • Knowledge Assessment.......................... 79

Lesson 6: Formatting Cells and Ranges..................... 81 • Mini project 1........................................... 83 ÔÔ Insert Cells into a Worksheet...................... 83 ÔÔ Delete Cells from a Worksheet................... 83 • Mini project 2........................................... 84 ÔÔ Align Cell Contents..................................... 84 ÔÔ Indent Cell Contents................................... 85 ÔÔ Change Text Orientation............................. 85 ÔÔ Choose Fonts and Font Sizes..................... 86 ÔÔ Change Font Color...................................... 86 ÔÔ Apply Special Character Attributes........... 87 ÔÔ Fill Cells with Color..................................... 87 • Mini project 3........................................... 88 ÔÔ Apply Number Formats.............................. 88 ÔÔ Wrap Text in a Cell...................................... 90 ÔÔ Merge and Split Cells................................. 90 ÔÔ Place Borders around Cells........................ 91 • Mini project 4........................................... 91 ÔÔ Use the Format Painter to Copy Formatting................................................... 91 ÔÔ Understand Paste Special Options............ 92

• Mini project 5........................................... 92 ÔÔ Apply Cell Styles......................................... 92 ÔÔ Customize a Cell Style................................ 93 • Mini project 6........................................... 94 ÔÔ Insert a Hyperlink in a Cell......................... 94 ÔÔ Use a Hyperlink........................................... 94 ÔÔ Remove a Hyperlink from a Cell................ 94 • Mini project 7........................................... 95 ÔÔ Apply a Specific Conditional Format........ 95 ÔÔ Apply Multiple Conditional Formatting Rules............................................................ 95 ÔÔ Use the Rules Manager to Apply Conditional Formats................................... 96 ÔÔ Clear a Cell’s Formatting............................ 96 • Knowledge Assessment.......................... 97

Lesson 7: Formatting Worksheets.............................. 99 • Mini project 1........................................... 101 ÔÔ Insert and Delete Rows and Columns....... 101 ÔÔ Modify Row Height and Column Width.... 101 ÔÔ Format an Entire Row or Column.............. 102 ÔÔ Hide or Unhide a Row or Column............. 103 ÔÔ Transpose Rows or Columns..................... 103 • Mini project 2........................................... 104 ÔÔ Choose a Theme for a Workbook............... 104 ÔÔ Customize a Theme by Selecting Colors... 105 ÔÔ Customize a Theme by Selecting Fonts and Effects................................................... 105 • Mini project 3........................................... 106 ÔÔ Format a Worksheet Background.............. 106 ÔÔ View and Print a Worksheet’s Gridlines.... 107 ÔÔ View and Print Column and Row Headings...................................................... 107 • Mini project 4........................................... 107 ÔÔ Add Page Numbers to a Worksheet.......... 107 ÔÔ Insert a Predeﬁned Header or Footer........ 108 ÔÔ Add Content to a Header or Footer........... 108 ÔÔ Insert a Watermark...................................... 109 ÔÔ Repeat Headers and Footers..................... 109 • Mini project 5........................................... 109 ÔÔ Add and Move a Page Break...................... 110 ÔÔ Set Margins................................................. 110 ÔÔ Set a Worksheet’s Orientation.................... 111 ÔÔ Scale a Worksheet to Fit on a Printed Page............................................................. 111 • Knowledge Assessment.......................... 112

Lesson 8: Managing Worksheets............................... 115 • Mini project 1........................................... 117 ÔÔ Copy a Worksheet....................................... 117 ÔÔ Rename a Worksheet.................................. 118 ÔÔ Reposition the Worksheets in a Workbook.................................................... 118 ÔÔ Change the Color of a Worksheet Tab....... 118

ÔÔ Hide and Unhide a Worksheet................... 119 ÔÔ Insert a New Worksheet into a Workbook.119 ÔÔ Delete a Worksheet from a Workbook....... 120 ÔÔ Work with Multiple Worksheets in a Workbook.................................................... 121 ÔÔ Hide and Unhide Worksheet Windows in a Workbook.................................................... 122 ÔÔ Use Zoom and Freeze to Change the Onscreen View............................................ 123 ÔÔ Locate Data with the Find Command........ 123 ÔÔ Replace Data with the Replace Command.................................................... 124 • Knowledge Assessment.......................... 125

Lesson 9: Working whit Data and Macros................ 127 • Mini project 1........................................... 130 ÔÔ Open a Non-Native File Directly in Excel.. 130

• Mini project 2........................................... 130 ÔÔ Get External Data........................................ 131 • Mini project 3........................................... 131 ÔÔ Append Data to a Worksheet..................... 131

• Mini project 4........................................... 132 ÔÔ Restrict Cell Entries to Certain Data Types........................................................... 132 ÔÔ Allow Only Specific Values to Be Entered in Cells......................................................... 132 ÔÔ Remove Duplicate Rows from a Worksheet................................................... 133 ÔÔ Sort Data on a Single Criterion.................. 133 ÔÔ Sort Data on Multiple Criteria.................... 134 ÔÔ Sort Data Using Cell Attributes.................. 134 ÔÔ Use AutoFilter............................................. 135 ÔÔ Create a Custom AutoFilter........................ 136 ÔÔ Filter Data Using Cell Attributes................ 137 ÔÔ Group and Ungroup Data.......................... 137 • Mini project 5........................................... 138 ÔÔ Auto-Outline Data....................................... 138 ÔÔ Collapse Groups of Data in an Outline..... 138 • Mini project 6........................................... 139 ÔÔ Subtotal Data in Outlines........................... 139 • Mini project 7........................................... 139 ÔÔ Format a Table with a Quick Style.............. 140 ÔÔ Remove Styles from a Table....................... 140 ÔÔ Define a Title for a Table............................. 142 ÔÔ Use the Total Row Command in a Table.... 142 ÔÔ Add and Remove Rows and Columns in a Table.......................................................... 143 ÔÔ Filter Records in a Table.............................. 143 • Mini project 8........................................... 144 ÔÔ Sort Data on Multiple Columns in a Table............................................................ 144 ÔÔ Change Sort Order in a Table..................... 145 ÔÔ Remove Duplicates in a Table.................... 145 • Mini project 9........................................... 145 ÔÔ Use a Slicer to View Table Data.................. 145

• Mini project 10......................................... 146

ÔÔ Convert a Table to a Range......................... 146

• Mini project 11......................................... 146 ÔÔ Record a Basic Macro................................. 147 ÔÔ Run a Macro................................................ 147 ÔÔ Manage Macro Security............................. 148 • Knowledge Assessment.......................... 149

Lesson 10: Using Advance Formulas......................... 151 • Mini project 1........................................... 154 ÔÔ Use the SUMIF Function............................ 154 ÔÔ Use the SUMIFS Function.......................... 155 ÔÔ Use the COUNTIF Function........................ 156 ÔÔ Use the COUNTIFS Function..................... 156 ÔÔ Use the AVERAGEIF Function.................... 157 ÔÔ Use the AVERAGEIFS Function.................. 157 • Mini project 2........................................... 158 ÔÔ Use the VLOOKUP Function....................... 159 ÔÔ Use the HLOOKUP Function...................... 161 ÔÔ Use the IF Function..................................... 161 ÔÔ Use the AND Function................................ 162 ÔÔ Use the OR Function................................... 162 ÔÔ Use the NOT Function................................ 162 ÔÔ Use the IFERROR Function......................... 163 • Mini project 3........................................... 163 ÔÔ Convert Text to Columns............................ 164 ÔÔ Use the LEFT Function............................... 164 ÔÔ Use the RIGHT Function............................. 164 ÔÔ Use the MID Function................................. 164 ÔÔ Use the TRIM Function............................... 165 ÔÔ Use the PROPER Function.......................... 165 ÔÔ Use the UPPER Function............................ 165 ÔÔ Use the LOWER Function........................... 166 ÔÔ Use the CONCATENATE Function............. 166 ÔÔ Use the FIND Function............................... 166 ÔÔ Use the SUBSTITUTE Function................. 167 • Knowledge Assessment.......................... 168

Lesson 11: Securing and Sharing Workbooks......... 171 • Mini project 1........................................... 173 ÔÔ Protect a Worksheet.................................... 173 ÔÔ Protect a Workbook..................................... 174 • Mini project 2........................................... 175 ÔÔ Allow Multiple Users to Edit a Workbook Simultaneously........................................... 175 • Mini project 3........................................... 176 ÔÔ Use the Document Inspector..................... 176 ÔÔ Mark a Document as Final......................... 177 ÔÔ Distribute a Workbook by Email From Excel............................................................. 177 ÔÔ Distribute a Worksheet as an Email Message...................................................... 178 ÔÔ Distribute a Workbook from within your Email Program............................................ 178 • Mini project 4........................................... 178 ÔÔ Share a Workbook in the Cloud................. 178

• Mini project 5........................................... 179

• Mini project 3........................................... 213

ÔÔ Turn Track Changes On and Off................. 179 ÔÔ Set Track Change Options.......................... 179 ÔÔ Insert Tracked Changes............................... 180 ÔÔ Delete Your Changes................................... 181 ÔÔ Accept Changes from Another User......... 181 ÔÔ Reject Changes from Another User........... 182 ÔÔ Remove Shared Status from a Workbook.182 • Mini project 6........................................... 183

ÔÔ Delete Graphics.......................................... 213 ÔÔ Move Graphics............................................ 214 ÔÔ Copy a Graphic........................................... 214 • Mini project 4........................................... 215

ÔÔ Insert a Comment....................................... 183 ÔÔ View a Comment........................................ 183 ÔÔ Edit a Comment.......................................... 184 ÔÔ Delete a Comment...................................... 184 ÔÔ Print Comments in a Workbook................. 184 • Knowledge Assessment.......................... 185

Lesson 12: Creating Charts.......................................... 187 • Mini project 1........................................... 189 ÔÔ Select Data to Include in a Chart............... 191 ÔÔ Move a Chart............................................... 192 ÔÔ Choose the Right Chart for Your Data....... 192 ÔÔ Use Recommended Charts........................ 193 ÔÔ Create a Bar Chart....................................... 194 ÔÔ Format a Chart with a Quick Style............. 195 ÔÔ Format a Chart with Quick Layout............. 196 ÔÔ Edit and Add Text on Charts....................... 198 ÔÔ Format a Data Series.................................. 198 ÔÔ Change the Chart’s Border Line................. 198 ÔÔ Modify a Chart’s Legend............................ 199 ÔÔ Add Elements to a Chart............................ 199 ÔÔ Delete Elements from a Chart.................... 199 ÔÔ Add Additional Data Series........................ 200 ÔÔ Resize a Chart.............................................. 200 ÔÔ Choose a Different Chart Type................... 201 ÔÔ Switch Between Rows and Columns in Source Data................................................. 201 ÔÔ Add a Chart or Sparklines.......................... 201 ÔÔ Work with Totals ......................................... 202 ÔÔ Apply Conditional Formatting .................. 202 • Mini project 2........................................... 202 ÔÔ Create a Basic PivotTable........................... 203 ÔÔ Add a PivotChart ........................................ 203 • Knowledge Assessment.......................... 204

Lesson 13: Adding Pictures and Shapes to a Worksheets..................................................................... 207 • Mini project 1........................................... 209 ÔÔ Insert a Picture from a File......................... 209 ÔÔ Insert Office.com Clip Art........................... 210 ÔÔ Insert an Image via a Bing Image Search.210 ÔÔ Insert an Image from Your OneDrive......... 210 • Mini project 2........................................... 210 ÔÔ Add a Shape................................................ 211 ÔÔ Draw Lines.................................................. 211 ÔÔ Add Text to a Shape.................................... 212 ÔÔ Add a Text Box............................................. 212 ÔÔ Use WordArt................................................ 213

ÔÔ Apply a Style to a Shape............................ 215 ÔÔ Resize a Graphic......................................... 215 ÔÔ Rotate a Graphic......................................... 216 ÔÔ Control Stacking Order when Graphics Overlap........................................................ 216 • Mini project 5........................................... 217 ÔÔ Make Corrections to a Graphic.................. 217 ÔÔ Change the Color of an Image................... 219 ÔÔ Apply Artistic Effects.................................. 219 ÔÔ Apply Picture Styles................................... 220 ÔÔ Reset a Picture............................................ 220 ÔÔ Modify a Picture’s Properties..................... 220 • Mini project 6........................................... 221 ÔÔ Capture a Screenshot................................. 221

• Mini project 7........................................... 222 ÔÔ Create a Flowchart...................................... 222 ÔÔ Create an Organization Chart.................... 222 • Knowledge Assessment.......................... 223

Microsoft® Office Specialist (MOS) Skills for Excel 2013: Exam 77-420.......................................................... 225 Excel 2013 Glossary...................................................... 229

Lesson 1

Overview Skill Matrix Technological Skills

Section Mini project 1

Start Excel

Certification

Use the Onscreen Tools

Certification

Navigate the Ribbon

General Knowledge

Open Backstage View

General Knowledge

Use the Microsoft Office FILE Tab and Backstage View

General Knowledge

Change Excelâ&#x20AC;&#x2122;s View

Certification

Split the Window

Certification

Open a New Window

General Knowledge

Open an Existing Workbook

General Knowledge

Open a Workbook from Your OneDrive

General Knowledge

Use the Help System

General Knowledge Mini project 2

Navigate a Worksheet

Certification

Navigate Data with the Go To Command

Certification

Lesson 2 - Overview

Software Orientation Microsoft® Excel’s Opening Screen Microsoft Office Excel 2013 provides powerful tools that enable users to organize, analyze, manage, and share information easily. The foundation of Excel and locations where you do your work are cells, rows, and columns within a worksheet, and worksheets as part of a workbook. Many of the tools you use whileworkink in Excel are located on the ribbon, running across the top of the window. The ribbon is organized into task-oriented command tabs. Each tab is divided into task-specific command groups appropriate to the type of work the user is currently performing. Because you can customize the ribbon and new options might appear, such as the DEVELOPER, ADD-IN, and POWERPIVOT tabs depending on your setup, your screen might appear different than Figure 1-1. a

k e f i

Figure 1-1 - Excel’s Blank workbook. A) FILE tab / B) Quick Access Toolbar / C) Command tabs / D) Title bar / E) Name Box / F) Active cell / G) Row labels / H) Worksheet tab / I) Formula bar / J) Column labels / K) Ribbon.

Lesson 2 - Overview

BMini project 1

Starting Excel

The Bottom Line To work efficiently in Microsoft Excel, you need to become familiar with its primary user interface. You can open Microsoft Excel 2013 in Windows 8 by moving to the bottom left corner of your screen, clicking on Start, right-clicking a blank area of the Start screen, clicking All apps, and clicking Excel 2013.

Excel opens to a list of templates and in most cases you choose Blank workbook or open a previous file. A workbook, or spreadsheet file, is shown in Figure 1-1. Think of a workbook as a physical book with many pages. The filename (Book1) and the program name (Excel) appear in the title bar at the top of the screen. Book1 (or Book2, Book3, and so on.) is a temporary title for your workbook until you save the workbook with a name of your choice. The new workbook contains one worksheet (Sheet1) by default—similar to the first page in a book—where you enter information. If a workbook has more pages (or worksheets), you use the sheet tabs that are located just above the Status bar and are identified as Sheet1, Sheet2, and Sheet3. You can rename worksheets to identify their content and add worksheets with the New sheet (+) button as needed.

Opening Excel In this exercise, you learn to use the Start screen to open Excel and view the new workbook’s first blank worksheet. 88 Certification Ready - How do you start Excel? 1.1.1

@@ Start Excel Use the learning platform Course: Excel

`` Take Note If you use Excel repeatedly, you will want to pin your application to the Start screen. From the All Apps screen, right-click the app, and choose Pin to Start. You can also choose Pin to taskbar to allow you to click the icon on the bottom of the Windows Desktop screen to start Excel. A worksheet is a grid composed of rows, columns, and cells. Each worksheet column starts at the top of the worksheet and goes to the bottom of the worksheet and is identified by a letter. Each row starts at the left edge of the worksheet and continues to the right and is identified by a number. Each box, or cell, on the grid is identified by the intersection of a column and a row. Thus, the first cell in an open worksheet is A1. You enter information by typing it into the selected or active cell, which is outlined by a bold rectangle. This is also called the current or highlighted cell.

Lesson 2 - Overview

Working in the excel window

The Bottom Line When you launch Excel and click Blank workbook, the program opens a new workbook and displays a blank worksheet. You just learned about some of the most important components of the Excel worksheet such as rows, columns, and cells. In this section, you explore the Excel window and learn to identify and customize the Quick Access Toolbar, the ribbon, and other important onscreen tools and components. You also learn to open and use Backstage view, Microsoft’s replacement for the Office button and File menu commands found in previous versions of Office.

Using the Onscreen Tools The Quick Access Toolbar gives you fast and easy access to the tools you use most often in any given Excel session. It appears on the left side of the title bar, above the ribbon (although you can move the toolbar below the ribbon if you want it closer to your work area). You can add and remove commands to and from the toolbar so that it contains only those commands you use most frequently. In this lesson, you learn to move and customize the Quick Access Toolbar by adding and removing commands. You also learn how to use ScreenTips, which are small, onscreen rectangles that display descriptive text when you rest the pointer on a command or control. 88 Certification Ready - How do you manipulate the Quick Access Toolbar? 1.4.3

@@ Use the Onscreen Tools Use the learning platform Course: Excel

`` Take Note Use ScreenTips to remind you of a command’s function. Enhanced ScreenTips display in a larger rectangle that contains more descriptive text than a ScreenTip. Most Enhanced ScreenTips contain a link to a Help topic. ÔÔ Another Way To add a command to the Quick Access Toolbar, you can also right-click any icon on the ribbon and then click Add to Quick Access Toolbar.

`` Take Note To add commands to the Quick Access Toolbar that do not appear in the drop-down list, click More Commands on the drop-down list. The Excel Options dialog box opens. You can also right-click the Quick Access Toolbar or any ribbon tab and select Customize Quick Access Toolbar to open the Excel Options dialog box. By default, the Quick Access Toolbar contains the Save, Undo, and Redo commands. As you work in Excel, customize the Quick Access Toolbar so that it contains the commands you use most often. Do not, however, remove the Undo and Redo commands. These commands are not available on the ribbon’s command tabs.

Lesson 2 - Overview

Navigating the Ribbon The ribbon organizes tools and commands into an intuitive and useful interface. Having commands visible on the work surface enables you to work quickly and efficiently and is especially helpful for new users. The ribbon in Microsoft Office Excel 2013 is made up of a series of tabs, each related to specific tasks that users perform in Excel. By pressing and releasing the Alt key, you can reveal Keytips, or small badges displaying keyboard shortcuts for specific tabs and commands on the ribbon and Quick Access Toolbar. In this exercise, you learn how to navigate between Excel tabs and use their commands and Keytips. `` Take Note Keytips are sometimes also referred to as hotkeys. Note, however, that when you use the Microsoft Office 2013 Help, no reference is listed for hotkeys. Only Keytips is referenced. Within each tab on the ribbon, commands are organized into related tasks called command groups, or just groups, as shown in Figure 1-2. For example, consider the HOME tab. When the HOME tab is displayed, you see the Clipboard group, which contains the command buttons to cut, copy, and paste data. These commands allow you to revise, move, and repeat data within a worksheet. Similarly, you can use commands in the Editing group to fill adjacent cells, sort and filter data, find specific data in a worksheet, and perform other tasks related to editing worksheet data. Some of the commands have an options arrow that displays additional options for the command. On some of the command groups are icons in the bottom-right corner of the group. These Dialog Box Launchers open a dialog box and give more options than display on the ribbon.

Figure 1-2 - HOME tab command groups. A) Clipboard group / B)Clipboard group Dialog Box Launcher / C) Font group / D) Alignment group / E) Number group / F) Styles group / G) Cells group / H) Editing group.

@@ Navigate the Ribbon Use the learning platform Course: Excel

Lesson 2 - Overview

Introducing Office Backstage The most noticeable new feature in Microsoft Office 2010 and 2013 is Backstage. The Backstage view shows you behind-the-scenes options to manage files such as opening, saving, printing, and documenting files. Backstage view is covered in more depth in Lesson 3, but you need to know how to access it for simple commands in this lesson.

@@ Open Backstage View Use the learning platform Course: Excel MM Troubleshooting Backstage defaults to different commands depending on what you’re doing. When you are working in a blank workbook or with no workbooks open, Backstage defaults to the Open command, which shows different options for opening files. If you have started typing in a document, Backstage defaults to Info.

Using the Microsoft® Office FILE Tab and Backstage View In Microsoft Office 2013, clicking the FILE tab takes you to Backstage view, with its navigation bar of commands extending down the left side of the Excel window. Backstage view helps you access and use file management features, just as the ribbon offers commands that control Excel’s authoring features. In this exercise, you learn to use the FILE tab to open Backstage view. You also use Backstage commands to create a new blank workbook.

@@ Use the Microsoft® Office FILE Tab and Backstage View Use the learning platform Course: Excel As you have seen, a new blank workbook in Excel 2013 contains one worksheet. You can enter data in the first worksheet and click the New sheet button to create another worksheet and then enter additional data. Excel saves the worksheets together in one workbook, rather than as separate documents.

Changing Excel’s View

The Bottom Line On the ribbon, The VIEW tab holds commands for controlling the appearance of the displayed document. You can also open and arrange new windows and split windows for side-by-side views of different parts of your document.

Change Excel’s View As mentioned in a previous section, some groups on the ribbon tabs have an arrow in their lower right corner, called a Dialog Box Launcher. Clicking the arrow opens a dialog box or a window containing more options for that particular group of commands. In this exercise, you learn how to use the VIEW tab commands to change Excel’s view.

Lesson 2 - Overview

88 Certification Ready - How do you split a window? 1.4.13 88 Certification Ready - How do you change back to Normal view? 1.4.6

ÔÔ Another Way You can also change the view among Normal, Page Layout, Page Break Preview, and Zoom by using the icons in the status bar at the bottom of the screen.

@@ Change Excel’s View Use the learning platform Course: Excel

As demonstrated in this exercise, you can preview your printed worksheet by clicking the ribbon’s VIEW tab, and then clicking Page Layout in the Workbook Views group (first section). This view enables you to fine-tune pages before printing. You can change your worksheet’s layout and format in both this view and Normal view. You can also use the rulers to measure the width and height of your window and determine whether you need to change its margins or print orientation.

Splitting the Window When a worksheet contains a lot of data, you can see only a small portion of the worksheet in Excel’s Normal and Page Layout views. The Split command enables you to overcome this limitation by viewing the worksheet in two panes or four quadrants. After issuing this command, you can use the scroll bars on the right and at the bottom of the window to display different sections of the worksheet at the same time so that you can more easily compare or contrast data or see what effect a change in one part of the worksheet might have on a distant part of the worksheet. In this exercise, you learn to split the Excel window and use the scroll bars to view different sections of a worksheet. You also practice entering data into cells in the split windows, and you learn how to remove the split to return to single-window view.

@@ Split the Window 88 Certification Ready - How do you change to Page Layout view? 1.4.6

Use the learning platform Course: Excel

`` Take Note The Split command is especially useful when you need to compare various portions of a long worksheet. When you use a worksheet that contains a small amount of data, it is easy to scroll through the worksheet and focus on specific cells. As you become experienced in working with Excel, however, you might find yourself working on much larger worksheets. The ability to view more than one section of a worksheet at the same time by using split windows is especially useful when you need to compare different sections of data.

Lesson 2 - Overview

Opening a New Window Splitting a window allows you to look at two sections of a worksheet side by side. You can also view two sections of a worksheet by using the New Window command. In this section, you learn to use the New Window command on the VIEW tab to open a new window in Excel. You also learn to use the Switch Window command to change the active window, and you learn how to close multiple windows. ÔÔ Another Way You also can use the Arrange All command on the VIEW tab to display open windows side by side so that you can compare various parts of a large worksheet. Use the View Side by Side and Synchronous Scrolling commands to have both windows scroll together.

Working with an Existing Workbook

@@ Open a New Window Use the learning platform Course: Excel

`` Take Note Clicking the Close Window button closes only the new window opened at the beginning of this exercise. If you use the Close command on the FILE tab, you will close the entire workbook.

The Bottom Line Many workbooks require frequent updating because existing data has changed or new data must be added. Workers frequently open an existing workbook, update information, and then save the workbook to be revised again at a later time. Often, files are created by one person, and then used or updated by others. Filenames should reflect the type of data contained in the file. A descriptive filename enables you to locate and retrieve files quickly. Filenames can be up to 255 characters long, including the filename extension. However, most workers use short descriptive filenames that clearly identify the content of the workbook.

Opening an Existing Workbook When you save an Excel 2013 file, the program automatically adds the .xlsx extension to the end of the file’s name. This extension identifies the program in which the file can be opened (for example, .xlsx is the file extension used in Excel). To open a file, you must also identify the location (such as network or OneDrive), drive, and folder that contain the file. In your local computer environment, your local drive is usually designated as C:. Network, or flash drives can have other letters such as E: or S:.

@@ Open an Existing Workbook Use the learning platform Course: Excel

ÔÔ Another Way To display the Open options in Backstage without using the FILE tab, press Ctrl+O. To display the Open dialog box, press Ctrl+F12.

`` Take Note Throughout this book, you see information that appears in brackets, such as [your e-mail address]. The information contained in the brackets is intended to be directions specific for you rather than something you actually type word for word. It instructs you to perform an action or substitute text. Do not type the actual text that appears within brackets.

Lesson 2 - Overview

ÔÔ Another Way You can press Alt+F4 to close the current file and Excel at the same time

`` Take Note By default, the Open dialog box lists only the files that were created in the program you are using—in this case, Excel. To see files created in other programs, you can select All Files in the Files of type box (next to the File name box) at the bottom of the Open dialog box. If you are familiar with Microsoft Word, you know that when you open a file, the program places your cursor and screen display at the beginning of the document. When you open an Excel workbook, however, the active cell is the same one that was active when you last saved the file. For example, when you open the Contoso Employee Info workbook, A22 is the active cell in Normal view, because A22 was the active cell displayed in Normal view when the file was last saved. This feature enables you to continue working in the same location when you return to the workbook.

Opening a Workbook from Your OneDrive OneDrive is a Microsoft feature that allows you to work with files in the Cloud—a location that is available from any computer in the world as long as you have an Internet connection. When Office 2013 is installed, you have an option of installing OneDrive or you can go to the Microsoft.com site, search for OneDrive download, and install it at a different time.

@@ Open a Workbook from Your OneDrive Use the learning platform Course: Excel

Working with Excel’s Help System

The Bottom Line The Help system in Excel 2013 is rich in information, illustrations, and tips that can help you complete any task as you create worksheets and workbooks. When you install Excel, you automatically install hundreds of help topics on your computer. Excel can also access thousands of additional help topics online.

Using the Help System Finding the right information in Excel’s Help system is easy: You can pick a topic from popular searches, see what’s new, get training, or perform keyword searches by entering terms that best describe the task you want to complete. In this exercise, you learn to open the Help window and move between its online and offline topics. `` Take Note If you aren’t sure what an onscreen tool does, just point to it. Once the mouse pointer rests on a tool, a box called a ScreenTip appears. A basic ScreenTip displays the tool’s name and shortcut key (if a shortcut exists for that tool). Some of the ribbon’s tools have enhanced ScreenTips, which also provide a brief description of the tool.

Lesson 2 - Overview

ÔÔ Another Way You can also press F1 to open the Help window.

@@ Use the Help System Use the learning platform Course: Excel

ÔÔ Another Way You can also press Ctrl+T to keep the Help window as the top window.

Excel’s Help window gives you access to various help topics that offer information about specific Excel features or tools. Help topics can assist you with virtually any task, feature, or problem you encounter when working with Excel. The Help window is set up like a browser, with links to specific categories and topics, and it features some of the same tools you find in your web browser, including: • Back: Jumps to the previously opened Help topic. • Forward: Jumps to the next opened Help topic. • Home: Returns to the initial Help window. • Print: Allows you to print the current Help topic. • Use Large Text: Shows larger text in the Help window.

Figure 1-3 - The Excel Help Toolbar.

`` Take Note Many Excel dialog boxes contain a Help button. When you click it, a Help window opens with information about that dialog box. You can find help in several different ways. For example, you can click one of the links under Popular searches or click a topic listed under Getting started or Basics and beyond. You can also type a keyword or phrase in the Search box, and then press Enter. When you do this, related help topics appear in the Help window. When you click the arrow next to Excel Help at the top of the Help window, the resulting menu lets you choose between searching help topics that are available online and just those topics installed on your computer (referred to as offline help). If your computer has an “always on” connection to the Internet, such as a cable modem or LAN connection, you might want to select Excel Help from Office.com, which is Microsoft’s online-based built-in Help system. If your computer uses a dial-up modem, or if you simply choose not to access online help information, choose the Excel Help from your computer option to access the topics installed on your computer.

Lesson 2 - Overview

B Mini project 2 Navigating a Worksheet An Excel worksheet can contain more than one million rows and more than sixteen thousand columns. There are several ways to move through worksheets that contain numerous rows and columns. You can use the arrow keys, the scroll bars, or the mouse to navigate through a worksheet. In the following exercises, you explore the different methods for moving through a worksheet. `` Take Note A worksheet can be very large or quite small depending on your needs. Available columns go from A through XFD, and available rows can go from 1 through 1,048,567.

88 Certification Ready - Where is the Name Box located and what is it used for? 1.2.5

@@ Navigate a Worksheet Use the learning platform Course: Excel

`` Take Note Ctrl+Arrow allows you to move to the start and end of ranges of data. The worksheet title, which spans all of the columns, is not considered part of the worksheetâ&#x20AC;&#x2122;s data range.

`` Take Note When Scroll Lock is on, SCROLL LOCK is displayed on the left side of the Status bar. To use the arrow keys to move between cells, you must turn off Scroll Lock. Some keyboards come equipped with an onboard Scroll Lock Key, whereas others do not. This is an option, not a necessity.

Navigating Data with the Go To Command The workbook used in these exercises is neither long nor particularly complicated. When you begin dealing with much larger databases, or longer sets of workbooks, you might wish you had some easier means to get around the data than just scrolling. The Name Box indicates the current cell you are in as well as gives you the opportunity to name the cell or a range. The Go To command can take you to particular points in a worksheet, including cells and cell ranges that you name yourself.

@@ Navigate Data with the Go To Command Use the learning platform Course: Excel

Lesson 2 - Overview

Knowledge Assessment

Multiple Choice Select the best response for the following statements. 1. An arrow in the bottom-right corner of a group on the ribbon tells you that which of the following is available? a. dialog box b. additional workbook c. list of worksheets d. additional part of the current range 2. Which of the following is a selected cell? a. current command b. default option c. active cell d. default cell 3. Which feature enables you to preview headers and footers, page breaks, and other features that will print? a. Page Preview b. Print Layout c. Synchronous Scrolling d. Window view 4. After a file has been opened, the filename appears in which of the following? a. title bar b. footer c. header d. Description pane 5. When you split a window, the window is divided into how many panes? a. two b. three c. four d. two or four 6. When you click the Help button, what opens? a. ScreenTips b. Keytips c. Help window d. dialog box 7. Which is the intersection of a row and column? a. range b. tab c. bar chart d. cell

Lesson 2 - Overview 8. Which of the following starts off with Open, Undo, and Redo and can be customized to contain the commands you use most frequently? a. A worksheet b. The Help window c. The Quick Access Toolbar d. The ribbon 9. How many worksheets does a new Excel 2013 workbook open with? a. one b. two c. three d. four 10. To get to the last cell on the worksheet, which of the following should you press? a. Ctrl + Home b. Ctrl + End c. Ctrl + Right d. Ctrl + Left

True / False Circle T if the statement is true or F if the statement is false. T T

F F

T T T T T T T T

F F F F F F F F

1. Pressing the F1 key displays Backstage view. 2. Pressing the Alt key activates Keytips that allow you to use the keyboard to choose ribbon tabs instead of clicking them with the mouse. 3. Ctrl + O opens a new blank workbook. 4. The Quick Access Toolbar appears on the right side of the title bar, above the ribbon. 5. Ctrl + F displays Backstage view. 6. Click the FILE tab to get to Backstage view. 7. Press Ctrl + Home to go to cell A1. 8. The columns in a worksheet are identified by numbers. 9. The active cell in a worksheet is outlined by a bold rectangle. 10. Page Layout view is useful when preparing your data for printing.