Course Overview

Ch 1: Course Overview

Chapter 1 introduces the TestOut Excel Pro course, starting with an introduction to Microsoft Office and Excel. It discusses the course objectives, and how to use the lab simulations for effective learning. It also introduces keyboard shortcuts as well as tips for Mac and Chromebook users.

Ch 2: Common Office Features

Chapter 2 explores common features across Microsoft office products such as the ribbon and the backstage view. It also covers how to customize the user's view and options. Students will learn to print files as well as navigate and search documents within Microsoft Office. This chapter concludes with practicing how to insert and format objects such as shapes and images.

Ch 3: Excel Basics

Chapter 3 teaches how to manage worksheets in excel workbooks, build spreadsheets using templates and themes, and import external data. It covers how to organize and enter data, use copy and paste and work with larger data sets. Students will learn how to change properties for protection, data validation, and document printing. This chapter concludes by discussing cell formatting options, including numbers, cells, styles, colors, and borders.

Ch 4: Formulas and Functions

Chapter 4 discusses the effectiveness of basic formulas and functions, instructing students to use AutoFill options, mathematical operator functions, text functions and concatenation. This chapter also explores advanced functions like relative and absolute references, named cells and ranges, logical functions, and the IF function. This chapter concludes by covering error messages within Excel.

Ch 5: Simple Data Analysis

Chapter 5 explores data analysis by introducing charts, their different types, and how to format charts to effectively present data. Tables are an effective way to organize data within Excel and students will learn how to use them effectively. This chapter explains how to customize tables, and format tables based on conditions using the conditional formatting tool.

Ch 6: MO-200 (Excel Associate) Practice Exams

Chapter 6 introduces the auto-graded live projects within Excel. Unlike other assignments, these live projects are done using an installed version of Microsoft Excel where students can practice what they have learned from the simulated labs. Students can download guided instruction from LabSim to execute the tasks within Microsoft Excel. Teachers and students will still be able to see the results from the assignments and access the submitted assignments. This chapter provides 3 live projects. Students will also prepare to take the MOS Excel Associate (MO-200) exam. This chapter teaches best practices in preparing for the MOS exam, such as studying over time, focusing on exam objectives and entering the test well-rested. It also reviews how to perform unfamiliar tasks by searching panes, dialog boxes and contextual tabs. Lastly this chapter introduces the TestOut MOS Practice exams, providing domain practice labs as well as practice exams, so that students can enter the actual exam with confidence.

Ch 7: Advanced Workbook Options and Settings

Chapter 7 is the first chapter exploring advanced topics to prepare students for the MOS Excel expert exam. Beginning with AutoSave and AutoRecover, this chapter covers crucial skills to not lose work, how to reference data in different workbooks, configure language settings and manage workbooks. This chapter concludes by instructing students how to prepare workbooks for collaboration by protecting different parts of a workbook, configuring formula calculation options, and managing comments.

Ch 8: Advanced Data Formatting

Chapter 8 introduces advanced features of autofill, flash fill, and advanced fill series options. It addresses the utility of customize fill options. Students will learn how to format and validate data by creating custom number formats, configure data validation, use outlines to create groups and subtotals, and remove duplicate records. This chapter concludes by discussing advanced conditional formatting and filtering. It covers Boolean operators and their logic, and how to create and adjust rules when using conditional formatting.

Ch 9: Advanced Formulas and Macros

Chapter 9 Explores several advanced features in Excel, such as logical operators, look up functions, date and time functions, data analysis, troubleshooting formulas and building macros. Students will learn about nested and mathematical functions, VLOOKUP and HLOOKUP functions, and MATCH and INDEX functions. They will also learn to summarize data, perform a what-if analysis, and interpret error messages to troubleshoot formulas. This chapter concludes by covering how to record and run simple macros, enable and disable macros, and how to copy macros.

Ch 10: Advanced Charts and Tables

