Cert Prep: Excel Associate - Microsoft Office Specialist for Office 2019 and Office 365

Introduction

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

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

• Import TXT or CSV data into a new workbook
• Import TXT or CSV data into an existing workbook
• Search for data within a workbook
• Navigate data with named ranges and hyperlinks
• Format worksheets and workbooks
• Customize headers and footers
• Display and modify content in different views
• Modify basic workbook properties
• Customize the Quick Access Toolbar
• Inspect and export workbooks
• Configure print settings
• Challenge: Manage worksheets and workbooks
• Solution: Manage worksheets and workbooks

• Paste data using special paste options
• Fills cells by using AutoFill
• Insert and delete columns, rows, and cells
• Format cells and ranges
• 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

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

• Insert relative, absolute, and mixed references
• Reference named ranges and named tables 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
• 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

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

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

• Next steps