Prove your Excel skills as a MOS Expert

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

  • The different kinds of Microsoft Office Specialist certifications

    1m 17s
  • Prepare for the test

    3m 14s
  • 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

  • Enable macros

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

    7m 10s
  • Reference data in other workbooks

    7m 36s
  • Manage workbook versions

    6m 17s
  • Protect workbooks and worksheets

    7m 37s
  • Configure formula calculation options

    9m 52s
  • Manage comments

    5m 11s
  • Configure editing and display languages

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

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

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

  • Fill cells using Flash Fill

    2m 59s
  • Fill cells using advanced Fill Series options

    6m 44s
  • Create custom number formats

    8m 19s
  • Configure data validation

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

    7m 10s
  • Remove duplicate records

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

    9m
  • Create conditional formatting rules using formulas

    8m 8s
  • Challenge: Manage and format data

    30s
  • 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
  • Use SWITCH() in nested functions

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

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

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

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

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

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

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

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

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

    3m 30s
  • Troubleshoot formulas

    9m 24s
  • Challenge: Create advanced formulas

    27s
  • Solution: Create advanced formulas

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

  • Create and modify dual-axis and combo charts

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

    5m 55s
  • Create and modify histogram and map charts

    6m 27s
  • Create and modify sunburst and waterfall charts

    6m 39s
  • Create and modify PivotTables

    7m 44s
  • Create and modify PivotCharts

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

    28s
  • Solution: Manage advanced charts and tables

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

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

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

    20m 30s
• Conclusion Conclusion

  • Next steps

    32s