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

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