Microsoft Excel MO-200 Exam Prep: Master Excel 2019
What you will learn:
- Master efficient spreadsheet and workbook management in Microsoft Excel 2019.
- Develop advanced skills in managing data cells and ranges for effective data analysis.
- Learn to use tables and manipulate their data for simplified analysis and information presentation.
- Build strong skills in creating and managing charts to represent data visually and understandably.
- Prepare effectively for the Microsoft Excel MO-200 certification exam.
- Learn to use advanced Excel functions like IF, AVERAGE, MAX, MIN, SUM, COUNT, COUNTA, COUNTBLANK, RIGHT, LEFT, MID, UPPER, LOWER, LEN, CONCAT, and TEXTJOIN.
- Become proficient in importing and exporting data from various file formats, including TXT and CSV.
- Master data manipulation techniques such as sorting, filtering, and conditional formatting.
- Understand the use of relative, absolute, and mixed cell references for efficient formula creation.
- Gain expertise in creating visually appealing charts and graphs for insightful data representation.
Description
Ace the Microsoft Excel MO-200 exam and unlock your career potential! This comprehensive course provides expert-level training in Excel 2019, equipping you with the practical skills to manage spreadsheets, analyze data, and create compelling visuals.
Learn from Juan Carlos, a seasoned Excel professional with over 10 years of experience. Through practical examples and real-world applications, you'll master essential techniques such as data manipulation, formula creation, advanced functions, chart design, and effective data visualization. You'll learn to build professional budgets, financial statements, team performance charts, sales invoices, and data entry systems. This course covers everything from importing diverse data types (like TXT and CSV files) to optimizing your workbook for collaboration and problem-solving.
Whether you're a beginner or have some Excel experience, this course is structured to take you from basic spreadsheet management to advanced data analysis. You'll gain proficiency in handling multiple worksheets, creating and formatting tables, implementing powerful formulas and functions (including IF, AVERAGE, MAX, MIN, SUM, COUNT, COUNTA, COUNTBLANK, RIGHT, LEFT, MID, UPPER, LOWER, LEN, CONCAT, and TEXTJOIN), and generating visually impactful charts to communicate your findings effectively. We'll cover essential concepts like relative, absolute, and mixed cell references, named ranges, and conditional formatting to streamline your workflow and maximize your data analysis capabilities.
This course isn't just about passing the MO-200 exam; it's about transforming you into a highly sought-after Excel professional. Enroll today and unlock your full potential in the modern workplace!
Curriculum
Introduction
This introductory section starts with an overview of the course and instructor (00:57). You'll learn what the MO-200 exam entails (02:58) and the course's comprehensive curriculum (02:22), and finally learn how to access downloadable resources (00:13).
Data Import
Learn to import data from various file formats. This module covers importing data from TXT and CSV files, including a review session for better understanding (03:38, 00:55).
Workbook Navigation
Master efficient navigation techniques within Excel workbooks. This section covers searching for information (02:42), navigating to specific cells and ranges (03:17), and working with hyperlinks (03:49).
Customize Ribbon & Views
Personalize your Excel experience. Learn to customize the Quick Access Toolbar (02:29), freeze rows and columns (03:29), change window views (02:16), modify workbook properties (01:23), and display formulas (03:09).
Collaboration Setup
Prepare your workbooks for seamless collaboration. This module teaches you to set up print areas (03:01), save workbooks in alternative file formats (02:35), configure print settings (03:04), and inspect workbooks for potential issues (03:42).
Practice 1: Workbook & Worksheet Management
Put your newly acquired skills to the test with a comprehensive practice exercise focused on workbook and worksheet administration (05:57).
Data Manipulation
Learn essential data manipulation techniques. This includes using special paste options (03:49), auto-filling cells (02:32), inserting and deleting rows/columns (03:20), and inserting/deleting individual cells (02:43).
Cell & Range Formatting
Master cell and range formatting for professional-looking spreadsheets. This involves merging and unmerging cells (02:55), adjusting alignment, orientation, and indentation (01:40), using Format Painter (02:23), adjusting text within cells (01:31), applying number formats (03:04), formatting cells via the dialog box (03:45), applying cell styles (02:51), and clearing cell formatting (01:39).
Named Ranges & References
Enhance efficiency with named ranges. Learn how to define named ranges (03:55) and name tables (02:43) for easier formula creation and data management.
Visual Data Summary
Create visually appealing summaries of your data. Learn how to insert sparklines (03:02), apply built-in conditional formatting (03:44), and remove conditional formatting (02:01).
Practice 2: Data & Range Management
Reinforce your knowledge with a practical exercise focusing on managing data and cell ranges (09:46).
Table Creation & Formatting
Learn to create and format tables for efficient data organization. Learn to create tables from cell ranges (02:59), apply table styles (02:03), and convert tables back to cell ranges (01:48).
Table Modification
Master techniques for modifying tables. This includes adding and deleting rows and columns (02:56), configuring table style options (02:06), and inserting and configuring total rows (02:08).
Table Data Filtering & Sorting
Filter and sort table data efficiently. This module covers filtering records (02:36) and sorting data by multiple columns (02:10).
Practice 3: Table & Data Management
Consolidate your table and data management skills with a focused practice session (05:42).
Inserting References
Learn to use different types of cell references effectively. This includes relative, absolute, and mixed references (05:20), and referencing named ranges and tables within formulas (02:00).
Data Calculation & Transformation
Perform calculations and transform data using key functions. This includes using AVERAGE(), MAX(), MIN(), and SUM() (02:47), COUNT(), COUNTA(), and COUNTBLANK() (04:22), and conditional operations with IF() (02:59).
Text Formatting & Modification
Master text formatting and manipulation with Excel functions. This covers using RIGHT(), LEFT(), and MID() (04:45), UPPER(), LOWER(), and LEN() (02:32), and CONCAT and TEXTJOIN (01:31).
Practice 4: Formula & Function Operations
Practice your formula and function skills with a comprehensive exercise (04:48).
Chart Creation
Learn to create different types of charts to visualize your data (03:50) and create chart sheets (03:11).
Chart Modification
Modify and enhance your charts. This involves adding data series (02:34), switching between row and column data (02:19), and adding/modifying chart elements (03:01).
Chart Formatting
Apply professional formatting to your charts. Learn to apply chart layouts (02:28), chart styles (02:53), and add alt text for accessibility (01:56).
Practice 5: Chart Management
Refine your chart creation and management skills with a final practice session (05:24).
Conclusion
Conclude the course with a final video (02:12) and an extra tip (00:26).