Chapter 10 investigates the usefulness of charts and tables for interpreting and presenting data by exploring advanced chart types and formatting. Students will learn how to create and modify a PivotTable and calculate fields in a PivotTable. This chapter concludes by introducing PivotCharts and how to create, manipulate and drill down into PivotCharts.

Ch 11: MO-201 (Excel Expert) Practice Exams

Chapter 11 prepares students to take the MOS Excel Expert (MO-101) exam. It begins by teaching best practices in preparing for the MOS exam such as studying over time, focusing on exam objectives and entering the test well-rested. It also reviews how to perform unfamiliar tasks by searching panes, dialog boxes and contextual tabs. Lastly, this chapter introduces the TestOut MOS practice exams, providing domain practice labs as well as practice exams so that students can enter the actual exam with confidence.

Ch 12: TestOut Pro Certified: Microsoft Excel® Advanced Practice Exams

Chapter 12 reviews the TestOut Pro Certified: Microsoft Excel® Advanced certification exam objectives, lists objectives by course section, and tells how to take the Pro exam. This chapter also provides a practice exam and domain practice exams that help prepare students for the certification exam.

Ch 1: Course Overview

Excel and Microsoft Office Suite

  • The Microsoft Office Suite
  • Introduction to Microsoft Excel
  • Excel Tasks Overview

Course Features

  • Introduction to TestOut's Microsoft Excel® Course
  • Course Overview
  • Course Objectives
  • Tips for Mac Users
  • Using the Simulated Labs
  • Keyboard Shortcuts in Labs
  • Tips for Chromebook Users

Ch 2: Common Office Features

Getting Started with Office

  • Introduction to Office Applications
  • Microsoft Office Versions
  • Learning Office Applications
  • The Ribbon
  • Beyond the Ribbon
  • Backstage View Options
  • Common Office Application Features
  • Desktop vs. Web Applications

Customizing Views and Options

  • Document Views
  • Customizing Toolbars and Ribbons

Printing Files

  • Printing Options in Office Applications

Navigating Files

  • Navigating Through a Word Document
  • Navigating Through an Excel Workbook

Working With Objects

  • Formatting Images
  • Formatting Shapes
  • Object Formatting

Using Office Collaboration Features

  • Tracking Changes and Comments
  • Combining Revisions
  • Distributing Files
  • Collaboration Tips

Ch 3: Excel Basics

Creating and Managing Workbooks

  • Spreadsheet Basics
  • Worksheet Management
  • Templates and Themes
  • External Data
  • Workbook Management Tips

Organizing and Entering Data

  • Deleting and Clearing Cells
  • Copy and Paste Options
  • Large Data Sets
  • Data Entry Tips

Changing Properties and Printing Worksheets

  • Data Protection
  • Data Validation
  • Excel Printing Tips
  • Worksheet Printing Tips

Formatting Cells

  • Number Formats
  • Cell Formats
  • Cell Styles and Colors
  • Cell Borders
  • Cell Formatting Tips

Ch 4: Formulas and Functions

Entering Simple Formulas

  • Formulas and Functions
  • AutoFill Options
  • Add and Average
  • Text Functions
  • Concatenating in Excel
  • Formula Tips

Using Advanced Functions

  • Relative and Absolute References
  • Advanced Cell References
  • Named Cells and Ranges
  • The IF Function
  • Logical Functions
  • Excel Error Messages
  • Advanced Function Tips

Ch 5: Simple Data Analysis

Displaying Data Charts

  • Chart Formatting
  • Chart Types
  • Chart Tips

Organizing Data in Tables

  • Conditional Formatting
  • Excel Tables
  • Table Customization
  • Formulas in Tables
  • Excel Table Tips

Ch 6: MO-200 Practice Exams

Excel Live Projects

  • Using Excel Live Projects
  • Installing Task Guide Plugin
  • Live Project Tips

Preparing for MOS Associate Certification

  • Preparing for the MOS Exam
  • Performing Unfamiliar Tasks
  • Using TestOut MOS Practice Exams

