Microsoft Excel 2016 Advanced
Private Class From
(2 Days)

Upon completion of this program, participants should be able to:

  • Advance Chart function and PowerPivot Function
  • Analyze data Using Pivot Tables
  • Perform What If Analysis using:
  • Goal Seek
  • Solver
  • Input Table
  • Scenarios
  • Use VLOOKUP function to extract data
  • Nesting INDEX and MATCH Function
  • Combining & Consolidating Data
  • Create Hyperlink in a workbook

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.


Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

  • Have attended Microsoft Excel – Foundation and Intermediate Level; OR
  • Able to use and apply Database function
  • Able to use and apply Logical function (IF, AND)
  • Able to use and apply Statistical function (COUNTIF, AVERAGEIF, COUNTIFS, AVERAGEIFS)
  • Able to use and apply Mathematical function (SUMIF, SUMIFS)
  • Able to use and apply Lookup function (VLOOKUP)
  • Able to use Auto and Advanced Filter command
  • Able to validate data
  • Able to apply Conditional Formatting
  • Able to Protect worksheet and workbook

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise

Topic A: Subtotal and Outlining

  • Creating Subtotals
  • Displaying and Collapsing Levels
  • Using Automatic Outlining

Topic A: Using Data Analysis Tools

  • Using a One or Two Input Data Table
  • Using Goal Seek

Topic B: Exploring Scenarios

  • What is a Scenario?
  • Creating a Scenario
  • Saving Multiple Scenarios
  • Creating a Scenario Summary Report

Topic C: Using Solver

  • Understanding Solver
  • Generating Reports and Scenarios with Solver
  • Changing Solver Values
  • Managing Solver Constraints
  • Using Solver as a Goal Seek Tool

Topic A: Using the Advanced Function

  • Using the INDEX Function
  • Using the MATCH Function
  • Combining the MATCH and INDEX functions

Topic B: Linking, Consolidating, and Combining Data

  • Linking Workbooks
  • Consolidating Workbooks
  • Combining Worksheets

Topic A: Create a Trendline

  • Trendlines
  • Types of Trendlines
  • Adding a Trendline
  • The Format Trendline Task Pane

Topic B: Create Advanced Charts

  • Dual Axis Charts
  • Creating Custom Chart Templates