YoVDO

Cert Prep: Excel 2013 Microsoft Office Expert Part One (77-427)

Offered By: LinkedIn Learning

Tags

Microsoft Certification Courses Microsoft Excel Courses PivotTables Courses

Course Description

Overview

Prepare for the Microsoft Office Specialist Expert exam for Excel in Microsoft 365.

Syllabus

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
2. 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
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, 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
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 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
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 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
6. Final Challenge
  • Challenge: Full-length MOS Excel Expert sample exam
  • Solution: Full-length MOS Excel Expert sample exam

Taught by

Jennifer McBee

Related Courses

Introduction to Computational Finance and Financial Econometrics
University of Washington via Coursera
Excel 2010
Miríadax
The Finance of Retirement & Pensions
Stanford Graduate School of Business via NovoEd
Einführung in die Wahrscheinlichkeitstheorie
Johannes Gutenberg University Mainz via iversity
Introduction to VBA/Excel Programming
Cal Poly Pomona via Open Education by Blackboard