Cert Prep: Excel Expert - Microsoft Office Specialist for Office 2019 and Office 365

Introduction

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

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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