Cert Prep: Excel 2013 Microsoft Office Expert Part One (77-427)

Introduction

• Welcome
• What to know before watching this course
• Using the exercise files

1. Microsoft Office Specialist (MOS) Expert Exam Overview

• Understand the different kinds of Microsoft Office Specialist certifications
• Review the exam format and learning objectives

2. Manage and Share Workbooks

• Track, retain, and display all changes
• Manage comments
• Identify and troubleshoot errors with tracing
• Challenge: Manage and share workbooks
• Solution: Manage and share workbooks

3. Apply Custom Formats and Layouts

• Apply advanced conditional formatting
• Use functions to format cells and apply advanced filters
• Modify tab order among workbook elements and objects
• Prepare a workbook for internationalization
• Prepare a workbook for accessibility
• Manage multiple options for +Body and +Heading fonts
• Challenge: Apply custom formats and layouts
• Solution: Apply custom formats and layouts

4. Create Advanced Formulas

• Use the LOOKUP function
• Use the HLOOKUP and VLOOKUP functions
• Use the MATCH and INDEX LOOKUP functions
• Use the TRANSPOSE function
• Use the NOW and TODAY functions
• Use functions to serialize dates and times
• Challenge: Create advanced formulas
• Solution: Create advanced formulas

5. Create Advanced Charts and Tables

• Add trendlines and view chart animation
• Create dual axis charts and custom chart templates
• Create, format, and filter PivotTables
• Group records and use calculated fields
• Use PowerPivot and manage relationships
• Challenge: Create advanced charts and tables
• Solution: Create advanced charts and tables

6. Full-Length MOS Expert Sample Exam for Excel 2013

• Challenge: Full-length practice exam
• Solution: Full-length practice exam

Conclusion

• Next steps