Domain Practice Labs: Excel Associate 2019

  • Managing Worksheets and Workbooks
  • Manage Data Cells and Ranges
  • Manage Tables and Table Data
  • Perform Operations by using Formulas and Functions
  • Manage Charts

Practice Exams: Excel Associate 2019 (MO-200)

  • Practice Exam A
  • Practice Exam B

Ch 7: Advanced Workbook Options and Settings

Managing Workbooks

  • Configuring AutoSave and AutoRecover
  • Referencing Data in Other Workbooks
  • Configuring and Using Language-Specific Features
  • Tips for Managing Workbooks

Preparing Workbooks for Collaboration

  • Protecting Cell Ranges, Worksheets, and Workbooks
  • Configuring Formula Calculation Options
  • Managing Comments
  • Tips to Prepare Workbooks for Collaboration

Ch 8: Advanced Data Formatting

Filling Cells Based on Existing Data

  • Flash Fill and Advanced Fill Series Options
  • Tips for Fill Cells Based on Existing Data

Formatting and Validating Data

  • Custom Number Formats
  • Configuring Data Validation
  • Using Outlines to Create Groups and Subtotals
  • Removing Duplicate Records
  • Tips to Format and Validate Data

Advanced Conditional Formatting and Filtering

  • Boolean Operators
  • Using Boolean Logic in Excel
  • Custom Conditional Formatting Rules
  • Conditional Formatting Rules That Use Formulas
  • Tips for Advanced Conditional Formatting and Filtering

Ch 9: Advanced Formulas and Macros

Performing Logical Operations in Formulas

  • Logical Functions and Nested Functions
  • Logical Operations with Mathematical Functions
  • Tips to Perform Logical Operations in Formulas

Looking Up Data by Using Functions

  • The VLOOKUP and HLOOKUP Functions
  • The MATCH and INDEX Functions
  • Tips to Look Up Data by Using Functions

Using Advanced Date and Time Functions

  • Date and Time Functions
  • Tips for Advanced Date and Time Functions

Performing Data Analysis

  • Summarizing Data from Multiple Ranges
  • Performing What-If Analysis
  • Common Financial Terminology
  • Calculating Financial Data
  • Data Analysis Tips

Troubleshooting Formulas

  • Tracing Precedence and Dependence
  • The Watch Window
  • Errors and Error Checking
  • Evaluating Formulas
  • Tips for Troubleshooting Formulas

Creating and Modifying Simple Macros

  • Recording and Running Simple Macros
  • Enabling and Disabling Macros
  • Copying Macros Between Workbooks
  • Tips to Create and Modify Simple Macros

Ch 10: Advanced Charts and Tables

Creating and Modifying Advanced Charts

  • Selecting Advanced Chart Types
  • Advanced Chart Formatting
  • Dual-Axis Charts
  • Tips to Create and Modify Advanced Charts

Creating and Modifying PivotTables

  • Introduction to PivotTables
  • Advanced PivotTable Features
  • Calculated Fields in PivotTables
  • Tips to Create and Modify PivotTables

Creating and Modifying PivotCharts

  • Creating and Manipulating PivotCharts
  • Drilling Down into PivotChart Details
  • Tips to Create and Modify PivotCharts

Ch 11: MO-201 Practice Exams

Preparing for MOS Expert Certification

  • Preparing for the MOS Exam
  • Performing Unfamiliar Tasks
  • Using TestOut MOS Practice Exams

Domain Practice Labs: Excel Expert 2019 (MO-201)

  • Manage Workbook Options and Settings
  • Manage and Format Data
  • Create Advanced Formulas and Macros
  • Manage Advanced Charts and Tables

Practice Exams: Excel Expert 2019 (MO-201)

  • Expert Practice Exam A
  • Expert Practice Exam B

Ch 12: TestOut Pro Certified: Microsoft Excel® Advanced Practice Exams

Preparing for TestOut Pro Certification: Microsoft Excel® Advanced Certification

  • Pro exam objectives
  • Objectives by course section
  • How to take the Pro exam
  • Using domain practice labs and practice exams