Cert Prep: Excel 2016 Microsoft Office Expert (77-728)

Demonstrate your advanced knowledge of Excel 2016 by becoming a Microsoft Office Specialist Expert. This course helps you prepare for exam 77-728.

Introduction

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

1. Microsoft Office Specialist (MOS) Expert Exam Overview

• Understand the different kinds of Microsoft Office Specialist certifications
• Prepare for the test
• Overview of the 2016 MOS Expert exam format and learning objectives

2. Manage Workbook Options and Settings

• Manage workbooks
• Reference data in another workbook
• Reference data using structured references
• Protect worksheets and workbooks
• Challenge: Manage workbook options and settings
• Solution: Manage workbook options and setting

3. Apply Custom Data Formats and Layouts

• Create custom formats (number, time, and date)
• Populate cells by using advanced Fill Series options
• Create custom conditional formatting rules using operators and formulas
• Manage conditional formatting rules
• Create custom styles and themes
• Insert and configure form controls
• Configure data validation
• Challenge: Apply custom data formats and layouts
• Solution: Apply custom data formats and layouts

4. Create Advanced Formulas

• Use AND/OR functions
• Use IFS functions
• Use the LOOKUP function
• Use the MATCH and INDEX functions
• Use date and time functions
• Troubleshoot formulas
• Use What-If Analysis
• Define named ranges and objects
• Use the PMT financial function
• Cube functions
• Challenge: Create advanced formulas
• Solution: Create advanced formulas

5. Create Advanced Charts and Tables

• Use advanced chart formatting
• Create PivotTables and filter with slicers
• Format PivotTables and use GETPIVOTDATA
• Create and modify PivotCharts
• Challenge: Create advanced charts and tables
• Solution: Create advanced charts and tables

6. Final Challenge

• Challenge: Full-length MOS Expert sample exam for Excel 2016
• Solution: Full-length MOS Expert sample exam for Excel 2016

Conclusion

• Next steps