Microsoft Office Specialist: Expert (Office 365 and Office 2019) Cert Prep

Course details

Demonstrate your mastery of Excel by becoming a Microsoft Office Specialist (MOS) Expert. Get training to prepare for the MOS Excel Expert certification exam for Office 2019 and Office 365 (MO-201). This course, created by Certified MOS Master Instructor Jennifer McBee, helps you prepare for the four main areas of the exam: managing workbook options and settings, managing and formatting data, creating advanced formulas and macros, and managing advanced charts and tables. The course includes an overview of the new Office 365 and Office 2019 Microsoft Office Specialist Program, its costs, and all the exam objectives. Follow along with the downloadable sample files. Then take the full-length practice test at the end of the course to test your skills and speed.

Learning objectives
Copying macros between workbooks
Protect worksheets and cell ranges
Configuring formula calculation options
Using advanced fill options
Configuring Data Validation
Creating custom conditional formatting rules
Looking up data using functions
Troubleshooting formulas
Creating advanced charts
Creating and modifying PivotTables and PivotCharts
Taking the sample exam

Concepts

Introduction

Prove your Excel skills as a MOS Expert
Who should take this course
Using the exercise files
Using the challenge files

Microsoft Office Specialist (MOS) Expert Exam Overview

The different kinds of Microsoft Office Specialist certifications
Prepare for the test
Overview of the 2019 MOS Expert exam format and learning objectives

Manage Workbook Options and Settings

Enable macros
Create simple macros and copy to another workbook
Reference data in other workbooks
Manage workbook versions
Protect workbooks and worksheets
Configure formula calculation options
Manage comments
Configure editing and display languages
Challenge - Manage workbook options and settings
Solution - Manage workbook options and setting

Manage and Format Data

Fill cells using Flash Fill
Fill cells using advanced Fill Series options
Create custom number formats
Configure data validation
Group and ungroup data and use subtotals and totals
Remove duplicate records
Create and manage custom conditional formatting rules
Create conditional formatting rules using formulas
Challenge - Manage and format data
Solution - Manage and format data

Create Advanced Formulas

Use AND(), OR(), and NOT() in nested functions
Use IF() and IFS() in nested functions
Use SWITCH() in nested functions
Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions
Use COUNT() and COUNTIF()
Use MAXIFS() and MINIFS() in nested functions
Look up data using VLOOKUP() and HLOOKUP()
Use the MATCH() and INDEX() functions
Use NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functions
Summarize data from multiple ranges by using the Consolidate feature
Use What-If Analysis Goal Seek and Scenario Manager
Calculate financial data with NPER() and PMT() functions
Troubleshoot formulas
Challenge - Create advanced formulas
Solution - Create advanced formulas

Manage Advanced Charts and Tables

Create and modify dual-axis and combo charts
Create and modify box and whisker and funnel charts
Create and modify histogram and map charts
Create and modify sunburst and waterfall charts
Create and modify PivotTables
Create and modify PivotCharts
Challenge - Manage advanced charts and tables
Solution - Manage advanced charts and tables

Final Challenge

Challenge - Full-length MOS Expert sample exam for Excel 2019
Solution - Full-length MOS Expert sample exam for Excel 2019

Conclusion

Next steps