Exam 77-420 Microsoft Excel 2013
Microsoft Official Academic Course Series, Vol. 376

This Microsoft Excel 2013, Exam 77-420 book is the only Microsoft Official Academic Course (MOAC) textbook. This series includes a complete classroom instructional program. This Excel 2013 text is mapped to the Excel 2013 certification exam  objectives and is designed to re-enforce workforce skills. With  this book students learn to create and edit professional-looking spreadsheets for a variety of purposes and situations. It also covers such skills as charting, creating analytical, financial reports, data entry, developing budgets, formatting numerical (financial, statistical, etc.) reports, creating forms,  graphing, processing data, reporting, technical support, trending  and much more. The Microsoft Official Academic Course (MOAC) Office series also  offers OfficeGrader. This valuable tool corrects your  students tasked-based assignments. Students work on  real-world problems like the ones they will encounter in the  workforce ensuring they are ready for real professional  challenges. OfficeGrader allows for efficient and  consistent grading saving time for other important teaching  activities. Grading is easier than ever allowing faster assignment  turnaround to students. Excel 2013 certification can help students with classwork and differentiate job hunters in todays competitive job market.  Students who have earned certification can  broaden their employment opportunities in such fields as accounting, office administration, consulting, as executives and managers, help desk personnel, instructors/trainers, program/project managers, and sales careers.  

