MOS - Excel Expert 2013
Print in

21 Hours

(Exam 77-427 & 77-428)

Course Description

Microsoft® Excel Expert teaches students how to use a variety of intermediate and advanced features to merge workbooks, perform data analysis, and audit formulas in spreadsheets. Students also use summary functions, create PivotTables and PivotCharts, and work with macros.

Students who complete this course will have reviewed all the exam objectives to prepare for: Microsoft Excel 2013 Expert Exams #77-427 and #77-428. Successful completion of the certification exam provides a competitive advantage by validating the knowledge and skill sets for individuals who may be seeking employment or further job opportunities in their careers.

Course Prerequisites

This course assumes that students have completed the Microsoft Excel 2013 Core course or have equivalent Excel knowledge and experience.

  • Manage the worksheet environment
  • Create cell data
  • Format cells and worksheets
  • Manage worksheets and workbooks
  • Apply formulas and functions
  • Present data visually
  • Share worksheet data with other users
  • Analyze and organize data

Course Outline

Lesson 1: Advanced Charts and Formatting, Checking Formulas

  • Create Advanced Chart Elements
    • Dual Axis Charts
    • Using Chart Animation
    • Custom Chart Templates
    • Creating a Trendline
  • Advanced Conditional Formatting
    • Custom Conditional Formats
    • Use Functions to Format Cells
    • Manage Conditional Format Rules
  • Checking for Formula Errors
    • Using the Error Checking Tool
    • Tracing Formula Errors
    • Evaluate Formulas
    • Manual Checking and Displaying Formulas

Lesson 2: PivotTables and Advanced Functions

  • Lookup Functions
  • Transpose Function
  • Date and Time Functions
  • Create and Manage PivotTables
  • Create New PivotTables
    • Manipulate PivotTables
    • Using a Data Slicer
    • Group Data
    • Calculated Fields
    • Format Data
    • Manage Relationships
  • Using Power Pivot

Lesson 3: Filtering, Sharing Workbooks and Accessibility

  • Advanced Filtering
    • Advanced Filters
    • Comparison Operators
  • Workgroup Functions
    • Create a Shared Workbook
    • Resolving Shared Workbook Conflicts
    • Tracking Changes
    • Showing History of Changes
    • Merging Workbooks
    • Removing Shared Use of Workbooks
  • Mark as Final
  • Cell Comments
  • Internationalization
    • Modify Tab Order
    • Display Data in International Formats
    • Use International Symbols
  • Accessibility
    • Using Accessibility Tools
    • Manage Multiple Options for Fonts

 Lesson 4: Custom Formatting and What-If Analysis

  • Using Custom Cell Formats
    • Custom Number Formats
    • Custom Currency and Accounting Formats
    • Custom Date and Time Formats
    • Custom Scientific Formats
    • Custom Text Formats
  • Advanced Format Filling
  • User Defined Styles
    • Custom Color Formats
    • Custom Cell Styles
  • What-If Analysis
    • Manual What-If
    • Using the Goal Seek Tool
    • Using the Solver
  • Working with Scenarios

 Lesson 5: Pivot Charts and More Advanced Formulas

  • Create and Manage Pivot Charts
    • Create a PivotChart
    • Manipulate PivotCharts
    • Apply styles to PivotCharts
  • Financial Functions
  • AND/OR Functions
  • Nesting Functions
  • Conditional Summary Functions
  • Consolidating Data

 Lesson 6: Protecting and Configuring Workbooks

  • Working with Templates
    • Creating a Template
    • Modifying Templates
    • Copy Styles from Template to Template

Register online @ http://one.pa.gov.sg

SDF Applicant, please register @ https://www.skillsconnect.gov.sg

IIT Centre reserves the right to reschedule the course without prior notice due to class size or unforeseen circumstances.