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

Introduction

• Prove your Excel skills with a MOS certification
• Who should take this course?
• About the exercise files
• About the challenges

1. Microsoft Office Specialist Exam Overview

• Different kinds of Microsoft Office Specialist certifications
• Prepare for the test and review learning objectives

2. Manage Worksheets and Workbooks

• Import TXT data into a new or existing workbook
• Import data from online sources
• Search for data within a workbook
• Navigate to named cells, ranges, or workbook elements
• Insert and remove hyperlinks
• Format worksheets and workbooks
• Customize headers and footers
• Manage the Quick Access Toolbar
• Display and modify worksheets in different views
• Modify built-in workbook properties
• Inspect and export workbooks
• Configure print settings
• Manage comments and notes
• Challenge: Manage worksheets and workbooks
• Solution: Manage worksheets and workbooks

3. Manage Data Cells and Ranges

• Paste data using special paste options
• Fill cells by using AutoFill
• Insert and delete columns, rows, and cells
• Format cells and ranges
• Generate random and sequential data using Functions
• Apply styles to cells and ranges
• Define and reference named ranges
• Insert sparklines
• Summarize data visually with built-in conditional formatting
• Challenge: Manage data, cells, and ranges
• Solution: Manage data, cells, and ranges

4. Manage Tables and Table Data

• Create and format tables
• Modify tables
• Filter and sort table data
• Challenge: Manage tables and table data
• Solution: Manage tables and table data

5. Perform Operations Using Formulas and Functions

• Insert relative, absolute, and mixed references
• Use structured references in formulas
• Using the AVERAGE(), MAX(), MIN(), and SUM() functions
• Count cells using the COUNT(), COUNTA(), and COUNTBLANK() functions
• Perform conditional operations using the IF() function
• Sort data and return unique values using the SORT() and UNIQUE() functions
• Format text using the RIGHT(), LEFT(), and MID() functions
• Format text using UPPER(), LOWER(), and LEN() functions
• Format text using the CONCAT() and TEXTJOIN() functions
• Challenge: Perform operations using formulas and functions
• Solution: Perform operations using formulas and functions

6. Manage Charts

• Create and modify charts
• Add and modify chart elements
• Format charts
• Challenge: Manage charts
• Solution: Manage charts

7. Final Practice Exam

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