Lesson 1: Overview Lesson Skill Matrix Key Terms Software Orientation Starting Excel Opening Excel Working in the Excel Window Using Onscreen Tools Navigating the Ribbon Introducing Office Backstage Using the Microsoft Office FILE Tab and Backstage View Changing Excel’s View Change Excel’s View Splitting a Window Workplace Ready: Large Workbooks Opening a New Window Working with an Existing Workbook Opening an Existing Workbook Opening a Workbook from Your SkyDrive Navigating a Worksheet Navigating Data with the Go To Command Working with Excel’s Help System Using the Help System Summary Skill Matrix Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 2: Working with Microsoft Excel 2013 Lesson Skill Matrix Key Terms Creating Workbooks Creating a Workbook from Scratch Switching Between Open Workbooks Saving Workbooks Naming and Saving a Workbook Workplace Ready: Organization File Conventions Saving to Your Skydrive Saving a Workbook Under a Different Name Saving a Workbook in a Previous Excel Format Saving in Different File Formats Entering and Editing Basic Data in a Worksheet Entering Basic Data in a Worksheet Changing Column Width Editing a Cell′s Contents Deleting and Clearing a Cell′s Contents Using Data Types to Populate a Worksheet Entering Labels and Using AutoComplete Entering Numeric Values Entering Dates Filling a Series with Auto Fill Filling Cells with Flash Fill Cutting, Copying, and Pasting Data Copying a Data Series with the Mouse Moving a Data Series with the Mouse Copying and Pasting Data Cutting and Pasting Data Editing a Workbook′s Properties Assigning Keywords Summary Skill Matrix Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 3: Using Office Backstage Lesson Skill Matrix Key Terms Software Orientation Accessing and Using Backstage view Printing With Backstage Printing and Previewing a Document Using Quick Print to Print a Worksheet Setting the Print Area Printing Selected Worksheets Printing Selected Workbooks Applying Printing Options Changing a Printer Changing the Excel Environment Customizing the Quick Access Toolbar Customizing the Ribbon Customizing the Excel Default Settings Resetting Default Settings, Ribbon, and Quick Access Toolbar Accessing and Using Excel Templates Selecting a Template from the New Tab Searching for Additional Templates Workplace Ready: Job Interview Hint Summary Skill Matrix Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 4: Using Basic Formulas Lesson Skill Matrix Key Terms Software Orientation Understanding and Displaying Formulas Understanding Order of Operations Building Basic Formulas Creating a Formula that Performs Addition Creating a Formula that Performs Subtraction Creating a Formula that Performs Multiplication Creating a Formula that Performs Division Using Cell References in Formulas Using Relative Cell References in a Formula Using Absolute Cell References in a Formula Workplace Ready: Absolute Cell References for Payroll Tracking Using Mixed Cell References in a Formula Using External Cell References Using Cell Ranges in Formulas Naming a Range Changing the Size of a Range Creating a Formula that Operates on a Named Range Keeping Track of Named Ranges Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 5: Using Functions Lesson Skill Matrix Key Terms Software Orientation Exploring Functions Displaying Dates and Times with Functions Exploring Dates Using TODAY Using NOW Workplace Ready: Using Dates at Work Using the SUM Function Using the COUNT Function Using the COUNTA Function Using the AVERAGE Function Using the MIN Function Using the MAX Function Using a Financial Function Use PMT Using Formulas to Create Subtotals Selecting and Creating Ranges for Subtotaling Building Formulas for Subtotaling Modifying Ranges for Subtotaling Uncovering Formula Errors Reviewing Error Messages Tracing and Removing Trace Arrows Displaying and Printing Formulas Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Circling Back 1 Lesson 6: Formatting Cells and Ranges Summary Skill Matrix Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 7: Formatting Worksheets Lesson Skill Matrix Key Terms Software Orientation Working with Rows and Columns Inserting or Deleting a Row or Column Modifying Row Height and Column Width Formatting an Entire Row or Column Hiding or Unhiding a Row or Column Transposing Rows or Columns Using Themes Choosing a Theme for a Workbook Customizing a Theme Customizing a Theme by Selecting Fonts and Effects Workplace Ready: Putting Themes to Work Modifying a Worksheet’s Onscreen and Printed Appearance Formatting a Worksheet Background Viewing and Printing a Worksheet’s Gridlines Viewing and Printing Column and Row Headings Inserting Headers and Footers Adding Page Numbers to a Worksheet Inserting a Predefined Header or Footer Adding Content to a Header or Footer Inserting a Watermark Repeating Headers and Footers Preparing a Document for Printing Adding and Moving a Page Break Setting Margins Setting a Worksheet’s Orientation Scaling a Worksheet to Fit on a Printed Page Summary Skill Matrix Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 8: Managing Worksheets Lesson Skill Matrix Key Terms Software Orientation Organizing Worksheets Copying a Worksheet Renaming a Worksheet Repositioning the Worksheets in a Workbook Changing the Color of a Worksheet Tab Hiding and Unhiding Worksheets Inserting a New Worksheet into a Workbook Deleting a Worksheet from a Workbook Working with Multiple Worksheets Working with Multiple Worksheets in a Workbook Hiding and Unhiding Worksheet Windows in a Workbook Workplace Ready: Ideas for Arranging Worksheets in Excel Using Zoom and Freeze to Change the Onscreen View Finding and Replacing Data Locating Data with the Find Command Replacing Data with the Replace Command Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 9: Working with Data and Macros Lesson Skill Matrix Key Terms Software Orientation Importing Data Opening Non–Native Files Directly in Excel Getting External Data Appending Data to a Worksheet Workplace Ready: Working with Databases Ensuring Your Data’s Integrity Restricting Cell Entries to Certain Data Types Allowing Only Specific Values to Be Entered in Cells Removing Duplicate Rows from a Worksheet Sorting Data Sorting Data on a Single Criterion Sorting Data on Multiple Criteria Sorting Data Using Cell Attributes Filtering Data Using AutoFilter Creating a Custom AutoFilter Filtering Data Using Cell Attributes Outlining and Subtotaling Data Grouping and Ungrouping Data Auto–Outlining Data Collapsing Groups of Data in an Outline Subtotaling Data in Outlines Setting up Data in a Table Format Formatting a Table with a Quick Style Removing Styles from a Table Defining a Title for a Table Using the Total Row Command in a Table Adding and Removing Rows or Columns in a Table Filtering Records in a Table Sorting Data on Multiple Columns in a Table Changing Sort Order in a Table Removing Duplicates in a Table Using a Slicer to View Table Data Converting a Table into a Range Saving Work with Macros Recording a Basic Macro Workplace Ready: Planning to Record a Macro Running a Macro Managing Macro Security Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Circling Back 2 Lesson 10: Using Advanced Formulas Lesson Skill Matrix Key Terms Software Orientation Workplace Ready: Begin Your Excel Portfolio Using Formulas to Conditionally Summarize Data Using SUMIF Using SUMIFS Using COUNTIF Using COUNTIFS Using AVERAGEIF Using AVERAGEIFS Using Formulas to Look Up Data in a Workbook Using VLOOKUP ]Using HLOOKUP Adding Conditional Logic Functions to Formulas Using IF Using AND Using OR Using NOT Using IFERROR Using Formulas to Modify Text Converting Text to Columns Using LEFT Using RIGHT Using MID Using TRIM Using PROPER Using UPPER Using LOWER Using CONCATENATE Using FIND Using SUBSTITUTE Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 11: Securing and Sharing Workbooks Lesson Skill Matrix Key Terms Software Orientation Securing Your Work Before Sharing It with Others Protecting a Worksheet Protecting a Workbook Workplace Ready: Passwords and Security Allowing Multiple Users to Edit a Workbook Simultaneously Using the Document Inspector Marking a Document as Final Distributing a Workbook by Email and the Cloud Distributing a Workbook by Email Sharing a Workbook in the Cloud Tracking Changes to a Workbook Turning Track Changes On and Off Setting Track Changes Options Inserting Tracked Changes Deleting Your Changes Accepting Changes from Another User Rejecting Changes from Another User Removing Shared Status from a Workbook Adding Comments to a Workbook Inserting a Comment Viewing a Comment Editing a Comment Deleting a Comment Printing Comments in a Workbook Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 12: Creating Charts Lesson Skill Matrix Key Terms Software Orientation Building Charts Workplace Ready: Personal Charts Selecting Data to Include in a Chart Moving a Chart Choosing the Right Chart for Your Data Using Recommended Charts Creating a Bar Chart Formatting a Chart with a Quick Style Formatting a Chart with a Quick Layout Formatting the Parts of a Chart MANUALLY Editing and Adding Text on Charts Formatting the Data Series Changing the Chart′s Border Line Modifying a Chart’s Legend Modifying a Chart Adding Elements to a Chart Deleting Elements from a Chart Adding Additional Data Series Resizing a Chart Choosing a Different Chart Type Switching Between Rows and Columns in Source Data Using New Quick Analysis Tools Adding a Chart or Sparklines Working with Totals Applying Conditional Formatting Creating PivotTables and PivotCharts Creating a Basic PivotTable Adding a PivotChart Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Lesson 13: Adding Pictures and Shapes to a Worksheet Lesson Skill Matrix Key Terms Software Orientation Inserting Pictures Inserting a Picture from a File Inserting an Online Picture Adding Shapes, Lines, Text Boxes, and WordArt Adding Shapes Drawing Lines Adding Text to a Shape Adding a Text Box Using WordArt Deleting, Copying and Moving Graphics Deleting Graphics Moving Graphics Copying Graphics Formatting Graphics Applying Styles to Shapes and Changing Styles Resizing a Graphic Rotating a Graphic Stacking Overlapping Graphics Adding Graphic Enhancements Using Picture Tools Making Corrections to a Graphic Using the Color Feature to Enhance Images Changing a Graphic with Artistic Effects Applying a Picture Style Resetting a Picture to Its Original State Using Picture Properties Using a Screenshot to Capture and Crop Images Workplace Ready: Using Graphics in Excel and Other Office Applications Using SmartArt Graphics Creating a Flowchart Creating an Organization Chart Assessment Knowledge Assessment Competency Assessment Proficiency Assessment Mastery Assessment Circling Back 3

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. MOAC 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 Microsoft Official Academic Course program.