Cert Prep: Excel Expert - Microsoft Office Specialist for Microsoft 365 Apps (2023)

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. 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 using advanced options, creating advanced formulas and macros, and creating and managing advanced charts, PivotCharts, and PivotTables. The course includes an overview of the new Microsoft Office Specialist Program, its costs, and all the exam objectives. Follow along with the downloadable exercise files. Then take the full-length practice exam at the end of the course to test your skills and speed.

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

Manage Workbook Options and Settings

Manage macros
Reference data in other workbooks
Manage workbook versions
Protect workbooks and worksheets
Configure formula calculation options
Challenge - Manage workbook options and settings
Solution - Manage workbook options and settings

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, use subtotals and totals
Remove duplicate records
Create and manage custom conditional formatting rules
Create conditional formatting rules using formulas
Generate numeric data by using RANDARRAY()
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 COUNTIF(), COUNTIFS(), MAXIFS, and MINIFS
Look up data using XLOOKUP(), VLOOKUP(), HLOOKUP(), INDEX(), and MATCH()
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
Use the SORTBY() and FILTER() 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 Sunburst, Histogram, and Waterfall charts
Create PivotTables and modify field selections and options
Create and modify PivotCharts
Challenge - Manage advanced charts and tables
Solution - Manage advanced charts and tables

Final Challenge

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

Cert Prep: Excel Expert - Microsoft Office Specialist for Microsoft 365 Apps (2023)

Authors

Jennifer McBee