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

Contents

• Introduction Introduction

  • Prove your Excel skills as a MOS Expert

    1m 51s
  • Who should take this course

    48s
  • Using the exercise files

    52s
  • Using the challenge files

    33s
• 1. Microsoft Office Specialist (MOS) Expert Exam Overview 1. Microsoft Office Specialist (MOS) Expert Exam Overview

  • (Locked)
    The different kinds of Microsoft Office Specialist certifications

    1m 17s
  • (Locked)
    Prepare for the test

    3m 14s
  • (Locked)
    Overview of the 2019 MOS Expert exam format and learning objectives

    3m 16s
• 2. Manage Workbook Options and Settings 2. Manage Workbook Options and Settings

  • (Locked)
    Enable macros

    3m 38s
  • (Locked)
    Create simple macros and copy to another workbook

    7m 10s
  • (Locked)
    Reference data in other workbooks

    7m 36s
  • (Locked)
    Manage workbook versions

    6m 17s
  • (Locked)
    Protect workbooks and worksheets

    7m 37s
  • (Locked)
    Configure formula calculation options

    9m 52s
  • (Locked)
    Manage comments

    5m 11s
  • (Locked)
    Configure editing and display languages

    4m 58s
  • (Locked)
    Challenge: Manage workbook options and settings

    2m 1s
  • (Locked)
    Solution: Manage workbook options and setting

    9m 37s
• 3. Manage and Format Data 3. Manage and Format Data

  • (Locked)
    Fill cells using Flash Fill

    2m 59s
  • (Locked)
    Fill cells using advanced Fill Series options

    6m 44s
  • (Locked)
    Create custom number formats

    8m 19s
  • Configure data validation

    9m 3s
  • (Locked)
    Group and ungroup data and use subtotals and totals

    7m 10s
  • (Locked)
    Remove duplicate records

    4m 24s
  • (Locked)
    Create and manage custom conditional formatting rules

    9m
  • Create conditional formatting rules using formulas

    8m 8s
  • (Locked)
    Challenge: Manage and format data

    30s
  • (Locked)
    Solution: Manage and format data

    10m 35s
• 4. Create Advanced Formulas 4. Create Advanced Formulas

  • Use AND(), OR(), and NOT() in nested functions

    6m 9s
  • Use IF() and IFS() in nested functions

    4m 31s
  • (Locked)
    Use SWITCH() in nested functions

    3m 38s
  • Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions

    5m 2s
  • (Locked)
    Use COUNT() and COUNTIF()

    2m 54s
  • Use MAXIFS() and MINIFS() in nested functions

    3m 27s
  • (Locked)
    Look up data using VLOOKUP() and HLOOKUP()

    5m 52s
  • Use the MATCH() and INDEX() functions

    5m 47s
  • (Locked)
    Use NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functions

    8m 15s
  • (Locked)
    Summarize data from multiple ranges by using the Consolidate feature

    3m 10s
  • (Locked)
    Use What-If Analysis Goal Seek and Scenario Manager

    5m 57s
  • (Locked)
    Calculate financial data with NPER() and PMT() functions

    3m 30s
  • (Locked)
    Troubleshoot formulas

    9m 24s
  • (Locked)
    Challenge: Create advanced formulas

    27s
  • (Locked)
    Solution: Create advanced formulas

    9m 8s
• 5. Manage Advanced Charts and Tables 5. Manage Advanced Charts and Tables

  • (Locked)
    Create and modify dual-axis and combo charts

    2m 47s
  • (Locked)
    Create and modify box and whisker and funnel charts

    5m 55s
  • (Locked)
    Create and modify histogram and map charts

    6m 27s
  • (Locked)
    Create and modify sunburst and waterfall charts

    6m 39s
  • (Locked)
    Create and modify PivotTables

    7m 44s
  • (Locked)
    Create and modify PivotCharts

    6m 5s
  • (Locked)
    Challenge: Manage advanced charts and tables

    28s
  • (Locked)
    Solution: Manage advanced charts and tables

    5m 40s
• 6. Final Challenge 6. Final Challenge

  • (Locked)
    Challenge: Full-length MOS Expert sample exam for Excel 2019

    39s
  • (Locked)
    Solution: Full-length MOS Expert sample exam for Excel 2019

    20m 30s
• Conclusion Conclusion

  • (Locked)
    Next steps

    32s