Cert Prep: Excel 2013 Microsoft Office Specialist (77-420)

Introduction

• Welcome
• What you should know before watching this course
• Using the exercise files
• Using the challenges

1. Microsoft Office Specialist Exam Overview

• Compare the different Microsoft Office Specialist certifications
• Prepare for the exam
• Exam format and registration process

2. Create and Manage Workbooks and Worksheets

• Create new workbooks
• Add, copy, and move worksheets
• Use hyperlinks to navigate workbooks
• Use named ranges in navigation and formulas
• Use Find and Replace for conditional formatting and data validation
• Format using styles or themes
• Format using the Page Layout ribbon
• Insert headers and footers into worksheets
• Insert columns and rows; hide and unhide data
• Configure worksheets and workbooks for printing
• Add values to workbook properties
• Challenge: Worksheets and workbooks
• Solution: Worksheets and workbooks

3. Create and Modify Cells and Ranges

• Use Append to insert data into cells
• Move data between cells, worksheets, and workbooks
• Format worksheets using merge, cell styles, and number formats
• Resize cells to accommodate text
• Format cells using merge and align options
• Apply conditional formatting
• Create and use named ranges
• Use Find and Replace
• Challenge: Cells and ranges
• Solution: Cells and ranges

4. Create and Modify Tables

• Create tables
• Modify tables
• Filter and sort tables
• Challenge: Create and modify tables
• Solution: Create and modify tables

5. Apply Formulas and Functions

• Use relative and absolute references in formulas
• Understand the order of operations in formulas
• Summarize data using functions
• Utilize conditional logic in functions
• Format with text functions
• Challenge: Formulas and functions
• Solution: Formulas and functions

6. Create Charts and Objects

• Create charts and add data series
• Format charts
• Insert and format graphic objects
• Challenge: Charts and objects
• Solution: Charts and objects

7. Final Practice Exam

• Challenge: Full-length practice exam
• Solution: Full-length practice exam

Conclusion

• Next steps