Cert Prep: Excel 2013 Microsoft Office Expert Part Two (77-428)

Introduction

• Welcome
• What to know before watching this course
• Using the exercise files

1. Microsoft Office Specialist (MOS) Expert Exam Overview

• The different kinds of Microsoft Office Specialist certifications
• 2013 MOS Expert exam format and learning objectives

2. Manage and Share Workbooks

• Create and modify templates
• Manage versions and merge multiple workbooks
• Copy styles from template to template
• Link to external data
• Copy macros from workbook to workbook
• Protecting cells and hiding formulas
• ✓ Challenge: Manage and share workbooks
• ✓ Solution: Management and share workbooks

3. Apply Custom Formats and Layouts

• Create and apply custom formats: Number, accounting, time, and date
• Use advanced Fill Series options
• Create and apply custom styles
• Create form fields
• ✓ Challenge: Apply custom formats and layouts
• ✓ Solution: Apply custom formats and layouts

4. Create Advanced Formulas

• Use a Nested IF function
• Use the AND/OR functions
• Use the SUMIFS, AVERAGEIFS, and COUNTIFS functions
• Consolidate data and use the Watch window
• Enable iterative calculations and use What-If Analysis tools
• Use financial functions
• ✓ Challenge: Create advanced formulas
• ✓ Solution: Create advanced formulas

5. Create Advanced Charts and Tables

• Create new PivotCharts
• Manipulate options in existing PivotCharts
• Apply styles to PivotCharts
• ✓ Challenge: Create advanced charts and tables
• ✓ Solution: Create advanced charts and tables

6. Sample Exam

• ✓ Challenge: Full-length MOS Expert sample exam for Excel 2013
• ✓ Solution: Full-length MOS Expert sample exam for Excel 2013

Conclusion

• Next steps